DBMS Interview Questions & Final Recommendations

This final chapter synthesizes core database management concepts, tackles frequently asked advanced SQL interview questions (such as finding the second highest salary), and reviews distributed systems principles like the CAP Theorem to prepare you for technical interviews.

1. Why Do We Need Interview Prep & Complex Queries?

• Bridging Theory and Practice: Knowing relational algebra, ACID properties, and normalization forms is essential, but interviewers frequently test your ability to translate business logic into optimized SQL queries.
• Handling Edge Cases: Real-world datasets contain duplicates, ties, and missing values. Knowing multiple ways to solve a problem (e.g., using LIMIT/OFFSET, subqueries, or window functions) demonstrates engineering maturity.
• Distributed System Trade-offs: Modern interviews often evaluate your architectural judgment using distributed database trade-offs like the CAP Theorem.

2. Core Mental Model: The Query Strategy Spectrum

When approached with a query problem in an interview, you should navigate a spectrum of solutions ranging from simple approaches to advanced analytic functions:

3. Reviewing Distributed Systems: CAP Theorem Basics

As discussed across distributed database modules, the CAP Theorem dictates that a distributed data store can simultaneously provide at most two of the following three guarantees:

4. Problem Statement: Finding the Second Highest Salary

A classic SQL interview question is: "Write a query to find the second highest salary from an Employee table."

Let's assume an Employee table with the following schema:

CREATE TABLE Employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10, 2)
);

Below, we explore three distinct approaches to solving this problem, ranging from naive utility to advanced analytic functions.

5. Method 1: Using ORDER BY, LIMIT, and OFFSET

The most intuitive and straightforward way to retrieve a specific rank is to sort the records in descending order and use pagination clauses.

SQL Implementation

SELECT salary 
FROM Employee 
ORDER BY salary DESC 
LIMIT 1 OFFSET 1;

How It Works

• ORDER BY salary DESC: Sorts all employee salaries from highest to lowest.
• LIMIT 1: Restricts the output to only 1 row.
• OFFSET 1: Skips the first row (the absolute maximum salary) so that the second row is returned.

Note: While simple and easy to write, LIMIT and OFFSET can become inefficient on massive datasets because the database engine still sorts the entire result set or scans indices before applying the offset.

6. Method 2: Using Subqueries and Aggregate Functions (MAX)

If the database dialect does not support LIMIT/OFFSET (or if you prefer a standard subquery approach), you can use aggregate functions.

SQL Implementation

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

How It Works

1. Inner Subquery (SELECT MAX(salary) FROM Employee): Finds the absolute highest salary in the entire table.
2. Outer Query (WHERE salary < ...): Filters out the maximum salary, leaving all lesser salaries.
3. MAX(salary) on Outer Query: Finds the maximum among the remaining salaries, which is precisely the second highest salary.

7. Method 3: Using Advanced Window Functions (DENSE_RANK)

For enterprise environments and senior-level interviews, window functions are the preferred approach because they easily scale to find the $N^{\text{th}}$ highest salary.

SQL Implementation

WITH RankedSalaries AS (
    SELECT 
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
    FROM Employee
)
SELECT DISTINCT salary 
FROM RankedSalaries 
WHERE rnk = 2;

How It Works

• DENSE_RANK() OVER (ORDER BY salary DESC): Assigns a sequential rank to rows based on their salary in descending order, without gaps in ranking numbers (even if multiple employees share the exact same salary).
• Common Table Expression (CTE) (WITH RankedSalaries AS (...)): Wraps the ranking logic into a temporary virtual table.
• Final Selection: Filters the CTE for rows where rnk = 2.

8. Generalizing to the $N^{\text{th}}$ Highest Salary

When interviewers expand the question from "second highest" to "$N^{\text{th}}$ highest", the subquery method becomes cumbersome. The window function approach shines here because you can parameterize or change the rank filter easily:

-- General template for Nth highest salary using DENSE_RANK
WITH RankedEmployees AS (
    SELECT 
        name,
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) as salary_rank
    FROM Employee
)
SELECT name, salary
FROM RankedEmployees
WHERE salary_rank = N; -- Replace N with desired integer (e.g., 3, 5, etc.)

9. Summary and Key Takeaways

• Master Multiple Approaches: Be prepared to write queries using LIMIT/OFFSET, subqueries with MAX(), and advanced window functions like DENSE_RANK().
• Understand Distributed Trade-offs: Review the CAP Theorem thoroughly to explain how distributed databases handle consistency versus availability during network partitions.
• Handle Ties Gracefully: DENSE_RANK() is superior to ROW_NUMBER() when dealing with duplicate salary values among different employees.
• Practice Complex Queries: Complex SQL queries are standard filters in advanced database and backend engineering interviews.
• Utilize Course Resources: Review downloadable PDF lecture notes and track your progress across DSA, System Design, and Full Stack Web Development tracks via community channels.