SQL Interview Questions

Find Nth Highest Salary

Query Most Asked 3 min

Use LIMIT with OFFSET, or DENSE_RANK() window function. Handle duplicates carefully — DISTINCT or DENSE_RANK is key.

This is THE most asked SQL interview question. There are multiple approaches, and interviewers want to see you understand the trade-offs. The trick is handling duplicate salaries — if 3 people have the highest salary, what's the "2nd highest"?

SQL — Method 1: LIMIT OFFSET

                -- Find 3rd highest salary (N=3)
                SELECT DISTINCT salary
                FROM Employee
                ORDER BY salary
                DESC LIMIT
                1 OFFSET
                2;
                -- OFFSET = N-1
              

SQL — Method 2: DENSE_RANK() (Best)

                -- Find Nth highest using window function
                SELECT salary
                FROM (
                SELECT salary,
                DENSE_RANK()
                OVER (ORDER BY
                salary DESC)
                AS rnk FROM
                Employee
                ) ranked
                WHERE rnk = 3;
                -- N = 3
              

SQL — Method 3: Subquery (Classic)

                -- Find Nth highest using correlated subquery
                SELECT DISTINCT salary
                FROM
                Employee e1
                WHERE 3 = (
                -- N = 3
                SELECT COUNT(DISTINCT
                salary) FROM
                Employee e2
                WHERE e2.salary >= e1.salary );
              

Key Insight

DENSE_RANK vs RANK vs ROW_NUMBER: DENSE_RANK handles ties without gaps (100, 100, 90 → ranks 1, 1, 2). RANK creates gaps (1, 1, 3). ROW_NUMBER gives unique numbers (1, 2, 3).
Use DISTINCT with LIMIT/OFFSET approach to handle duplicate salaries
DENSE_RANK is the most interview-friendly — shows you know window functions

Common Mistake

Using RANK() instead of DENSE_RANK(). If two people share the highest salary, RANK() makes the next salary rank 3, not 2. Also, forgetting DISTINCT with LIMIT/OFFSET returns wrong results when salaries repeat.

Find 2nd Highest Salary

Query 2 min

Special case of Nth highest. Use MAX with subquery or LIMIT 1 OFFSET 1. Handle NULL if table has less than 2 distinct salaries.

SQL — Cleanest Approach

                -- Using MAX with exclusion
                SELECT MAX(salary)
                AS SecondHighest
                FROM Employee
                WHERE salary < (SELECT MAX(salary) FROM
                Employee);
              

SQL — Handle NULL Case

                -- Returns NULL if no 2nd highest exists
                SELECT (
                SELECT DISTINCT salary
                FROM Employee
                ORDER BY salary
                DESC LIMIT
                1 OFFSET
                1 )
                AS SecondHighestSalary;
              

Key Insight

The subquery wrapper SELECT (SELECT ...) AS alias ensures NULL is returned if no result exists. Without it, you'd get an empty result set — which might not be what the interviewer wants. LeetCode specifically tests this edge case!

Find Duplicate Records

Query 2 min

GROUP BY the columns that define duplicates, use HAVING COUNT(*) > 1 to filter groups with more than one occurrence.

SQL — Find Duplicate Emails

                -- Find all duplicate emails
                SELECT email,
                COUNT(*)
                AS count FROM
                Users
                GROUP BY email
                HAVING COUNT(*) >
                1;
              

SQL — Find All Duplicate Rows

                -- Find duplicates based on multiple columns
                SELECT first_name, last_name, email,
                COUNT(*)
                AS count FROM
                Employees
                GROUP BY first_name, last_name, email
                HAVING COUNT(*) >
                1;
              

SQL — Show Actual Duplicate Rows

                -- Get all rows that are duplicates (including
                  original)
                SELECT * FROM
                Users
                WHERE email IN (
                SELECT email
                FROM Users
                GROUP BY email
                HAVING COUNT(*) >
                1 );
              

Key Insight

The first query shows WHICH values are duplicated and HOW MANY times. The third query shows ALL the actual rows that are duplicates. Know both — interviewers may ask for either.

Delete Duplicate Rows

Query Tricky 3 min

Keep one copy (usually the one with minimum ID), delete the rest. Use self-join, subquery with ROW_NUMBER, or CTE depending on what's supported.

SQL — Method 1: Self-Join (MySQL)

                -- Keep row with smallest id, delete others
                DELETE e1 FROM
                Employee e1
                INNER JOIN
                Employee e2
                WHERE e1.email = e2.email
                AND e1.id > e2.id;
              

SQL — Method 2: ROW_NUMBER (SQL Server/PostgreSQL)

                -- Using CTE with ROW_NUMBER
                WITH CTE AS (
                SELECT *,
                ROW_NUMBER()
                OVER (
                PARTITION BY email
                ORDER BY id )
                AS rn FROM
                Employee
                )
                DELETE FROM CTE
                WHERE rn > 1;
              

SQL — Method 3: Subquery

                -- Delete duplicates keeping min id
                DELETE FROM
                Employee
                WHERE id
                NOT IN (
                SELECT * FROM (
                SELECT MIN(id)
                FROM Employee
                GROUP BY email )
                AS temp );
              

Common Mistake

In MySQL, you can't directly reference the same table in a subquery of a DELETE. That's why Method 3 wraps the subquery in another SELECT — it's a workaround. Also, always test DELETE queries with SELECT first!

Key Insight

ROW_NUMBER approach is most versatile — works for complex duplicates (multiple columns), lets you control which duplicate to keep (first inserted, latest, etc.). Self-join is faster but only works for simple cases.

Highest Salary by Department

Query 2 min

Use GROUP BY with MAX(), or window function with RANK()/DENSE_RANK() to get employees earning max in each department.

SQL — Just the Max Salary per Dept

                SELECT department_id,
                MAX(salary)
                AS max_salary
                FROM Employee
                GROUP BY department_id;
              

SQL — Employee Details with Max Salary

                -- Get employee name, salary, department for top
                  earners
                SELECT e.name, e.salary, e.department_id
                FROM Employee e
                WHERE (e.department_id, e.salary)
                IN (
                SELECT department_id,
                MAX(salary) FROM
                Employee
                GROUP BY department_id );
              

SQL — Using Window Function (Elegant)

                SELECT * FROM (
                SELECT name, salary, department_id,
                RANK() OVER (
                PARTITION BY department_id
                ORDER BY salary
                DESC ) AS rnk
                FROM Employee
                ) ranked
                WHERE rnk = 1;
              

Key Insight

The window function approach handles ties naturally — if two people share the max salary, both are returned with rank 1. The subquery approach with tuple comparison (dept, salary) IN (...) also handles this. Simple MAX() only gives you the value, not who has it.

Employees Above Department Average

Query 2 min

Use correlated subquery or window function to compare each employee's salary against their department's average.

SQL — Correlated Subquery

                SELECT name, salary, department_id
                FROM
                Employee e1
                WHERE salary > (
                SELECT AVG(salary)
                FROM
                Employee e2
                WHERE e2.department_id =
                e1.department_id );
              

SQL — Window Function (Better Performance)

                SELECT * FROM (
                SELECT name, salary, department_id,
                AVG(salary)
                OVER (PARTITION BY
                department_id) AS dept_avg
                FROM Employee )
                AS t
                WHERE salary > dept_avg;
              

Key Insight

Window function is more efficient — it calculates the average once per partition and adds it to every row. Correlated subquery recalculates for each row (O(n²) vs O(n)). Show the window function approach to impress.

Employees with Same Salary

Query 2 min

Self-join employees table on salary, exclude matching the same row (e1.id != e2.id).

SQL — Self Join

                -- Find pairs of employees with same salary
                SELECT DISTINCT e1.name, e1.salary
                FROM
                Employee e1
                INNER JOIN
                Employee e2
                ON e1.salary = e2.salary
                AND e1.id != e2.id;
              

SQL — Using Subquery

                -- Alternative: find salaries that appear more than
                  once
                SELECT * FROM
                Employee
                WHERE salary
                IN (
                SELECT salary
                FROM Employee
                GROUP BY salary
                HAVING COUNT(*) >
                1 );
              

Employee & Manager (Self Join)

Query Classic 3 min

Self-join the Employee table to itself — join employee's manager_id to manager's id. This is the textbook self-join use case.

Sample Employee Table

id	name	manager_id
1	John (CEO)	NULL
2	Jane	1
3	Bob	1
4	Alice	2

SQL — Employee with Manager Name

                SELECT e.name
                AS employee_name, m.name
                AS manager_name
                FROM Employee e
                LEFT JOIN
                Employee m
                ON e.manager_id = m.id;
              

Key Insight

Use LEFT JOIN to include employees without managers (like CEO)
Use INNER JOIN if you only want employees WHO HAVE managers
The same table is used twice with different aliases (e for employee, m for manager)

Follow-up Questions

Find employees who earn more than their manager?

SELECT e.name AS employee, e.salary AS emp_salary,
       m.name AS manager, m.salary AS mgr_salary
FROM Employee e
INNER JOIN Employee m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Departments with 5+ Employees

Query 2 min

GROUP BY department, filter with HAVING COUNT(*) >= 5. Remember: WHERE filters rows BEFORE grouping, HAVING filters groups AFTER.

SQL

                SELECT department_id,
                COUNT(*)
                AS emp_count
                FROM Employee
                GROUP BY department_id
                HAVING COUNT(*) >=
                5;
              

SQL — With Department Name

                SELECT d.name,
                COUNT(e.id)
                AS emp_count
                FROM
                Department d
                INNER JOIN
                Employee e
                ON d.id = e.department_id
                GROUP BY d.id, d.name
                HAVING COUNT(e.id) >=
                5;
              

Customers Who Never Ordered

Query 2 min

LEFT JOIN customers to orders, filter WHERE order_id IS NULL. Or use NOT EXISTS / NOT IN with subquery.

SQL — LEFT JOIN + IS NULL (Best)

                SELECT c.name
                AS Customers
                FROM
                Customers c
                LEFT JOIN
                Orders o
                ON c.id = o.customer_id
                WHERE o.id
                IS NULL;
              

SQL — NOT EXISTS

                SELECT name
                AS Customers
                FROM
                Customers c
                WHERE NOT EXISTS (
                SELECT 1
                FROM Orders o
                WHERE o.customer_id = c.id );
              

SQL — NOT IN

                SELECT name
                AS Customers
                FROM Customers
                WHERE id
                NOT IN (
                SELECT customer_id
                FROM Orders
                WHERE customer_id
                IS NOT NULL );
              

Common Mistake

NOT IN with NULLs: If the subquery returns any NULL values, NOT IN returns no rows! Always add WHERE column IS NOT NULL inside the subquery, or use NOT EXISTS which handles NULLs correctly.

Key Insight

LEFT JOIN + IS NULL is typically fastest and most readable. NOT EXISTS is also efficient and NULL-safe. NOT IN is trickiest due to NULL behavior. Know all three — interviewers may ask why you chose one over another.

DELETE vs DROP vs TRUNCATE

Difference Most Asked 3 min

DELETE removes rows (can use WHERE, logged, slow). TRUNCATE removes all rows (fast, minimal logging). DROP removes the entire table structure.

Aspect	DELETE	TRUNCATE	DROP
What it removes	Specific rows (or all)	All rows only	Entire table + structure
WHERE clause	✓ Yes	✗ No	✗ No
Rollback	✓ Yes (logged)	✗ No (most DBs)	✗ No
Speed	Slow (row by row)	Fast (deallocate pages)	Fast
Identity/Auto-increment	Keeps counter	Resets to seed	N/A
Triggers fire	✓ Yes	✗ No	✗ No
Type	DML	DDL	DDL

SQL — Examples

                -- DELETE: Remove specific rows
                DELETE FROM
                Employee
                WHERE salary <
                30000;

                -- TRUNCATE: Remove all rows, keep structure
                TRUNCATE TABLE
                Employee;

                -- DROP: Remove entire table
                DROP TABLE
                Employee;
              

Key Insight

DELETE is like erasing text — slow but recoverable
TRUNCATE is like getting a new blank sheet — fast, keeps the paper
DROP is like throwing away the entire notebook
TRUNCATE resets auto-increment; DELETE doesn't

Competitive Edge

Mention that TRUNCATE is DDL (Data Definition Language) despite looking like DML. This means it causes an implicit COMMIT in most databases. Also, TRUNCATE requires ALTER permission on the table, not just DELETE permission. These details show deep understanding.

WHERE vs HAVING

Difference 2 min

WHERE filters rows BEFORE grouping. HAVING filters groups AFTER aggregation. You can't use aggregate functions in WHERE.

Aspect	WHERE	HAVING
When applied	Before GROUP BY	After GROUP BY
Aggregate functions	✗ Cannot use	✓ Can use
Works without GROUP BY	✓ Yes	Technically yes, but rarely useful
Purpose	Filter individual rows	Filter grouped results

SQL — Both Used Together

                -- WHERE filters rows first, then GROUP BY, then HAVING
                  filters groups
                SELECT department_id,
                AVG(salary)
                AS avg_sal FROM
                Employee
                WHERE status =
                'active'
                -- Filter rows FIRST
                GROUP BY department_id
                HAVING AVG(salary) >
                50000;
                -- Filter groups AFTER
              

Key Insight

SQL Execution Order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This is why WHERE can't use aliases defined in SELECT, and why HAVING can use aggregate functions (it runs after GROUP BY).

INNER JOIN vs LEFT JOIN

Difference Visual 3 min

INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from left table + matching rows from right (NULLs if no match).

SQL — Examples

                -- INNER JOIN: Only employees with departments
                SELECT e.name, d.name
                AS dept FROM
                Employee e
                INNER JOIN
                Department d
                ON e.dept_id = d.id;

                -- LEFT JOIN: All employees, even without department
                SELECT e.name, d.name
                AS dept FROM
                Employee e
                LEFT JOIN
                Department d
                ON e.dept_id = d.id;
              

Join Type	Returns	NULLs
`INNER JOIN`	Only matching rows from both	No NULLs from join
`LEFT JOIN`	All from left + matching from right	NULLs for non-matching right
`RIGHT JOIN`	All from right + matching from left	NULLs for non-matching left
`FULL OUTER JOIN`	All from both tables	NULLs on both sides where no match
`CROSS JOIN`	Cartesian product (m × n rows)	No condition, every combination

Key Insight

LEFT JOIN is used to find "missing" records (customers with no orders)
INNER JOIN is default when you just write JOIN
You can convert any RIGHT JOIN to LEFT JOIN by swapping table order
FULL OUTER JOIN is not supported in MySQL — use UNION of LEFT and RIGHT

Normalization & Normal Forms

Concept Theory 4 min

Normalization is organizing data to reduce redundancy and improve integrity. Main forms: 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies), BCNF (stricter 3NF).

Why normalize? Without normalization, you get: (1) Update anomalies — change one place, forget another. (2) Insert anomalies — can't add data without unrelated data. (3) Delete anomalies — deleting one thing loses other info.

Normal Form	Rule	Simple Explanation
1NF	Atomic values, no repeating groups	Each cell has ONE value. No arrays/lists in a column.
2NF	1NF + No partial dependencies	Every non-key column depends on the WHOLE primary key (matters for composite keys).
3NF	2NF + No transitive dependencies	Non-key columns don't depend on other non-key columns. A→B→C is bad.
BCNF	For every X→Y, X is a superkey	Stricter 3NF. Every determinant must be a candidate key.

Quick Memory Trick

"The key, the whole key, and nothing but the key, so help me Codd."

1NF: "The key" — must have a key, atomic values
2NF: "The whole key" — depend on ALL of composite key
3NF: "Nothing but the key" — no transitive deps through non-keys

Competitive Edge

Know when to denormalize: in read-heavy systems (analytics, reporting), joins are expensive. Duplicating data trades storage for speed. Data warehouses often use denormalized star schemas. Real-world systems balance normalization (integrity) with denormalization (performance).

PRIMARY KEY vs FOREIGN KEY

Difference 2 min

PRIMARY KEY uniquely identifies each row in its table (unique + not null). FOREIGN KEY references a primary key in another table to establish relationships.

Aspect	PRIMARY KEY	FOREIGN KEY
Purpose	Uniquely identify rows	Create relationship between tables
Uniqueness	Must be unique	Can have duplicates
NULL values	Not allowed	Allowed (optional relationship)
Count per table	Only 1	Multiple allowed
Auto-creates index	Yes (clustered in most DBs)	No (but recommended)

SQL — Example

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

                CREATE TABLE
                Employee ( id
                INT PRIMARY KEY, name
                VARCHAR(100),
                dept_id INT,
                FOREIGN KEY (dept_id)
                REFERENCES
                Department(id) );
              

Key Insight

Foreign keys enforce referential integrity: you can't insert an employee with a dept_id that doesn't exist in Department. You can't delete a department that has employees (or use ON DELETE CASCADE to auto-delete them).

Indexing & Why It's Used

Concept Performance 3 min

Index is a data structure (usually B-tree) that speeds up data retrieval. Like a book's index — instead of reading every page, jump directly to the right one.

Without index: Database does full table scan — reads EVERY row to find matches. O(n).
With index: Database uses the index structure to jump directly to relevant rows. O(log n).

SQL — Creating Indexes

                -- Single column index
                CREATE INDEX idx_salary
                ON
                Employee(salary);

                -- Composite index (multiple columns)
                CREATE INDEX idx_dept_sal
                ON
                Employee(department_id, salary);

                -- Unique index
                CREATE UNIQUE INDEX idx_email
                ON
                Employee(email);
              

Key Insight

Index speeds up: SELECT, WHERE, JOIN, ORDER BY on indexed columns
Index slows down: INSERT, UPDATE, DELETE (must update index too)
When to index: Frequently queried columns, JOIN columns, WHERE conditions
When NOT to: Small tables, frequently updated columns, low-cardinality columns

Common Mistake

"More indexes = better" is wrong. Each index consumes storage and slows down writes. Also, composite index order matters: INDEX(A, B) helps queries on A or (A and B), but NOT queries on just B. Leftmost prefix rule!

UNION vs UNION ALL

Difference 2 min

UNION combines results and removes duplicates (slower). UNION ALL keeps all rows including duplicates (faster). Use UNION ALL unless you specifically need deduplication.

Aspect	UNION	UNION ALL
Duplicates	Removes duplicates	Keeps all rows
Performance	Slower (must sort/compare)	Faster (just appends)
Use when	You need distinct results	Duplicates are OK or impossible

SQL — Example

                -- UNION: Removes duplicate names
                SELECT name FROM
                Employees
                UNION
                SELECT name FROM
                Customers;

                -- UNION ALL: Keeps all rows (faster)
                SELECT name FROM
                Employees
                UNION ALL
                SELECT name FROM
                Customers;
              

Key Insight

Both UNION types require: same number of columns, compatible data types in corresponding columns. Column names come from the FIRST query. ORDER BY applies to the final combined result (put at the end).

ACID Properties

Concept Core Theory 3 min

ACID guarantees reliable database transactions: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent transactions don't interfere), Durability (committed = permanent).

Property	Meaning	Example
Atomicity	Transaction is all-or-nothing	Bank transfer: debit AND credit both happen, or neither does
Consistency	DB moves from one valid state to another	Account balance can't go negative if rules forbid it
Isolation	Concurrent transactions don't affect each other	Two transfers happening simultaneously get correct results
Durability	Committed data survives crashes	After "transfer successful," data persists even if power fails

SQL — Transaction Example

                BEGIN TRANSACTION;
                UPDATE Account
                SET balance = balance -
                100 WHERE id =
                1; UPDATE
                Account
                SET balance = balance +
                100 WHERE id =
                2; COMMIT;
                -- Both happen, or if error, both rollback
              

Key Insight

Atomicity: Implemented via transaction logs (undo logs)
Consistency: Enforced by constraints, triggers, application logic
Isolation: Implemented via locks or MVCC (multi-version concurrency control)
Durability: Implemented via write-ahead logging (WAL)

Competitive Edge

Know isolation levels: Read Uncommitted (dirty reads possible) → Read Committed → Repeatable Read → Serializable (strongest). Higher isolation = fewer anomalies but worse performance. MySQL InnoDB default is Repeatable Read. PostgreSQL default is Read Committed.

Clustered vs Non-Clustered Index

Difference 3 min

Clustered index determines physical row order (table IS the index) — only one per table. Non-clustered index is a separate structure with pointers to rows — multiple allowed.

Aspect	Clustered Index	Non-Clustered Index
Physical order	Data rows sorted by index	Separate structure, data unsorted
Count per table	Only 1	Multiple (up to 999 in SQL Server)
Speed for range queries	Faster (data is contiguous)	Slower (must follow pointers)
Storage	No extra storage	Extra storage for index structure
Default on	PRIMARY KEY	Other indexes

Key Insight

Think of clustered index like a phone book sorted by last name — the data IS sorted. Non-clustered is like a book's index at the back — it tells you page numbers, but you still need to flip to those pages. In MySQL InnoDB, the primary key is always the clustered index. In PostgreSQL, there's no automatic clustered index.

Competitive Edge

Non-clustered indexes in InnoDB store the primary key value (not row pointer). So if your primary key is large (like UUID), ALL your indexes become larger. This is why auto-increment INT is often preferred for primary keys — smaller indexes, better performance.

Subquery vs Correlated Subquery

Difference Performance 3 min

Regular subquery runs once, independently. Correlated subquery references outer query, runs once PER ROW of outer query — potentially much slower.

SQL — Regular Subquery

                -- Subquery runs ONCE, returns a value
                SELECT * FROM
                Employee
                WHERE salary > (
                SELECT AVG(salary)
                FROM Employee
                -- Independent, runs once );
              

SQL — Correlated Subquery

                -- Subquery references outer query, runs for EACH row
                SELECT * FROM
                Employee e1
                WHERE salary > (
                SELECT AVG(salary)
                FROM
                Employee e2
                WHERE e2.department_id =
                e1.department_id -- References e1! );
              

Aspect	Regular Subquery	Correlated Subquery
References outer query	✗ No	✓ Yes
Execution	Once	Once per outer row
Performance	Usually better	Can be slow (O(n²))
Rewrite as	JOIN (sometimes)	Window function (often better)

Key Insight

Correlated subquery can often be rewritten as JOIN or window function — usually faster
EXISTS with correlated subquery is an exception — can be very efficient (stops at first match)
If you see table alias from outer query used in subquery, it's correlated

SQL — Better: Window Function

                -- Same result as correlated subquery, but faster
                SELECT * FROM (
                SELECT *,
                AVG(salary)
                OVER (PARTITION BY
                department_id) AS dept_avg
                FROM Employee
                ) t
                WHERE salary > dept_avg;
              

SQL Interview Mastery

Find Nth Highest Salary

Key Insight

Common Mistake

Find 2nd Highest Salary

Key Insight

Find Duplicate Records

Key Insight

Delete Duplicate Rows

Common Mistake

Key Insight

Highest Salary by Department

Key Insight

Employees Above Department Average

Key Insight

Employees with Same Salary

Employee & Manager (Self Join)

Sample Employee Table

Key Insight

Follow-up Questions

Departments with 5+ Employees

Customers Who Never Ordered

Common Mistake

Key Insight

DELETE vs DROP vs TRUNCATE

Key Insight

Competitive Edge

WHERE vs HAVING

Key Insight

INNER JOIN vs LEFT JOIN

Key Insight

Normalization & Normal Forms

Quick Memory Trick

Competitive Edge

PRIMARY KEY vs FOREIGN KEY

Key Insight

Indexing & Why It's Used

Key Insight

Common Mistake

UNION vs UNION ALL

Key Insight

ACID Properties

Key Insight

Competitive Edge

Clustered vs Non-Clustered Index

Key Insight

Competitive Edge

Subquery vs Correlated Subquery

Key Insight

Ready to test SQL?