15 RDBMS Interview Questions and Answers

Relational Database Management Systems (RDBMS) are foundational to modern data storage and retrieval. They provide a structured way to manage data using tables, ensuring data integrity, consistency, and security. RDBMS platforms like MySQL, PostgreSQL, and Oracle are integral to many applications, from enterprise-level systems to small-scale projects, making proficiency in RDBMS a valuable skill in the tech industry.

This article offers a curated selection of interview questions designed to test and enhance your understanding of RDBMS concepts. By working through these questions, you will be better prepared to demonstrate your knowledge and problem-solving abilities in interviews, giving you a competitive edge in the job market.

RDBMS Interview Questions and Answers

1. Explain the concept of normalization and its importance in database design.

Normalization is a process in database design that organizes columns and tables to reduce data redundancy and improve data integrity. It involves several stages, known as normal forms:

• First Normal Form (1NF): Ensures that the table has a primary key and that all columns contain atomic values.
• Second Normal Form (2NF): Ensures that all non-key attributes are fully functionally dependent on the primary key.
• Third Normal Form (3NF): Ensures that all attributes are functionally dependent only on the primary key.
• Boyce-Codd Normal Form (BCNF): A stricter version of 3NF where every determinant is a candidate key.

Normalization helps maintain data integrity, manage data efficiently, and improve query performance.

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

To find the second highest salary from an Employee table, you can use:

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

Alternatively, use the ROW_NUMBER() function:

SELECT salary AS SecondHighestSalary
FROM (
    SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
    FROM Employee
) AS temp
WHERE row_num = 2;

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

ACID properties ensure reliable database transactions:

1. Atomicity: A transaction is treated as a single unit, which either completely succeeds or fails.
2. Consistency: A transaction brings the database from one valid state to another.
3. Isolation: Transactions are isolated from each other until completed.
4. Durability: Once committed, a transaction remains so, even in the event of a system failure.

4. Explain the difference between clustered and non-clustered indexes.

A clustered index determines the physical order of data in a table, allowing only one per table. Non-clustered indexes create a separate object pointing to the data rows, allowing multiple per table. Clustered indexes are useful for range queries, while non-clustered indexes are versatile for various queries.

5. What is a foreign key, and how does it enforce referential integrity?

A foreign key is a column in one table that references the primary key of another table, ensuring referential integrity. For example, in Orders and Customers tables, a foreign key in Orders references the primary key in Customers, ensuring consistency.

CREATE TABLE Customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

6. Write a SQL query to calculate the total sales amount for each customer from the Orders table.

To calculate the total sales amount for each customer from the Orders table:

SELECT customer_id, SUM(sales_amount) AS total_sales
FROM Orders
GROUP BY customer_id;

7. Describe the differences between OLTP and OLAP systems.

OLTP Systems:

• Manage transactional data with a high volume of short transactions.
• Optimized for fast query processing and data integrity.
• Highly normalized databases.
• Used in real-time data processing applications.

OLAP Systems:

• Designed for analytical queries and data warehousing.
• Handle large volumes of data and complex queries.
• Often denormalized to improve query performance.
• Used in business intelligence and decision support systems.

8. Write a SQL query to create a view that shows the top 5 products by sales.

To create a view showing the top 5 products by sales:

CREATE VIEW Top5Products AS
SELECT product_id, product_name, SUM(sales) AS total_sales
FROM sales_table
GROUP BY product_id, product_name
ORDER BY total_sales DESC
LIMIT 5;

9. Explain the concept of sharding and its benefits in distributed databases.

Sharding distributes data across multiple machines to improve performance and scalability. Each shard contains a subset of the data, allowing parallel processing and reducing the load on any single machine. Benefits include improved scalability, performance, availability, and easier maintenance.

10. Write a SQL query to implement a recursive CTE (Common Table Expression) to traverse a hierarchical structure.

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

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT EmployeeID, EmployeeName, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

11. What are isolation levels in SQL, and how do they affect transaction behavior?

Isolation levels in SQL determine transaction integrity visibility:

• Read Uncommitted: Transactions can see uncommitted changes, leading to dirty reads.
• Read Committed: Prevents dirty reads but allows non-repeatable reads.
• Repeatable Read: Prevents dirty and non-repeatable reads but allows phantom reads.
• Serializable: Ensures complete isolation, preventing all read anomalies.

Higher isolation levels provide greater data consistency but can reduce concurrency.

12. Write a SQL query to partition a table by a specific column and retrieve data from a specific partition.

Partitioning divides a large table into smaller pieces, improving query performance and maintenance. To partition a table by a specific column:

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (2023)
);

SELECT * FROM sales PARTITION (p1);

13. Explain the concept of a deadlock and how it can be resolved in a database system.

A deadlock occurs when transactions wait for each other to release resources, halting the system. To resolve deadlocks:

• Detection and Resolution: The system detects and resolves deadlocks by rolling back transactions.
• Prevention: Design the system to avoid deadlocks by acquiring locks consistently.
• Timeouts: Transactions are rolled back if they cannot acquire resources within a set period.

14. Explain the concept of database indexing and its impact on performance.

Database indexing improves data retrieval speed. Indexes allow quick location of rows matching a query, enhancing performance for search conditions, sorting, and grouping. However, they require additional storage and can slow down write operations due to index updates.

15. Explain the concept of eventual consistency in distributed databases.

Eventual consistency in distributed databases achieves high availability and partition tolerance. Updates propagate asynchronously, ensuring all nodes converge to the same state over time. This model contrasts with strong consistency, which requires immediate agreement on data state, often leading to higher latency.