Top 35 SQL Query & Database Interview Questions

SQL is mandatory for Software Engineers, Data Engineers, and Data Analysts. Here are the most critical practical queries and database architecture questions asked in hiring assessments.

---

1. Finding the Nth Highest Salary (Crucial Query)

Using DENSE_RANK() Window Function (Recommended):

WITH RankedSalaries AS (
    SELECT employee_id, first_name, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) as rank_num
    FROM employees
)
SELECT employee_id, first_name, salary
FROM RankedSalaries
WHERE rank_num = 2; -- Returns 2nd highest salary

Using Subquery & LIMIT / OFFSET:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1; -- 2nd highest

---

2. Understanding SQL Joins with Diagrams

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

-- Fetch all employees and their respective department names (including employees without department)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

---

3. Difference Between WHERE and HAVING

• WHERE filters individual rows before aggregation happens. It cannot contain aggregate functions like SUM(), AVG(), or COUNT().
• HAVING filters aggregated groups after the GROUP BY operation.

-- Correct usage of WHERE and HAVING
SELECT department_id, COUNT(*) as employee_count, AVG(salary) as avg_sal
FROM employees
WHERE salary > 30000          -- Filters individual salaries first
GROUP BY department_id
HAVING COUNT(*) >= 5;         -- Filters departments having 5 or more employees

---

4. Difference Between RANK(), DENSE_RANK(), and ROW_NUMBER()

Given salaries: [5000, 5000, 4000, 3000]

• ROW_NUMBER(): Generates strictly unique sequential integers: [1, 2, 3, 4].


• RANK(): Assigns the same rank to identical values, but skips subsequent ranks: [1, 1, 3, 4].


• DENSE_RANK(): Assigns the same rank to identical values without skipping numbers: [1, 1, 2, 3].

5. ACID Properties in Relational Databases

• Atomicity: The "all-or-nothing" rule. Either all statements in a transaction succeed, or the entire transaction is rolled back.
• Consistency: Transactions preserve all database integrity constraints and foreign keys.
• Isolation: Concurrent transactions execute independently without interfering with each other.
• Durability: Once a transaction is committed, changes are permanently written to non-volatile storage, even during system crashes.