Here are the top 50 commonly asked questions in RDBMS interviews. Whether you’re just starting your preparation or need a quick refresher, these questions and answers will boost your confidence for the interview. Ranging from basic to advanced, they cover a wide array of RDBMS concepts. Practice these questions for campus and company interviews, positions from entry to mid-level experience, and competitive examinations. It’s also important to practice them to strengthen your understanding of RDBMS.
RDBMS Interview Questions with Answers
1. What is relational database?
Relational database is a type of database in which data is stored and organized in form of relations (tables). It is based on relational model proposed by E.F Codd.
2. What is the difference between DBMS and RDBMS?
Database Management System (DBMS) is the software used for efficient storage and efficient retrieval of data. Different types of DBMS exist for different application based on data management needs. RDBMS is a DBMS which is based on the relational model.
3. What are the advantages of database management system?
Database management system overcomes nearly all the drawbacks of file processing system. Features of database management system are as follows:
- Concurrent access
- Improved Data Security
- Data Integrity constraints are present
- Recovery of data in case of failures
- Efficient management
- Efficient retrieval
- High availability
4. Explain different functions provided by RDBMS?
RDBMS offer several functions that help us overcome problems associated with file based systems
- Integrity-Maintain accuracy of data
- Utilities-Data import-export tools are available
- Security-Access to authorized users only
- Concurrency control-simultaneous access to multiple users at the same time
5. Which consistency model is supported by RDBMS for transaction?
ACID consistency model is a set of attributes that assures that database transactions are processed in a reliable manner. Acronym ACID stands for Atomicity, Consistency, Isolation and Durability. BASE model is supported by non-relational databases.
6. What are the limitations of flat files?
The file processing system based approach suffers following problems:
- Security
- Complex process to access data
- Data redundancy and isolation
- Physical data dependency
- Loss of concurrent access
- Data Integrity
7. Which language is used to deal with relational databases?
Structured Query Language is a descriptive language used for managing data stored in a relational database management system (RDBMS).
8. Explain the difference between physical and logical data independence?
Data independence is a technique in which data stored is kept separately from applications that use data.
- Physical Data Independence: Physical data can be modified without having alteration in logical schema of data
- Logical Data Independence: Conceptual schema can be modified without having alteration in application programs
9. What are different types of database system (model) with example?
Types of database systems are as follows:
- Relational: Microsoft SQL Server
- Non-relational: MongoDB
- Hierarchical: IBM Information Management System
- Network: Integrated Data Store
10. Explain types of data integrity rules and constraints?
Data integrity refers to maintaining and assuring the accuracy and consistency of data.
Types of Integrity are as follows:
- Entity Integrity: Each table must have a column or a set of columns through which we can uniquely identify a row
- Domain Integrity: All attributes in a table must have a defined domain
- Referential Integrity: Every value of a column in a table must exist as a value of another column in a different or same table
11. Which approach is used to enforce entity integrity constraint?
Entity Integrity is enforced using primary key constraint. The PRIMARY KEY constraint uniquely identifies each record in a database table. Each table can have only one primary key. Primary key cannot contain null value.
12. Which data model is used for describing relationship in relational model?
ER (Entity-Relationship) data model is used for logical representation of relationship between different entities. Entity is a real world object which has certain attributes to describe itself. Relationship is an association among two or more entities.
13. What is the difference between primary key and foreign key?
Differences are as follows:
- Table has only one primary key whereas there is possibility of more than one foreign key in a table
- Primary key cannot contain null value whereas foreign key can contain null value
- Primary key is easily searchable as compared to foreign key due to default clustered index
14. Explain the difference between candidate key and primary key?
A Candidate Key is a minimal set of columns/attributes that can be used to uniquely identify a single tuple in a relation. Primary key is the candidate key that is selected to uniquely identify a tuple in a relation.
Candidate Keys are determined during database design based on the underlying business rules of the database whereas primary keys are identified during implementation phase.
Example: Let us identify primary and candidate key from below schema:
Customer (CustomerId, Name, Salary, DOB)
Name and DOB both can be considered as candidate key but CustomerId is a more suitable candidate key for primary key constraint.
15. Explain recursive relationship with real-life example?
A recursive relationship connects a single class type (serving in one role) to itself .A relationship can also exist between instances of same entity, e.g. an Employee reports to another Employee.
16. What are the cardinality types in relational model?
Cardinality of relationship is the number of instances in one entity which is associated to the number of instances in another. A data table’s cardinality with respect to another data table is one of the most critical aspects in database design.
Cardinality of relationships are of three types: 1:1, 1:N and M:N.
17. Explain one to one and many to many relationship with example?
1:1 relationship represents association between single occurrence of one entity and a single occurrence of the second entity. A person can sit on one chair at any point of time.
M:N relationship represents association between multiple occurrences of both entities. In M : N relationships, the relationship is represented by a completely new table that has a composite primary key. An Author can write several Books, and a Book can be written by several Authors.
18. Explain referential integrity in relational database with example?
Referential integrity is violated when the relation to which a foreign key refers no longer exists in the database. Referential integrity constraint is enforced through foreign key implementation. Different types of referential integrity constraints are applied on the database based on application needs.
19. Describe different types of SQL commands?
SQL is use to manage data in relational databases like SQL Server. SQL uses different commands as follows:
- DDL: Data Definition Language is used for defining database schema
- DML: Data Modification Language is used for modification of database values
- DCL: Data Control Language is used to control access to data stored in a database
- TCL: Transaction Control Language is used to process transaction in a database
20. Explain different types of DDL commands in SQL?
Different DDL commands are as follows:
- CREATE: This command is used for creation of new database objects
- ALTER: This command is used to alter database schema
- DROP: This command is used to drop the database objects like tables and columns
- TRUNCATE: This command is used to remove all values from the table
21. What is the difference between DELETE and TRUNCATE command in SQL?
Difference between DELETE and TRUNCATE are as follows:
- TRUNCATE is a DDL command whereas DELETE is a DML command
- Delete is slower than TRUNCATE command
- Rollback is not possible with TRUNCATE command
- Where condition cannot be used with TRUNCATE command
DELETE and TRUNCATE both can be rolled back when used with transaction.
22. Explain different types of DML commands in SQL?
Different DML commands are as follows:
- INSERT: This command is used to insert tuples in to the table
- UPDATE: This command is used to update existing values in the table
- DELETE: This command is remove some or all values from the table
- SELECT: This command is used to retrieve data from the database
23. What is Normalization in database and how do you achieve it?
Normalization is the process of minimizing data redundancy by reorganizing database schema. Normalization is achieved through various normal forms like 1NF, 2NF, 3NF and so on. Generally database is normalized till 3NF for transaction based database application.
24. Which of the SQL command type is related to user controls in database system?
Data Control language mostly deals with rights and permission to access specific database object for security purposes. DCL commands are of mainly two types:
- GRANT: This command is used to allow access privileges to users on the database.
- REVOKE: This command is used to withdraw back access privileges given to users on the database.
25. Explain the difference between commit and rollback command in SQL?
Commit and Rollback are transaction control language commands. They are specifically used to deal with the changes made by DML statements.
- COMMIT: This command is used to permanently save any change made in the database.
- ROLLBACK: This command is used to undo the changes made by any command but only before a commit is done.
Advanced RDBMS Interview Questions with Answers
26. What is the difference between single row function and multi row function?
SQL functions are built in modules provided by a database. All functions return a single value. They are categorized into two types based on number of rows they operate upon. They are as follows:
Single Row Function: These functions works with single row at a time. It produces one row of output for each row of input.
Example: Character functions like UPPER, LOWER etc.
Multiple Row Function: These functions works with multiple rows at a time. It always gives single row output, irrespective of the number of input rows.
Example: Aggregate functions like SUM, AVG etc.
27. When do we prefer denormalization of database design over normalization in the database?
Denormalization is the technique used to optimize the read performance of a database by adding data redundancy.
Normalized database design is preferred in a state where applications are performing intensive write operations rather than read operations. It is more suitable for transaction based applications.
Denormalized database design is preferred in a state where applications are performing intensive read operations rather than write operations. It is more suitable for reporting based applications.
28. What is the difference between UNION and UNION ALL operator?
UNION and UNION ALL clause is used to combine results from two or more SELECT statements. The select statements may be from same or different tables. They must have same number of columns and their data types at same position in both the query must be compatible. UNION command displays distinct rows whereas UNION ALL command displays all rows. UNION and UNION ALL give same result if the participating queries are mutually exclusive.
29. Why does each database table have only one clustered index?
Indexes are categorized in to two types based on ordering of data. They are as follows:
- Clustered: It defines physical ordering of data.
- Non-clustered: It defines logical ordering of data.
Clustered index is the index in which data is physically sorted .Each table can have only one clustered index because data in the primary table is physically sorted in that order. Usually this is created on the primary key columns.
30. Explain the scenario in which clustered and non-clustered index should be used?
A clustered index defines the way in which rows are physically stored. When you create a clustered index on a column, it sorts the table’s rows by that column. A non-clustered index, on the other hand, does not define the way in which rows are physically stored. It creates a completely new object inside the table that contains the column selected for indexing and a pointer back to the table’s rows containing the data.
31. Why do we need indexes and also enlist drawbacks of index?
Index is a database object that allows us to search data in tables quickly and efficiently thereby avoiding table scan. Indexes are also used to enforce data integrity. An index is automatically created for constraint enforcement when you create Primary Key and Unique constraint.
Drawbacks of Index:
- Index consumes additional space thereby requiring more time for backup and recovery of data
- Some indexes have to be explicitly maintained by DBAs
- DML operations are impacted due to continuous update of database servers.
32. Explain the different types of join in SQL?
Join is used to combine rows from multiple tables.
Types of Join are as follows:
- Inner Join: Inner join returns all the matching rows from the table based on join condition
- Self Join: It is a type of inner join in which table is joined to itself
- Outer Join: It joins the matching records from two table and all the records from one side of the table and those unmatched records which have null value.
- Left Outer Join: It joins the matching records from two table and all the records from left side of the table and those unmatched records which have null value
- Right Outer Join: It joins the matching records from two table and all the records from right side of the table and those unmatched records which have null value
- Full Outer Join: Regardless of matching record of both side of the tables, it produce all the records of both the table and produce NULL values for every column of the table that lacks of matching row.
- Cartesian (or Cross) Join: It is a join of every row of one table to every row of another table.
33. Explain SELF join in SQL with an example?
SELF Join is a type of inner join which represents join of a table with itself. The self join process uses nested loops to iterate through all rows of both the tables and identifies rows that meet the join criteria.
Example: To display the employee and its corresponding manager information in the same table, we need to use self join.
34. Write the query to display nth highest salary without using inbuilt functions?
Following query displays nth highest salary:
SELECT * /* This is the outer query part */ FROM Employee Emp1 WHERE (n-1) = ( /* Subquery starts here */ SELECT COUNT (DISTINCT (Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)
35. What is the difference between join and subquery?
Join is used to fetch data from multiple tables in a database. Subquery is a query within a query.
Differences are as follows:
- Subqueries are more intuitive, readable and logical than join queries
- Joins are generally faster than subqueries
- Subqueries are generally used with aggregate functions
36. What is the difference between independent and correlated subquery?
Subqueries is categorized in to following types:
- Independent subqueries: In an independent subquery, the inner and outer query are independent of each other. Inner query can be executed irrespective of outer query. Independent subquery are further classified into single row and multiple row types depending upon the number of rows returned. In case of independent subquery, the inner query just executes once.
- Correlated subqueries: A Correlated subquery is one in which the inner query that depends upon the outer query for its execution. The inner query is executed iteratively for each selected row of the outer query.
37. What is the difference between view and materialized view?
A view uses a query to pull data from the underlying tables. A materialized view is a table on disk that contains the result set of a query. Materialized views are primarily used to increase application performance when it isn’t desirable to use a standard view with indexes applied to it. Materialized views are schema objects and stores the results of a query in a separate schema object.
38. Why do we need stored procedures and enlist the drawbacks of it?
Stored procedure are reusable code that are stored in databases and can be invoked by any client. Stored Procedures improve performance by minimizing the number of network calls between client and server. Stored Procedure execute much faster than SQL statements. Stored procedures also improve security as DBAs can grant access to stored procedures and there is no need to give access to underlying tables. Additionally stored procedures also promote modularity and code reuse.
Drawbacks of Stored Procedure:
- Version control can be a pain
- Debugging is relatively difficult
- Complexify the code maintenance
Sometimes stored procedure degrades application performance due to utilization of server resources in the memory.
39. Why do we need transactions in database application?
Transaction is a logical unit of work containing one or more operations on a database. A transaction provides two important functions:
- Ensures that all operations within a transaction happens in an atomic manner
- Provides capability to undo the partial processing in the event of failure at any step
Transaction operations are atomic by default. For e.g. a table cannot be created partially with less columns, an insert statement cannot insert a partial record etc. All operations that alter the state of database always happen in a transaction. DB servers automatically starts a transaction whenever they encounter the first executable SQL statement.
40. Explain different states in a database transaction?
A transaction in a database can be in one of the following states:
- Active: Transaction is in this state while executing.
- Partially committed: Transaction is in this state after the final statement is executed.
- Failed: Transaction is in this state when normal execution cannot proceed.
- Aborted: Transaction is in this state after rolling back.
- Committed: Transaction is in this state after successful completion.
41. Explain the importance of execution plan?
Execution plan is an ordered set of steps used to retrieve data in a SQL RDBMS. Better the execution plan, less the time required to access the data. There are n number of execution plans for single SQL query. A given database management system may offer one or more mechanisms for returning the plan for a given query. Query optimizer will choose the most efficient query execution plan generally. Execution plan needs to be analyzed for database and query tuning.
42. Explain some of the SQL best performance practices?
The performance of SQL statements depends upon how well they are written.
- Do not use Select ‘*’, instead fetch only attributes that are required. This reduces I/O operations while fetching data from tables and also improves code maintainability
- Avoid use of DISTINCT clause as database server has to perform additional work to remove duplicates
- Avoid unnecessary computational overheads in WHERE or HAVING clause
- Avoid functions in where clause as the index will not be used if the indexed column is referred as part of an expression
- Use UNION all instead of UNION if you are sure that the two results are mutually exclusive.
- Do not use HAVING clause without any aggregate functions
43. What is the order in which query is executed in RDBMS?
Following shows the order in which query is executed:
- Firstly FROM clause is executed
- joins are evaluated
- WHERE clause
- group by operation
- having operation on group of rows
- select command
- distinct clause to remove duplicates
- Order by clause for sorting data
44. What is the difference between stored procedure and cursor?
Stored procedure are something that can have collection of SQL statements (including cursor) in it. They are mainly used for data validation. Stored procedures are pre-compiled objects and executes as bulk of statements.
Cursor is used for row by row processing of data. Cursor can be created inside stored procedure for fetching multiple rows one by one.
45. State the condition in which 3NF and BCNF are equivalent?
A relation R is said to be in the Third Normal Form (3NF) if and only if:
- It is in 2NF and
- Transitive dependency does not exists between key attributes and non-key attributes through another non-key attribute.
BCNF does not make a distinction between key and non-key attributes but requires that every nontrivial determinant is a superkey even its dependent attributes happen to be part of a key. BCNF is stricter than 3NF.
3NF and the BCNF are equivalent provided there is only one candidate key in the table.
46. What are the anomalies caused due to denormalized database design?
Insert, Update and Delete Anomalies are very frequent if Database is not normalized. Most 3NF tables are free of insertion, update, and deletion anomalies.
- Insert Anomaly: This occurs when certain attributes cannot be inserted into the database without the presence of other attributes.
- Update Anomaly: This occurs when one or more instances of duplicated data is updated, but not all.
- Delete Anomaly: This occurs when certain attributes are lost because of the deletion of other attributes.
47. Explain type of functional dependencies in the database?
An attribute A is said to functionally determine attribute B if each value of A is associated with only one value of B.
- Full Functional Dependency: If A and B are attributes of a relation, B is fully functionally dependent on A if it is functionally dependent on A, but not on any subset of A.
- Partial Functional Dependency: If A and B are attributes of a relation, B is partially dependent on A if it is dependent on subset of A.
- Transitive Functional Dependency: If A, B, and C are attributes of a relation such that if A -> B and B -> C, then C is transitively dependent on A via B.
48. When do we need triggers and also enlist drawbacks of it?
A trigger is an action that can be set to run when an event occur. Triggers are special type of stored procedures used mainly for auditing purposes. It is also used for Validation and business security checking.
Drawbacks of triggers are as follows:
- Triggers increases load on the server thereby making system slow.
- Triggers are difficult to view
- Difficult to maintain if there is no documentation.
49. Which set operations are supported by SQL?
SQL supports few Set operations to be performed on table data.
- UNION: It is used to combine the results of two or more Select statements
- UNION ALL: This operation is similar to Union. But it doesn’t eliminate duplicate rows
- INTERSECT: It is used to return the records which are common from both SELECT statements. This operator is not supported by some of the RDBMS
- MINUS: It combines result of two Select statements and return only those result which belongs to first set of result. This operator is not supported by some of the RDBMS
50. What are the different SQL dialects used in popular RDBMS?
SQL is an ANSI (American National Standards Institute) standard. Each RDBMS vendor have specific implementation of SQL for database management.
- MS SQL Server uses Transact SQL (T-SQL)
- Oracle uses PL/SQL, or Procedural Language/SQL
- Sybase also uses T-SQL
- DB2 uses DB2 SQL
- MySQL uses SQL/PSM
Useful Resources:
