SQL Interview Preparation

SQL interviews test three things: conceptual knowledge (can you explain it?), query writing (can you solve it correctly, including edge cases?), and engineering judgment (performance, correctness under concurrency, design trade-offs).

Concept

Why It Matters

SQL appears in interviews for software engineers, backend developers, data analysts, data engineers, QA/test engineers, BI developers, and DBAs. Many candidates know syntax but fail on NULLs, duplicates from joins, ties in rankings, and explaining their reasoning.

Syntax

How to answer a SQL coding question:

1. Clarify: expected output columns, grain (one row per what?), ties, NULLs, duplicates, date boundaries.
2. Identify tables and join keys; state the relationship cardinalities.
3. Write the query incrementally (FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY).
4. Walk through a tiny example by hand.
5. Discuss edge cases: NULLs, ties, empty groups, cancelled records.
6. Discuss performance: indexes, sargability, alternatives (window vs subquery).

Example

42.1 Beginner Questions Q: What is SQL? SQL is a declarative, standardized language for defining, manipulating, querying, and controlling access to data in relational databases. You specify *what* result you want; the database optimizer decides *how* to compute it.

Q: What is the difference between SQL and a DBMS (or MySQL)? SQL is a language. A DBMS (MySQL, PostgreSQL, SQL Server, Oracle) is software that stores data and executes SQL. Each DBMS implements its own dialect of SQL.

Q: Primary key vs foreign key? Uniquely identifies rows in its own table

References a key in another (or the same) table Unique and NOT NULL

Can repeat; can be NULL unless declared NOT NULL Many per table Enforces entity integrity Enforces referential integrity

Q: Primary key vs unique key? Both enforce uniqueness. A table has one primary key (never NULL); it can have many unique keys, which usually allow NULLs (SQL Server allows only one NULL per unique constraint).

Q: DELETE vs TRUNCATE vs DROP? DELETE (DML) removes selected rows, logs each row, fires triggers, and can be rolled back. TRUNCATE (DDL) removes all rows quickly by deallocating pages, usually resets identity, does not fire row triggers, and is non-rollbackable in MySQL/Oracle. DROP (DDL) removes the table structure and data entirely. (Full table in Module 2.)

Q: WHERE vs HAVING? WHERE filters rows before grouping and cannot use aggregates. HAVING filters groups after aggregation and typically uses aggregates.

Q: UNION vs UNION ALL? Both stack results of compatible queries. UNION removes duplicate rows (requires sort/hash, slower); UNION ALL keeps all rows (faster). Use UNION ALL unless you need de-duplication.

Q: What is NULL? A marker for missing/unknown data. Comparisons with NULL yield UNKNOWN, so use IS NULL / IS NOT NULL; aggregates (except COUNT(*)) ignore NULLs.

Q: CHAR vs VARCHAR? CHAR(n) is fixed length and space-padded; VARCHAR(n) stores only the actual characters (up to n). Use CHAR for fixed-width codes, VARCHAR for variable text.

Q: What are DDL, DML, DQL, DCL, TCL? Definition (CREATE/ALTER/DROP/TRUNCATE), manipulation (INSERT/UPDATE/DELETE/MERGE), query (SELECT), control (GRANT/REVOKE), transaction control (COMMIT/ROLLBACK/SAVEPOINT).

Q: What is the logical order of execution of a SELECT? FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET.

42.2 Intermediate Questions Q: Explain the JOIN types. INNER (matches only), LEFT (all left rows + matches), RIGHT (all right rows + matches), FULL OUTER (all rows from both), CROSS (Cartesian product), SELF (a table joined to itself). Mention NULL-padding, filter placement in ON vs WHERE for outer joins, and fan-out duplicates.

Q: What is a subquery? Correlated vs non-correlated? A query nested inside another. A non-correlated subquery runs independently (conceptually once); a correlated subquery references the outer query's columns and is logically evaluated per outer row.

Q: EXISTS vs IN? IN compares a value to a list/set; EXISTS checks whether a subquery returns any row. Performance is often the same in modern optimizers; the key difference is NULL handling: NOT IN returns no rows if the subquery yields a NULL, whereas NOT EXISTS behaves correctly.

Q: What is an index? Clustered vs non-clustered? A data structure (usually a B-tree) that speeds up lookups at the cost of storage and write overhead. A clustered index defines the physical order of table rows (one per table); non-clustered indexes are separate structures with pointers to rows (many per table).

Q: What is normalization? Explain 1NF, 2NF, 3NF. Organizing data to reduce redundancy and anomalies. 1NF: atomic values, no repeating groups. 2NF: no partial dependency on part of a composite key. 3NF: no transitive dependency between non-key attributes.

Q: What is a transaction? Explain ACID. A unit of work that succeeds or fails as a whole. Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't interfere beyond the isolation level), Durability (committed data survives crashes via the write-ahead log).

Q: What is a view? Can it be updated? A stored query presented as a virtual table. Simple single-table views without aggregation/DISTINCT/GROUP BY are usually updatable; complex views need INSTEAD OF triggers.

Q: Stored procedure vs function? Procedures are called with CALL/EXEC, can have OUT parameters, return result sets, and control transactions. Functions return a value/table and can be used inside SQL expressions; they typically cannot modify data (SQL Server) or commit.

Q: What are constraints? Name them. Rules enforced by the database: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.

Q: RANK vs DENSE_RANK vs ROW_NUMBER? For values 100, 90, 90, 80: ROW_NUMBER = 1, 2, 3, 4; RANK = 1, 2, 2, 4; DENSE_RANK = 1, 2, 2, 3.

42.3 Advanced Questions Q: What are window functions and how do they differ from GROUP BY? Window functions compute values across a set of related rows (defined by OVER with PARTITION BY, ORDER BY, and a frame) while keeping every row. GROUP BY collapses rows into one per group.

Q: What is a CTE? When would you use a recursive CTE? A named temporary result set defined with WITH, scoped to one statement, improving readability and enabling recursion. Recursive CTEs traverse hierarchies (org charts, BOMs, category trees) and generate series.

Q: How do you optimize a slow query? Measure (find the query and its frequency), get the actual execution plan, find the expensive operator, compare estimated vs actual rows, check for full scans on large tables, non-sargable predicates, missing/unused indexes, bad join orders, sorts spilling to disk, and fan-out; fix one thing at a time (index, rewrite, statistics); re-measure on realistic data.

Q: Explain isolation levels and anomalies. READ UNCOMMITTED allows dirty reads; READ COMMITTED prevents them but allows non-repeatable reads and phantoms; REPEATABLE READ prevents non-repeatable reads (phantoms possible per standard); SERIALIZABLE prevents all. Mention MVCC, snapshot isolation, write skew, and DBMS defaults (PostgreSQL/SQL Server/Oracle: READ COMMITTED; MySQL InnoDB: REPEATABLE READ).

Q: What is a deadlock? How do you prevent and handle it? Two or more transactions each hold locks the other needs, forming a cycle. The DBMS detects it and aborts a victim. Prevent by accessing objects in a consistent order, keeping transactions short, indexing to reduce locked rows, and using appropriate isolation; handle by retrying the aborted transaction.

Q: How do you choose and order columns in a composite index? Base it on real queries: equality predicates first, then range predicates or ORDER BY columns; consider selectivity and which query shapes must be supported (leftmost-prefix rule); consider INCLUDE columns for covering.

Q: How do you read an execution plan? Read inner-to-outer; identify access methods (seq/table scan, index seek/scan, index-only), join algorithms (nested loop, hash, merge), sorts and aggregates; compare estimated vs actual rows; find the most expensive nodes; look for warnings (spills, implicit conversions, key lookups).

Q: What is a covering index? An index that contains every column a query needs, so the query is answered from the index alone without table lookups.

Q: What is MVCC? Multi-Version Concurrency Control: writers create new row versions; readers see a consistent snapshot without taking read locks, so readers and writers don't block each other. Old versions are cleaned up later (PostgreSQL VACUUM, Oracle undo, InnoDB purge).

Q: When would you denormalize? When measured read performance or reporting needs justify redundancy — summary tables, materialized views, star schemas — and you have a reliable way to keep copies consistent.

Q: How would you design for idempotent inserts (e.g., payment webhooks)? Use a unique constraint on an idempotency key/event ID and INSERT ... ON CONFLICT DO NOTHING (or equivalent), so retries do not create duplicates.

42.4 SQL Coding Problems (with solutions) All use the course sample database.

Problem 1: Nth highest salary (N = 3), handling ties. Result: 120000.00.

Problem 2: Employees who earn more than their managers. Result: no rows in the sample data (state this explicitly in an interview and explain how you verified it).

Problem 3: Departments with no employees. Result: Finance.

Problem 4: Top earner per department (all ties). Result: (1, Asha, 250000), (2, Rahul, 120000), (3, Arjun, 90000), (NULL, Kiran, 80000).

Problem 5: Customers who placed orders in both January and March 2026.

Result: 2 (Ben Carter: 1002 in January, 1006 in March).

Problem 6: Delete duplicate rows keeping the lowest id — see Module 39.3.

Problem 7: Cumulative revenue by day. Problem 8: Percentage of orders that were cancelled. Result: 16.67.

Problem 9: Swap two values in a column (e.g., statuses 'SHIPPED' ↔ 'PENDING') in one statement.

Problem 10: Find the manager with the most direct reports.

Result: Asha, 4 (Vikram, Rahul, Arjun, Kiran).

SELECT DISTINCT salary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees) t
WHERE dr = 3;

SELECT e.first_name AS employee, e.salary, m.first_name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.salary > m.salary;

SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

SELECT dept_id, first_name, salary
FROM (SELECT e.*, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS r FROM employees e) t
WHERE r = 1
ORDER BY dept_id;

SELECT customer_id FROM orders WHERE order_date >= DATE '2026-01-01' AND order_date < DATE '2026-02-01'
INTERSECT
SELECT customer_id FROM orders WHERE order_date >= DATE '2026-03-01' AND order_date < DATE '2026-04-01';

SELECT order_date, SUM(total_amount) AS day_revenue,
       SUM(SUM(total_amount)) OVER (ORDER BY order_date) AS cumulative
FROM orders
GROUP BY order_date
ORDER BY order_date;

SELECT ROUND(100.0 * SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) / COUNT(*), 2) AS cancel_pct
FROM orders;

UPDATE orders
SET status = CASE status WHEN 'SHIPPED' THEN 'PENDING' WHEN 'PENDING' THEN 'SHIPPED' END
WHERE status IN ('SHIPPED', 'PENDING');

SELECT m.first_name, COUNT(*) AS direct_reports
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
GROUP BY m.emp_id, m.first_name
ORDER BY direct_reports DESC
FETCH FIRST 1 ROWS WITH TIES;           -- SQL Server: SELECT TOP (1) WITH TIES ...

Expected Result

A strong interview answer states the grain, handles ties and NULLs, gives the query, walks through a small example, and mentions indexes or alternatives.

Common Mistakes

Writing the query before clarifying ties/NULLs/duplicates.

Using LIMIT without ORDER BY for "top" questions.

Using MAX tricks for "Nth highest" without handling duplicates.

Forgetting customers/departments with zero rows (LEFT JOIN + COUNT(child.pk)).

Answering "it depends" without explaining on what.

Best Practices

Think aloud; explain the grain.

Prefer window functions for ranking problems.

Mention the DBMS you are assuming.

Test your query mentally against edge cases.

Interview Relevance

This entire module is interview preparation. Practice explaining each answer out loud in under two minutes.

Practice Questions

Explain the difference between RANK and DENSE_RANK to a non-technical manager.

Solve "second highest salary per department" in two different ways.

Explain why COUNT(*) after a LEFT JOIN can give 1 instead of 0.

Describe how you would investigate a deadlock reported in production.

Explain what a covering index is and design one for a query of your choice.