DBMS Interview Questions and Answers (updated)

Q1. What is a DBMS?

Ans: DBMS stands for Database Management System. A DBMS receives requests from applications and translates those requests into actions on a specific database.
A DBMS processes SQL statements or uses other functionality to create, process and administer databases.

Q2. What is a Database system?

Ans: The database and DBMS software together is called as Database system.

Q3. What is Trigger?

Ans: When database are inserted, deleted or updated, a procedure is executed in response to that action. Such SQL procedure are called as trigger.

Q4. What is Data Independence?

Ans: Data independence means that ?the application is independent of the storage structure and access strategy of data?. In other words, The ability to modify the schema definition in one level should not affect the schema definition in the next higher level.

Two types of Data Independence:

1. Physical Data Independence : Modification in physical level should not affect the logical level.
2. Logical Data Independence : Modification in logical level should affect the view level.

Q5. Define sub query?

Ans: Sub-Query is also called as Nested Query. Sub-Query is a query within another query. Sub-Query can be used with SELECT, INSERT, UPDATE and DELETE Statement.

Q6. What are data and information, and how are they related in a database?

Ans: Data is recorded facts and figures, and information is knowledge derived from data. A database stores data in such a way that information can be created.

Q7. What is Normalization?

Ans: Normalization is process of organizing database. Normalization process usually involves removing or eliminating redundancy data from fields and tables of relational database.

Q8. What is an Entity?

Ans: It is a 'thing' in the real world with an independent existence.

Q9. What are the advantages of DBMS?

Ans: The advantages of DBMS are as follows:

• Redundancy is controlled
• Unauthorised access is restricted
• Providing multiple user interfaces
• Enforcing integrity constraints
• Providing backup and recover

Q10. What is a view? How it is related to data independence?

Ans: A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence. .

Q11. Why is a database considered to be "self-describing"?

Ans: In addition to the users' data, a database contains a description of its own structure. This descriptive data is called "metadata."

Q12. What is normalization?

Ans: It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties

• Minimizing redundancy
• Minimizing insertion, deletion and update anomalies.

Q13. What is Data Model?

Ans: A collection of conceptual tools for describing data, data relationships data semantics and constraints.

Q14. What are different types of Normalization?

Ans: There are different types of Normalization. They are: First Normal Form(1NF), Second Normal Form(2NF), Third Normal Form(3NF), Fourth Normal Form(4NF) and Boyce-Codd Normal Form (BCNF)

Q15. What is SQL, and why is it important?

Ans: SQL stands for Structured Query Language, and is the most important data processing language in use today. It is not a complete programming language like Java or C#, but a data sublanguage used for creating and processing database data and metadata. All DBMS products today use SQL.

Q16. What are the types of constraint?

Ans: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT are some of constraints.

Q17. What is E-R model?

Ans: This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.

Q18. Define constraint?

Ans: Constraint in database set certain rules or property in a table. Suppose if a row has NOT NULL constraint, then it cannot have any null values.

Q19. Write an SQL SELECT statement to display all the columns of the STUDENT table but only those rows where the Grade column is greater than or equal to 90.

Ans: SELECT * FROM STUDENT WHERE Grade >= 90;

Q20. Difference between delete and truncate?

Ans:

• Delete statement is used to delete selected rows from database tables whereas truncate statement deletes all rows from table.
• Delete statement is slower than truncate statement.
• Truncate is DML(Data Manipulation Language) command and Delete is DDL(Data Definition Language) command.

Q21. What is Object Oriented model?

Ans: This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes.

Q22. Define index? Types of index?

Ans: Index is used for faster retrieval of information store and speedup query performance. There are two types of index, clustered index and non-clustered index.

Q23. Whats difference between DBMS and RDBMS?

Ans: DBMS provides a systematic and organized way of storing, managing and retrieving from collection of logically related information. RDBMS also provides what DBMS provides but above that it provides relationship integrity.

Q24. What are aggregate and scalar functions?

Ans: Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table. Scalar functions return a single value based on the input value.
Example:Aggregate – max(), count – Calculated with respect to numeric.
Scalar – UCASE(), NOW() – Calculated with respect to strings.

Q25. What is Union, minus and Interact commands?

Ans: MINUS operator is used to return rows from the first query but not from the second query. INTERSECT operator is used to return rows returned by both the queries.

Q26. How to create database?

Ans: Database can be created by ‘CREATE DATABASE’ Statements. For Example: CREATE DATABASE dbname;

Q27. What is an attribute?

Ans: It is a particular property, which describes the entity.

Q28. What is collation?

Ans: Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters.
ASCII value can be used to compare these character data.

Q29. What is DML (Data Manipulation Language)?

Ans: This language that enable user to access or manipulate data as organised by appropriate data model.

• Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data.
• Non -Procedural DML or High level:DML requires a user to specify what data are needed without specifying how to get those data.

Q30. What is SQL Join?

Ans: SQL join are used for retrieving information and data from two or more tables. SQL Join combine two or more table(or row).

Q31. What is Stored Procedure?

Ans: A stored procedure is a named group of SQL statements that have been previously created and stored in the server database.

Q32. What is Cross-Join?

Ans: Cross join defines as Cartesian product where number of rows in the first table multiplied by number of rows in the second table. If suppose, WHERE clause is used in cross join then the query will work like an INNER JOIN.

Q33. What is DML Compiler?

Ans: It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand.

Q34. How to create Table?

Ans: For creating a Table in database, we have to use ‘CREATE TABLE’ Statement.

For Example:

CREATE TABLE “SomeName”
(“col1” “datatype”,
(“col2” “datatype”);

Q35. What is a Cursor?

Ans: A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.