20 DBMS Interview Questions and Answers

Database Management Systems (DBMS) are crucial for efficiently storing, retrieving, and managing data in various applications. From small startups to large enterprises, DBMS solutions like MySQL, PostgreSQL, and Oracle are integral to handling vast amounts of data and ensuring its integrity and security. Mastery of DBMS concepts is essential for roles involving data management, software development, and system administration.

This article offers a curated selection of DBMS interview questions designed to test and enhance your understanding of key concepts. By working through these questions, you will be better prepared to demonstrate your expertise and problem-solving abilities in any technical interview setting.

DBMS Interview Questions and Answers

1. What is a Primary Key and how do you define it in SQL?

A primary key uniquely identifies a record in a database table, ensuring no duplicate or NULL values exist in the primary key column(s). This maintains data integrity and enables efficient data retrieval. To define a primary key in SQL, specify it during table creation or alter an existing table. For example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

Alternatively, add a primary key to an existing table:

ALTER TABLE Employees
ADD PRIMARY KEY (EmployeeID);

2. Write an SQL query to find the second highest salary from an Employee table.

To find the second highest salary from an Employee table, use the DENSE_RANK() window function, which assigns a rank to each row within a partition of a result set. Here’s an example:

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM Employee
) ranked_salaries
WHERE rank = 2;

The inner subquery ranks each salary in descending order, and the outer query filters to return the salary with a rank of 2.

3. How would you implement a foreign key constraint in SQL?

A foreign key constraint links two tables, ensuring the value in the child table matches a value in the parent table, maintaining referential integrity. Define the foreign key in the child table and reference the primary key of the parent table. For example:

CREATE TABLE parent_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE child_table (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);

Here, parent_id in child_table references id in parent_table.

4. Describe the ACID properties in the context of transaction management.

ACID properties ensure reliable database transactions: Atomicity (transactions are all-or-nothing), Consistency (transactions bring the database from one valid state to another), Isolation (transactions do not interfere with each other), and Durability (committed transactions persist despite failures).

5. Write an SQL query to join two tables and select specific columns from both.

In SQL, a join combines rows from two or more tables based on a related column. Here’s an example of joining two tables, employees and departments, and selecting specific columns:

SELECT 
    employees.employee_id, 
    employees.first_name, 
    employees.last_name, 
    departments.department_name
FROM 
    employees
INNER JOIN 
    departments 
ON 
    employees.department_id = departments.department_id;

This query joins the tables using the department_id column and selects specific columns from each table.

6. What are indexes and how do they improve query performance?

Indexes are data structures that speed up data retrieval. They are created on columns frequently used in query conditions. Types include primary, unique, clustered, and non-clustered indexes. While indexes improve query performance by reducing data scanning, they also increase storage requirements and can affect data modification operations.

7. How would you handle a many-to-many relationship in a relational database?

A many-to-many relationship in a relational database is managed using a junction table, which breaks down the relationship into two one-to-many relationships. The junction table contains foreign keys referencing the primary keys of the related tables. For example:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE Enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Here, Enrollments is the junction table.

8. Explain the concept of a database transaction log.

A database transaction log records all transactions and changes, aiding in data recovery, concurrency control, and providing an audit trail. It includes transaction ID, operation type, affected data, and timestamps, crucial for rollback and roll-forward operations.

9. Write an SQL query to perform a recursive CTE (Common Table Expression).

A recursive CTE (Common Table Expression) performs recursive queries, useful for hierarchical data structures. Here’s an example to find an employee hierarchy:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT EmployeeID, ManagerID, EmployeeName
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

This CTE retrieves all levels of the hierarchy.

10. What are triggers and how do you use them in SQL?

Triggers are stored procedures that execute automatically when specific events occur in a database table. They can enforce business rules, maintain audit trails, and ensure data integrity. For example:

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    SET NEW.last_modified = NOW();
END;

This trigger updates the last_modified column before any update on the employees table.

11. What is a deadlock and how can it be resolved in a DBMS?

A deadlock occurs when transactions wait indefinitely for each other to release resources. Strategies to resolve deadlocks include prevention, detection, avoidance, and timeouts. Prevention involves acquiring all required locks at once, detection checks for cycles in resource allocation, avoidance carefully allocates resources, and timeouts roll back transactions after a set period.

12. Write an SQL query to implement a full outer join without using the FULL OUTER JOIN keyword.

A full outer join returns all records with matches in either table. If not supported, use a combination of LEFT JOIN, RIGHT JOIN, and UNION:

SELECT table1.*, table2.*
FROM table1
LEFT JOIN table2 ON table1.id = table2.id

UNION

SELECT table1.*, table2.*
FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;

This simulates a full outer join.

13. Explain the CAP theorem and its implications for distributed databases.

The CAP theorem states that a distributed system cannot simultaneously provide Consistency, Availability, and Partition Tolerance. Systems must trade-off between these properties, such as prioritizing consistency and partition tolerance (CP) or availability and partition tolerance (AP).

14. Write an SQL query to rank employees based on their salaries within each department.

To rank employees by salary within each department, use the RANK() function:

SELECT 
    employee_id,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM 
    employees;

This query ranks employees within their department based on salary.

15. What is a materialized view and how does it differ from a regular view?

A materialized view stores query results on disk, improving performance for complex queries. Unlike regular views, which are computed dynamically, materialized views need refreshing to reflect data changes. They are useful when query performance is important, and data changes infrequently.

16. Explain data integrity and the different types of constraints used to enforce it.

Data integrity ensures data accuracy and consistency. Constraints like primary key, foreign key, unique, not null, check, and default enforce integrity by ensuring data validity and adherence to rules.

17. Discuss the importance of database security and some common practices to ensure it.

Database security protects data from unauthorized access and breaches. Practices include access control, encryption, regular audits, patch management, backup and recovery, and data masking. These measures help maintain data integrity, confidentiality, and availability.

18. Describe the process of backing up and recovering a database.

Backing up and recovering a database involves creating copies of the database and restoring it to a previous state. Backup types include full, incremental, and differential. The process involves determining backup type, creating backups, storing them securely, and using tools to restore and apply backups as needed.

19. Compare and contrast NoSQL databases with relational databases.

NoSQL databases, like MongoDB and Cassandra, handle large volumes of data and frequent schema changes. They differ from relational databases in data model, schema flexibility, scalability, and transaction support. NoSQL databases are suitable for applications with large-scale data storage needs and flexible data models.

20. What are the different types of NoSQL databases and their use cases?

NoSQL databases are categorized into document, key-value, column-family, and graph databases, each suited for different use cases. Document databases like MongoDB store data in documents, key-value stores like Redis use key-value pairs, column-family stores like Cassandra handle large data volumes, and graph databases like Neo4j manage complex relationships.