Skip to content

Interview PrepSQL · Core

SQL You Will Be Asked to Write ​

Every query on this page runs against the same two small tables, and every result shown is the real output from PostgreSQL.

sql
CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL, city text);
CREATE TABLE orders (id int PRIMARY KEY, customer_id int REFERENCES customers(id),
                     total numeric(10,2), placed_on date);
CREATE TABLE employees (id int PRIMARY KEY, name text, dept text, salary int, manager_id int);

customers

idnamecity
1AshaLondon
2BenLeeds
3ChenLondon
4DaraNULL

orders

idcustomer_idtotalplaced_on
101140.002026-10-01
102125.502026-10-03
103290.002026-10-03
104315.002026-10-04
105160.002026-10-05

employees

idnamedeptsalarymanager_id
1MayaEng150000NULL
2RaviEng1200001
3LenaEng1200001
4OmarEng950002
5SofiaSales900001
6TomSales700005
7IvySales700005

10. What are the different types of JOIN? ​

The short answer

A join combines rows from two tables where a condition matches. INNER JOIN keeps only rows with a match on both sides. LEFT JOIN keeps every row from the left table, with NULLs where the right has no match. RIGHT JOIN is the mirror image. FULL OUTER JOIN keeps unmatched rows from both sides. CROSS JOIN pairs every row with every row. A self join joins a table to itself, such as employees to their managers. The most asked use of LEFT JOIN is finding rows with no match.

INNER: matches onlyLEFT: all customersRIGHT: all ordersFULL: everythingcustomersorders
Which rows survive. The left circle is customers, the right is orders, and the overlap is customers who have orders. Each join keeps the shaded part.
sql
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;
nameorder_id
Asha101
Asha102
Asha105
Ben103
Chen104
DaraNULL

Reading the result: Asha appears three times, once per order, because a join produces one row per matching pair. Dara has no orders, so LEFT JOIN keeps her with NULL. An INNER JOIN would drop her.

The classic: customers who never ordered.

sql
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;                     -- only the rows where no order matched

Result: Dara. Writing WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) gives the same answer and is often just as fast.

11. What is the difference between WHERE and HAVING, and how does GROUP BY work? ​

The short answer

GROUP BY collapses rows that share a value into one row per group, so you can use aggregate functions such as COUNT, SUM and AVG per group. WHERE filters rows before grouping; it cannot use aggregates. HAVING filters groups after grouping; it can. Every column in the SELECT must either be in the GROUP BY or inside an aggregate.

Customers who spent more than £50 since 2 October:

sql
SELECT c.name, COUNT(*) AS orders, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.placed_on >= '2026-10-02'          -- (1) rows first: drops order 101
GROUP BY c.name                            -- (2) one row per customer
HAVING SUM(o.total) > 50                   -- (3) then groups: drops Chen with 15.00
ORDER BY spent DESC;
nameordersspent
Ben190.00
Asha285.50
  1. WHERE removes Asha's order 101 from 1 October before any totals are computed.
  2. GROUP BY makes one row each for Asha, Ben and Chen.
  3. HAVING removes Chen, whose total is only £15.00. Asha's total is 25.50 plus 60.00, which is 85.50.

12. In what order does SQL actually run a query? ​

The short answer

Not in the order you write it. The logical order is: FROM and JOIN to build the rows, WHERE to filter them, GROUP BY to group them, HAVING to filter groups, SELECT to compute the output columns, DISTINCT, ORDER BY, and finally LIMIT and OFFSET. This explains common errors: you cannot use a SELECT alias in WHERE, because WHERE runs before SELECT, but you can use it in ORDER BY, which runs after.

① FROMJOIN② WHERE③ GROUPBY④ HAVING⑤ SELECT⑥ DISTINCT⑦ ORDERBY⑧ LIMITaliases from ⑤ exist only in ⑥, ⑦ and ⑧
Build, filter, group, filter, compute, sort, cut. Each step works on the output of the one before. A name defined in SELECT does not exist yet in WHERE.
sql
SELECT total * 1.2 AS with_vat FROM orders WHERE with_vat > 50;          -- error: column "with_vat" does not exist
SELECT total * 1.2 AS with_vat FROM orders WHERE total * 1.2 > 50;       -- works
SELECT total * 1.2 AS with_vat FROM orders ORDER BY with_vat DESC;       -- works: ORDER BY runs after SELECT

13. How do you find the second-highest salary, or the Nth highest? ​

The short answer

For the second highest, take the maximum of the salaries below the maximum: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). For the Nth, use DENSE_RANK() ordered by salary descending and keep rank N, which handles ties correctly. ORDER BY salary DESC LIMIT 1 OFFSET 1 looks simpler, but it returns the second row, which may be another person with the same top salary, unless you add DISTINCT.

sql
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Result: 120000. The inner query finds 150000; the outer one finds the largest salary below it.

The Nth highest, ties included:

sql
SELECT name, salary
FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
) ranked
WHERE r = 2;
namesalary
Ravi120000
Lena120000

Both Ravi and Lena earn the second-highest salary, and DENSE_RANK returns both. Change 2 to any N. If the interviewer wants exactly one row, ask how ties should be broken, which is the question they are hoping you will ask.

14. How do you get the top N rows per group? ​

The short answer

Use a window function. ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) numbers the rows within each department from 1, highest salary first. Wrap it in a subquery or CTE and keep rows where the number is at most N. Use ROW_NUMBER for exactly N per group, or DENSE_RANK to include ties. Always add a tie-breaker to the ORDER BY so the result is the same every time.

sql
SELECT dept, name, salary
FROM (
  SELECT dept, name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, name) AS rn   -- (1)
  FROM employees
) t
WHERE rn <= 2                                                                    -- (2)
ORDER BY dept, rn;
deptnamesalary
EngMaya150000
EngLena120000
SalesSofia90000
SalesIvy70000
  1. PARTITION BY dept restarts the numbering for each department. name breaks ties, so Lena, alphabetically before Ravi, is chosen consistently.
  2. Keep the first two per department. A window function cannot appear directly in WHERE, because WHERE runs before it (question 12), hence the subquery.

PostgreSQL shortcut for top 1 per group: SELECT DISTINCT ON (dept) dept, name, salary FROM employees ORDER BY dept, salary DESC, name.

15. How do you find and delete duplicate rows? ​

The short answer

Find them with GROUP BY the columns that should be unique, and HAVING COUNT(*) > 1. Delete the extras by keeping one row per group, usually the one with the lowest ID, and deleting the rest, either with a self-join on the duplicate columns where the ID is larger, or with ROW_NUMBER() and deleting rows numbered above 1. Afterwards, add a unique constraint so duplicates cannot come back.

A signups table has six rows: a@x.example three times, b@x.example twice and c@x.example once, with IDs 1 to 6.

sql
SELECT email, COUNT(*)
FROM signups
GROUP BY email
HAVING COUNT(*) > 1;
emailcount
a@x.example3
b@x.example2

Delete all but the first of each:

sql
DELETE FROM signups s
USING signups k
WHERE s.email = k.email
  AND s.id > k.id;                        -- s has a twin k with a smaller id, so s goes

ALTER TABLE signups ADD CONSTRAINT signups_email_unique UNIQUE (email);

Remaining rows: IDs 1, 2 and 4, one per email. The constraint then makes the database reject any future duplicate.

16. What are window functions, and how do ROW_NUMBER, RANK and DENSE_RANK differ? ​

The short answer

A window function computes a value for each row using a set of related rows, its window, without collapsing them the way GROUP BY does. You write function() OVER (PARTITION BY … ORDER BY …). ROW_NUMBER gives 1, 2, 3, 4 with no ties. RANK gives tied rows the same number and then skips: 1, 1, 3. DENSE_RANK gives ties the same number without skipping: 1, 1, 2. Others you will use are SUM() OVER for running totals, LAG and LEAD for the previous and next row, and AVG() OVER for moving averages.

sql
SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS row_number,
       RANK()       OVER (ORDER BY salary DESC)       AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC)       AS dense_rank
FROM employees
WHERE id IN (2, 3, 5, 6, 7);
namesalaryrow_numberrankdense_rank
Lena120000111
Ravi120000211
Sofia90000332
Ivy70000443
Tom70000543

Reading it: Lena and Ravi tie. ROW_NUMBER still separates them. RANK gives both 1 and jumps Sofia to 3, because two people are ahead of her. DENSE_RANK gives Sofia 2, the next distinct salary.

A running total and the previous value:

sql
SELECT id, placed_on, total,
       SUM(total) OVER (ORDER BY placed_on, id) AS running_total,
       LAG(total) OVER (ORDER BY placed_on, id) AS previous
FROM orders;
idplaced_ontotalrunning_totalprevious
1012026-10-0140.0040.00NULL
1022026-10-0325.5065.5040.00
1032026-10-0390.00155.5025.50
1042026-10-0415.00170.5090.00
1052026-10-0560.00230.5015.00

17. What is a CTE, and how does a recursive query work? ​

The short answer

A CTE, common table expression, is a named subquery written before the main query with WITH name AS (…). It makes long queries readable by building them in steps. A recursive CTE, WITH RECURSIVE, has two parts joined by UNION ALL: an anchor query that gives the starting rows, and a recursive query that refers to the CTE itself to find the next level. It repeats until no new rows appear. It is how you walk trees: an employee's management chain, category hierarchies, or threaded comments.

Omar's chain of managers, up to the top:

sql
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE id = 4                         -- (1) anchor: Omar
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.id = c.manager_id                 -- (2) the manager of the last row found
)
SELECT name, depth FROM chain ORDER BY depth;
namedepth
Omar0
Ravi1
Maya2
  1. The anchor finds Omar.
  2. Each round joins to find the manager of the rows found in the previous round: Ravi, then Maya. Maya has no manager, so the next round finds nothing and the recursion stops.

Guard against cycles. If bad data makes someone their own manager's manager, the query never ends. Add a depth limit, WHERE c.depth < 20, or track visited IDs.

18. What are the common NULL traps in SQL? ​

The short answer

NULL means "unknown", not zero or empty. So NULL = NULL is not true but unknown, and rows only pass a WHERE when the condition is true. Use IS NULL and IS NOT NULL to test for it. COUNT(*) counts rows, but COUNT(column) skips nulls. SUM and AVG ignore nulls. COALESCE(x, 0) replaces a null. The nastiest trap is NOT IN with a list containing a null: it returns no rows at all.

sql
SELECT COUNT(*)                            AS all_rows,
       COUNT(city)                         AS with_city,
       COUNT(*) FILTER (WHERE city IS NULL) AS no_city
FROM customers;
all_rowswith_cityno_city
431

The NOT IN trap:

sql
SELECT COUNT(*) FROM customers WHERE city NOT IN ('Leeds', NULL);   -- returns 0

Asha's city is London. Is London not in the list? It is not Leeds, but compared with NULL the answer is unknown, so the whole test is unknown, and the row is dropped. Every row is dropped. This happens for real when the list comes from a subquery, WHERE id NOT IN (SELECT customer_id FROM orders), and one order has a null customer_id. Use NOT EXISTS instead, which does not have this problem.

19. What is the difference between UNION and UNION ALL, and between DELETE, TRUNCATE and DROP? ​

The short answer

UNION stacks the results of two queries and removes duplicate rows, which needs a sort or hash, so it is slower. UNION ALL keeps every row and is faster; use it unless you need duplicates removed. DELETE removes chosen rows, one by one, fires triggers, and can be filtered with WHERE. TRUNCATE empties a whole table almost instantly and resets storage. DROP removes the table itself, structure included. In PostgreSQL all three can be rolled back inside a transaction.

sql
SELECT city FROM customers WHERE city IS NOT NULL UNION     SELECT 'London';   -- Leeds, London
SELECT city FROM customers WHERE city IS NOT NULL UNION ALL SELECT 'London';   -- Leeds, London, London, London
DELETETRUNCATEDROP
RemovesChosen rowsAll rowsThe table
WHERE allowedYesNoNo
Speed on a big tableSlow, row by rowFastFast
Fires row triggersYesNoNo
Table still existsYesYesNo

20. What is normalization, and when would you denormalize? ​

The short answer

Normalization means organising tables so each fact is stored once, which prevents update mistakes. First normal form: one value per cell, no repeating groups. Second: every non-key column depends on the whole key, not part of it. Third: non-key columns depend only on the key, not on other non-key columns. You denormalize, deliberately copying data, when reads matter more than the cost of keeping copies in step: a reporting table, a cached order total, or a product name copied onto an invoice line because it must not change later.

Breaking normal forms, with one bad table:

order_idcustomer_emailcustomer_cityitems
101asha@shop.exampleLondonshoes, socks
102asha@shop.exampleLondoncap
  1. Not 1NF: items holds a list in one cell. Fix: an order_items table with one row per item.
  2. Repeated facts: Asha's city is stored on every order. If she moves, some rows will say London and some Leeds. Fix: a customers table, and orders store only customer_id. This is what 2NF and 3NF force.

Denormalization done safely:

CopyWhyHow to keep it correct
orders.totalAvoid summing items on every list pageUpdate in the same transaction as the items
invoice_lines.product_nameLegal record must not change if the product is renamedCopy once, never update
A reporting tableHeavy analytics off the main tablesRebuild on a schedule, or a materialized view

21. What are primary keys, foreign keys and other constraints? ​

The short answer

A primary key uniquely identifies each row and cannot be null; each table has one, possibly over several columns, a composite key. A foreign key says a column must match a primary key in another table, so an order cannot point to a customer who does not exist, with rules for what happens on delete. Unique forbids duplicates, not null forbids missing values, and check enforces a condition such as total >= 0. Constraints are the database guaranteeing your rules, even when application code has a bug.

sql
CREATE TABLE order_items (
  order_id   int  NOT NULL REFERENCES orders(id) ON DELETE CASCADE,   -- (1)
  product_id int  NOT NULL REFERENCES products(id),                   -- (2)
  qty        int  NOT NULL CHECK (qty > 0),                           -- (3)
  price      numeric(10,2) NOT NULL CHECK (price >= 0),
  PRIMARY KEY (order_id, product_id)                                  -- (4)
);
  1. Delete an order, and its items go with it.
  2. No ON DELETE rule, so deleting a product that appears on an order is refused.
  3. No zero or negative quantities, whatever the API sends.
  4. A composite primary key: the same product appears at most once per order.
Key termMeaning
Candidate keyAny column set that could be the primary key, such as id or email
Natural keyA real-world value, such as an email or ISBN
Surrogate keyA made-up ID, such as a serial number or UUID
Composite keyA key of two or more columns

Index your foreign keys. PostgreSQL does not index the referencing column automatically. Without an index on order_items.order_id, deleting an order scans the whole items table.

22. What are views and materialized views? ​

The short answer

A view is a saved query that you can select from like a table. It stores no data; each time you query it, the database runs the underlying query, so it is always current. Views simplify complex joins and can hide columns from some users. A materialized view stores the query's result on disk, so reading it is fast, but it is a snapshot: it only changes when you run REFRESH MATERIALIZED VIEW. Use it for expensive reports that may be a few minutes old.

sql
CREATE VIEW customer_totals AS
SELECT c.id, c.name, COALESCE(SUM(o.total), 0) AS spent
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT placed_on, SUM(total) AS revenue FROM orders GROUP BY placed_on;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;   -- needs a unique index; readers are not blocked
ViewMaterialized view
Stores dataNoYes
Always currentYesOnly after a refresh
Read speedAs slow as the queryFast; can be indexed
Use forSimplifying queries, limiting accessDashboards, heavy reports

Combine chapters into one PDF

Pick the chapters you want. They print in the order you pick them.

Interview Prep
Databases & Caching
Foundations
SQL
Performance
Concurrency
Scaling
Caching
Other topics
Nothing picked yet