Your one-stop platform to prepare, practice and ace your interviews.
Loading your questions…
46 hand-picked SQL interview questions with detailed answers. Open the interactive version above to search, filter by difficulty, run code, bookmark questions and track your progress.
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;WHERE filters rows before grouping/aggregation. HAVING filters after GROUP BY, so it can use aggregate functions like COUNT().
SELECT dept, COUNT(*)
FROM emp GROUP BY dept HAVING COUNT(*) > 5;An index (usually a B-tree) speeds up reads by letting the DB find rows without a full scan. Tradeoff: it consumes storage and slows down writes (every insert/update maintains the index). Index columns used in WHERE/JOIN/ORDER BY, not everything.
CREATE INDEX idx_user_email ON users(email);Organising tables to reduce redundancy and anomalies. 1NF — atomic columns; 2NF — no partial dependency on part of a composite key; 3NF — no transitive dependencies. Denormalise selectively for read performance.
COUNT, SUM, AVG, MIN, MAX collapse rows into a summary. GROUP BY defines the buckets they summarise.
SELECT status, COUNT(*) FROM orders GROUP BY status;Both nest a query. A CTE (WITH name AS (...)) is named, more readable, can be referenced multiple times, and supports recursion — generally preferred for complex logic over deeply nested subqueries.
WITH active AS (SELECT * FROM users WHERE active = true)
SELECT * FROM active WHERE country = 'IN';BEGIN; UPDATE acct SET bal = bal - 100 WHERE id = 1; UPDATE acct SET bal = bal + 100 WHERE id = 2; COMMIT;Primary key — uniquely identifies a row, not null, one per table. Foreign key — references a primary key in another table, enforcing referential integrity. Unique — no duplicates, but allows a null.
They compute across a set of rows related to the current row without collapsing them (unlike GROUP BY). Examples: ROW_NUMBER(), RANK(), running totals with SUM() OVER (...).
SELECT name, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn
FROM emp;NULL means 'unknown', so NULL = NULL is not true — use IS NULL / IS NOT NULL. Aggregates like COUNT(col) skip NULLs, and COALESCE substitutes a default.
SELECT COALESCE(nickname, name) FROM users;An attack where user input is concatenated into a query, letting an attacker alter it. Prevent it with parameterized queries / prepared statements (never string concatenation), least-privilege DB accounts, and input validation.
-- safe (bound parameter):
SELECT * FROM users WHERE email = ?;IN — checks if a value matches any value in a list or subquery result. Simple and readable for small sets.
EXISTS — returns true if the subquery returns at least one row. Short-circuits on first match — often faster for correlated subqueries and large datasets.
For NULL safety, EXISTS handles NULLs more predictably than NOT IN.
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);Conditional logic in SQL — like if/else in programming:
Simple CASE — compare one expression to multiple values.
Searched CASE — evaluate multiple conditions independently.
Use in SELECT, WHERE, ORDER BY, and aggregations.
SELECT name,
CASE status
WHEN 'active' THEN 'Active'
WHEN 'inactive' THEN 'Inactive'
ELSE 'Unknown'
END AS status_label
FROM users;UNION — combines result sets and removes duplicates (requires sort/distinct — slower).
UNION ALL — combines without deduplication — faster when you know sets are disjoint or duplicates are acceptable.
Both require matching column count and compatible types.
SELECT email FROM users
UNION ALL
SELECT email FROM archived_users;A table joined to itself — typically with table aliases. Used for hierarchical data (employee → manager), comparing rows within the same table, or finding duplicates.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;A saved query that acts like a virtual table. Simplifies complex joins for consumers, enforces access control, and provides a stable interface even if underlying schema changes.
Materialized views store the result physically — faster reads but need refresh. Standard views compute on every query.
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE active = true;age >= 18).CREATE TABLE products (
price NUMERIC CHECK (price > 0),
status TEXT DEFAULT 'draft'
);Logical order (not written order):
That's why you can't use a SELECT alias in WHERE — it hasn't been computed yet. Use a CTE or subquery instead.
OFFSET/LIMIT — LIMIT 20 OFFSET 100. Simple but slow on large offsets (DB still scans skipped rows).
Cursor (keyset) — WHERE id > last_seen_id ORDER BY id LIMIT 20. Consistent performance regardless of page depth; no skipped/duplicate rows when data changes during paging.
-- cursor pagination
SELECT * FROM orders
WHERE id > 1024
ORDER BY id
LIMIT 20;The single most-asked SQL coding question. Know three approaches and their trade-offs:
PARTITION BY.-- 1) DENSE_RANK: any N, tie-safe
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rnk
FROM employees
) t WHERE rnk = 2;
-- 2) Subquery MAX: 2nd only
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- 3) LIMIT/OFFSET
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET 1;
-- Follow-up: highest paid PER department
SELECT * FROM (
SELECT e.*, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) rnk
FROM employees e
) t WHERE rnk = 1;All three number rows within an OVER (ORDER BY ...) window — they differ only on ties:
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, -- 1,2,3,4
RANK() OVER (ORDER BY salary DESC) AS rnk, -- 1,2,2,4
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk -- 1,2,2,3
FROM employees;Find: group by the columns defining "duplicate" and keep groups with COUNT(*) > 1.
Delete keeping one, two standard patterns:
rn > 1; lets you choose which to keep via ORDER BY (e.g. latest updated).-- find
SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1;
-- delete, keep lowest id (MySQL-style self join)
DELETE u FROM users u
JOIN users keep ON keep.email = u.email AND keep.id < u.id;
-- delete via ROW_NUMBER (PostgreSQL / SQL Server), keep newest
DELETE FROM users WHERE id IN (
SELECT id FROM (
SELECT id, ROW_NUMBER() OVER (
PARTITION BY email ORDER BY updated_at DESC) rn
FROM users
) t WHERE rn > 1
);Clustered — the index is the table: rows are physically stored in index order. One per table (usually the primary key — MySQL/InnoDB always does this; SQL Server by default). Range scans on it are fast because neighbors are adjacent on disk.
Non-clustered (secondary) — a separate structure of key → row pointer; lookups find the key, then hop to the row (in InnoDB, via the clustered key — why fat primary keys hurt every index).
When indexes hurt: every INSERT/UPDATE/DELETE must maintain all of them (write amplification); low-selectivity columns (status with 3 values) rarely help; unused indexes cost storage and slow writes for nothing.
-- covering index: query answered entirely from the index, no row hop
CREATE INDEX idx_orders_cust ON orders (customer_id) INCLUDE (status, total);
SELECT status, total FROM orders WHERE customer_id = 42; -- index-only scanCHAR(n) — fixed length: always stores n characters, padding with spaces. VARCHAR(n) — variable length: stores actual content + a small length prefix, up to n.
Use CHAR only for genuinely fixed-width codes (country 'IN', currency 'INR'); VARCHAR for everything else. Gotcha: CHAR's trailing-space padding can make comparisons and app-level equality behave surprisingly across databases.
country CHAR(2) -- fixed codes: fine
email VARCHAR(255) -- variable data: always varchar
-- 'abc' stored in CHAR(10) is 'abc ' — padding is realSELECT fn(col) FROM ...), intended to be side-effect-light; can't manage transactions.CALL), may return nothing/result sets/out params, can perform DML batches and (in most engines) control transactions (COMMIT/ROLLBACK inside).Modern practice keeps business logic in the application (testability, versioning, portability) and reserves procs for data-local batch work where round-trips dominate — worth saying out loud in an interview.
-- function: usable in a query
CREATE FUNCTION full_name(f TEXT, l TEXT) RETURNS TEXT
LANGUAGE sql AS $ SELECT f || ' ' || l $;
SELECT full_name(first_name, last_name) FROM employees;
-- procedure: standalone action, owns its transaction
CREATE PROCEDURE archive_old_orders() LANGUAGE plpgsql AS $
BEGIN
INSERT INTO orders_archive SELECT * FROM orders WHERE created < now() - interval '2 years';
DELETE FROM orders WHERE created < now() - interval '2 years';
COMMIT;
END $;
CALL archive_old_orders();COALESCE(a, b, c) — returns the first non-NULL argument; the standard way to supply a default for a possibly-NULL column.
NULLIF(a, b) — returns NULL when a = b, otherwise a. Its killer use is guarding division: NULLIF(denominator, 0) turns a divide-by-zero error into a NULL result.
SELECT COALESCE(nickname, name, 'Anonymous') AS display,
revenue / NULLIF(orders, 0) AS avg_order_value
FROM customers;% matches any sequence of characters (including none); _ matches exactly one. ILIKE (PostgreSQL) is the case-insensitive variant. Escape a literal % with ESCAPE.
Performance gotcha: a leading wildcard (LIKE '%term') can't use a normal B-tree index, forcing a full scan — reach for full-text search or a trigram index instead.
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE code LIKE 'A_9%';BETWEEN a AND b — a range test, inclusive of both bounds. IN (...) — membership in an explicit list or subquery result.
Date gotcha: BETWEEN '2024-01-01' AND '2024-01-31' excludes almost all of Jan 31 if the column is a timestamp (the bound is midnight). Prefer >= start AND < next_day for timestamp ranges.
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
SELECT * FROM orders WHERE status IN ('paid', 'shipped');CREATE, ALTER, DROP, TRUNCATE. Often auto-commits.SELECT, INSERT, UPDATE, DELETE.GRANT, REVOKE.COMMIT, ROLLBACK, SAVEPOINT.Common tools: CURRENT_DATE / NOW() for the clock, interval arithmetic for offsets, EXTRACT(part FROM ts) to pull a year/month, and DATE_TRUNC('month', ts) to bucket rows for reporting.
Best practice: store timestamps in UTC (timestamptz) and convert to the user's zone at display time — never store local time.
SELECT DATE_TRUNC('month', created_at) AS mth, COUNT(*)
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1 ORDER BY 1;WITH RECURSIVE has two parts joined by UNION ALL: an anchor (the starting rows) and a recursive member that references the CTE itself. The engine repeats the recursive part, feeding each pass's output back in, until it produces no new rows.
Use it for tree/graph traversal (org charts, categories, bill-of-materials) and for generating sequences of numbers or dates.
WITH RECURSIVE chain AS (
SELECT id, name, manager_id, 1 AS lvl
FROM employees WHERE id = 1 -- anchor: the CEO
UNION ALL
SELECT e.id, e.name, e.manager_id, c.lvl + 1
FROM employees e
JOIN chain c ON e.manager_id = c.id -- recurse down the tree
)
SELECT * FROM chain ORDER BY lvl;An aggregate that collapses many row values into a single delimited string per group — the inverse of splitting.
STRING_AGG(expr, ',')GROUP_CONCAT(expr SEPARATOR ',')LISTAGG(expr, ',')You can order the elements with ... ORDER BY ... inside the call.
SELECT customer_id,
STRING_AGG(product, ', ' ORDER BY product) AS items
FROM order_lines
GROUP BY customer_id;Use an aggregate as a window function with an ORDER BY in the OVER clause — that turns SUM() into a cumulative sum up to the current row. The frame clause (ROWS BETWEEN ...) sets exactly which rows are included, letting you also build moving averages.
SELECT day, amount,
SUM(amount) OVER (ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
AVG(amount) OVER (ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;Window functions that read a value from a row offset from the current one — LAG looks backward, LEAD forward — without a self-join. Perfect for period-over-period deltas, detecting gaps, or comparing each row to its neighbour.
Signature: LAG(col, offset, default) OVER (PARTITION BY ... ORDER BY ...). The default fills the first/last row where no neighbour exists.
SELECT day, revenue,
revenue - LAG(revenue, 1, 0) OVER (ORDER BY day) AS day_over_day
FROM daily_revenue;The portable technique is conditional aggregation: wrap a CASE inside an aggregate, one per target column, so each category becomes its own column.
Some engines add a dedicated operator — PIVOT (SQL Server, Oracle) or the crosstab function (PostgreSQL's tablefunc) — but they need the column list known up front, so the CASE form is the interview-safe answer.
SELECT product,
SUM(CASE WHEN region = 'US' THEN qty END) AS us,
SUM(CASE WHEN region = 'EU' THEN qty END) AS eu,
SUM(CASE WHEN region = 'IN' THEN qty END) AS in_
FROM sales
GROUP BY product;A non-correlated subquery is self-contained — it runs once and its result is reused (e.g. WHERE id IN (SELECT ...)).
A correlated subquery references a column from the outer query, so conceptually it re-evaluates for each outer row (e.g. EXISTS (SELECT 1 ... WHERE o.user_id = u.id)). That can be slow, though modern optimizers often rewrite them into joins.
-- correlated: 'users who have at least one order'
SELECT u.name FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);Insert a row, but if it would violate a unique/primary key, update the existing row instead — atomic, no read-then-write race.
INSERT ... ON CONFLICT (key) DO UPDATEINSERT ... ON DUPLICATE KEY UPDATEMERGEIt needs a unique constraint on the conflict target to know what 'already exists' means.
INSERT INTO inventory (sku, qty) VALUES ('A1', 5)
ON CONFLICT (sku)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;A self-join classic: join the employee row to its manager row via manager_id = manager.id, then keep pairs where the employee's salary exceeds the manager's. An INNER JOIN naturally drops employees with no manager (the CEO), which is usually what's wanted.
SELECT e.name AS employee, e.salary, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;The gaps-and-islands trick: for each user's ordered dates, subtract a ROW_NUMBER() (as days) from the date. Consecutive days advance in lockstep with the row number, so the difference is constant within a streak — group by that constant to collapse each run, then filter runs of the required length.
WITH marked AS (
SELECT user_id, day,
day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
FROM active_days
)
SELECT user_id, MIN(day) AS streak_start, COUNT(*) AS len
FROM marked
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;Procedural code that fires automatically on INSERT/UPDATE/DELETE, timed BEFORE or AFTER the event, at row or statement level. Common uses: audit logging, maintaining derived/denormalized columns, and enforcing rules a CHECK can't express.
Downside: logic is hidden from anyone reading the app code, they run inside the writing transaction (adding latency), and they make bugs hard to trace — many teams prefer explicit application logic.
CREATE TRIGGER set_updated
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION touch_updated_at(); -- sets NEW.updated_at = now()Four SQL levels, each ruling out one more anomaly at the cost of concurrency:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... reads/writes see a consistent snapshot ...
COMMIT;A deadlock is a circular wait: txn A holds a lock B wants while B holds a lock A wants, so neither can proceed. The database's deadlock detector spots the cycle and kills one transaction (the victim), which must retry.
Avoid them by: acquiring locks in a consistent order everywhere, keeping transactions short, touching fewer rows (good indexes narrow lock scope), and using a lower isolation level or optimistic concurrency where safe.
-- both sessions lock ids in the SAME order → no cycle
BEGIN;
UPDATE accounts SET bal = bal - 100 WHERE id = 1;
UPDATE accounts SET bal = bal + 100 WHERE id = 2;
COMMIT;EXPLAIN shows the planner's chosen strategy; EXPLAIN ANALYZE actually runs it and reports real times and row counts. Key things to read:
ANALYZE.EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'paid';Partitioning splits one logical table into physical pieces within a single database — by range (dates), list, or hash. The planner prunes irrelevant partitions, and you can drop old ones instantly. It's a maintenance and query-pruning win, not horizontal scale.
Sharding spreads data across multiple database servers keyed by a shard key — true horizontal scale for writes/storage. The cost: cross-shard joins and transactions become hard, and a bad shard key creates hotspots.
-- range partitioning (PostgreSQL declarative)
CREATE TABLE events (id bigint, ts timestamptz) PARTITION BY RANGE (ts);
CREATE TABLE events_2024 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');Unlike a regular view (a saved query recomputed on every access), a materialized view stores the result physically, so expensive aggregations/joins read instantly. The trade-off is staleness — the data is a snapshot until you REFRESH it.
Use them for dashboards and reports where slightly-old data is acceptable and the underlying query is costly. In PostgreSQL, REFRESH ... CONCURRENTLY avoids locking readers (but needs a unique index).
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT DATE_TRUNC('day', created_at) AS day, SUM(total) AS revenue
FROM orders GROUP BY 1;
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;