10 Relational Databases Interview Questions and Answers

Relational databases are a cornerstone of modern data management, providing a structured and efficient way to store, retrieve, and manipulate data. They are widely used across various industries due to their robustness, scalability, and ability to handle complex queries. Mastery of relational databases, including understanding SQL and database design principles, is a critical skill for many technical roles.

This article offers a curated selection of interview questions designed to test your knowledge and problem-solving abilities with relational databases. By working through these questions, you will gain a deeper understanding of key concepts and be better prepared to demonstrate your expertise in a professional setting.

Relational Databases 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 dividing a database into multiple tables and defining relationships between them. The process follows a series of steps 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): Achieved when the table is in 1NF and all non-key attributes are fully functionally dependent on the primary key.
• Third Normal Form (3NF): Achieved when the table is in 2NF and all attributes are functionally dependent only on the primary key.
• Boyce-Codd Normal Form (BCNF): A stronger version of 3NF where every determinant is a candidate key.

Normalization is important because it:

• Reduces data redundancy, saving storage space and ensuring consistency.
• Improves data integrity by ensuring logical data dependencies.
• Facilitates easier maintenance and updates by organizing data into logical units.
• Enhances query performance by reducing schema complexity.

2. Describe the ACID properties and why they are important in relational databases.

ACID properties ensure reliable processing of database transactions. They stand for Atomicity, Consistency, Isolation, and Durability.

• Atomicity: Ensures a transaction is treated as a single unit, which either completely succeeds or fails. If any part fails, the entire transaction is rolled back.
• Consistency: Ensures a transaction brings the database from one valid state to another, maintaining all predefined rules.
• Isolation: Ensures concurrent transactions do not affect each other. Intermediate states of a transaction are invisible to others.
• Durability: Guarantees that once a transaction has been committed, it will remain so, even in the event of a system failure.

3. Explain the concept of indexing and how it improves query performance.

Indexing involves creating a data structure that improves the speed of data retrieval operations. An index is created on one or more columns of a table, allowing the database to find rows more quickly.

Types of indexes include:

• Primary Index: Automatically created with a primary key, ensuring uniqueness and fast retrieval.
• Unique Index: Ensures all values in the indexed column are unique.
• Clustered Index: Sorts data rows in the table based on the index key. A table can have only one clustered index.
• Non-Clustered Index: Contains a sorted list of references to the table data. A table can have multiple non-clustered indexes.

Indexes improve query performance by reducing the amount of data the database engine needs to scan.

4. What are foreign keys, and how do they enforce referential integrity?

A foreign key is a column in one table that uniquely identifies a row of another table, enforcing referential integrity by ensuring a value in the child table corresponds to a valid value in the parent table.

Example:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

In this example, the DepartmentID column in the Employees table is a foreign key referencing the DepartmentID column in the Departments table.

5. Write an SQL query to join three tables: Employees, Departments, and Projects, and retrieve relevant information.

To join three tables in SQL, use the JOIN clause to combine rows from the Employees, Departments, and Projects tables based on related columns.

Example SQL query:

SELECT 
    e.employee_id,
    e.employee_name,
    d.department_name,
    p.project_name
FROM 
    Employees e
JOIN 
    Departments d ON e.department_id = d.department_id
JOIN 
    Projects p ON e.project_id = p.project_id;

In this query:

• The Employees table is aliased as “e”.
• The Departments table is aliased as “d”.
• The Projects table is aliased as “p”.
• The JOIN clause combines rows based on the department_id and project_id columns.

6. Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Joins combine rows from two or more tables based on a related column. The main types are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

• INNER JOIN: Returns only rows with matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table. If no match, the result is NULL on the right side.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matched rows from the left table. If no match, the result is NULL on the left side.
• FULL OUTER JOIN: Returns all rows when there is a match in either table. If no match, the result is NULL on the side with no match.

Example SQL queries:

-- INNER JOIN
SELECT a.*, b.*
FROM table_a a
INNER JOIN table_b b ON a.id = b.id;

-- LEFT JOIN
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id;

-- RIGHT JOIN
SELECT a.*, b.*
FROM table_a a
RIGHT JOIN table_b b ON a.id = b.id;

-- FULL OUTER JOIN
SELECT a.*, b.*
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;

7. What is a stored procedure, and when would you use one?

A stored procedure is a set of SQL statements stored in the database and executed as a single unit. They encapsulate complex operations, enforce business rules, and improve performance by reducing data transfer between the client and server. Stored procedures can accept input parameters, return output parameters, and handle transactions.

Example:

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT
AS
BEGIN
    SELECT FirstName, LastName, Department
    FROM Employees
    WHERE EmployeeID = @EmployeeID
END

In this example, the stored procedure GetEmployeeDetails takes an EmployeeID as an input parameter and returns the first name, last name, and department of the specified employee.

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

A recursive CTE (Common Table Expression) is used to perform recursive queries, useful for traversing hierarchical data structures.

Example:

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;

In this example, the CTE EmployeeHierarchy is defined with two parts:

• The anchor member selects the root of the hierarchy (employees with no manager).
• The recursive member joins the CTE with the Employees table to find all employees reporting to the current level of managers.

The UNION ALL operator combines these two parts, and recursion continues until no more rows are returned.

9. Explain the concept of database sharding and its benefits and challenges.

Database sharding partitions a large database into smaller, more manageable pieces called shards. Each shard is a separate database that can be hosted on a different server.

Benefits of Database Sharding:

• Scalability: Allows the database to scale horizontally by adding more servers.
• Performance: Distributes data across multiple servers, reducing query response times.
• Fault Tolerance: If one shard fails, others can continue to operate.

Challenges of Database Sharding:

• Complexity: Adds complexity to the database architecture.
• Data Distribution: Ensuring even distribution of data across shards can be challenging.
• Consistency: Maintaining data consistency across multiple shards can be difficult.
• Operational Overhead: Managing multiple database instances increases operational overhead.

10. Explain different transaction isolation levels and their impact on database operations.

Transaction isolation levels define the degree to which operations in one transaction are isolated from others. The four standard levels are:

• Read Uncommitted: The lowest level, where transactions can read uncommitted data, leading to dirty reads, non-repeatable reads, and phantom reads.
• Read Committed: Transactions can only read committed data, preventing dirty reads but allowing non-repeatable reads and phantom reads.
• Repeatable Read: Ensures that if a transaction reads a row, subsequent reads will return the same data, preventing dirty and non-repeatable reads but allowing phantom reads.
• Serializable: The highest level, where transactions are completely isolated, preventing dirty reads, non-repeatable reads, and phantom reads, but potentially reducing concurrency.