Appearance
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
| id | name | city |
|---|---|---|
| 1 | Asha | London |
| 2 | Ben | Leeds |
| 3 | Chen | London |
| 4 | Dara | NULL |
orders
| id | customer_id | total | placed_on |
|---|---|---|---|
| 101 | 1 | 40.00 | 2026-10-01 |
| 102 | 1 | 25.50 | 2026-10-03 |
| 103 | 2 | 90.00 | 2026-10-03 |
| 104 | 3 | 15.00 | 2026-10-04 |
| 105 | 1 | 60.00 | 2026-10-05 |
employees
| id | name | dept | salary | manager_id |
|---|---|---|---|---|
| 1 | Maya | Eng | 150000 | NULL |
| 2 | Ravi | Eng | 120000 | 1 |
| 3 | Lena | Eng | 120000 | 1 |
| 4 | Omar | Eng | 95000 | 2 |
| 5 | Sofia | Sales | 90000 | 1 |
| 6 | Tom | Sales | 70000 | 5 |
| 7 | Ivy | Sales | 70000 | 5 |
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.
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;| name | order_id |
|---|---|
| Asha | 101 |
| Asha | 102 |
| Asha | 105 |
| Ben | 103 |
| Chen | 104 |
| Dara | NULL |
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 matchedResult: 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;| name | orders | spent |
|---|---|---|
| Ben | 1 | 90.00 |
| Asha | 2 | 85.50 |
WHEREremoves Asha's order 101 from 1 October before any totals are computed.GROUP BYmakes one row each for Asha, Ben and Chen.HAVINGremoves 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.
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 SELECT13. 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;| name | salary |
|---|---|
| Ravi | 120000 |
| Lena | 120000 |
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;| dept | name | salary |
|---|---|---|
| Eng | Maya | 150000 |
| Eng | Lena | 120000 |
| Sales | Sofia | 90000 |
| Sales | Ivy | 70000 |
PARTITION BY deptrestarts the numbering for each department.namebreaks ties, so Lena, alphabetically before Ravi, is chosen consistently.- Keep the first two per department. A window function cannot appear directly in
WHERE, becauseWHEREruns 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;| count | |
|---|---|
| a@x.example | 3 |
| b@x.example | 2 |
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);| name | salary | row_number | rank | dense_rank |
|---|---|---|---|---|
| Lena | 120000 | 1 | 1 | 1 |
| Ravi | 120000 | 2 | 1 | 1 |
| Sofia | 90000 | 3 | 3 | 2 |
| Ivy | 70000 | 4 | 4 | 3 |
| Tom | 70000 | 5 | 4 | 3 |
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;| id | placed_on | total | running_total | previous |
|---|---|---|---|---|
| 101 | 2026-10-01 | 40.00 | 40.00 | NULL |
| 102 | 2026-10-03 | 25.50 | 65.50 | 40.00 |
| 103 | 2026-10-03 | 90.00 | 155.50 | 25.50 |
| 104 | 2026-10-04 | 15.00 | 170.50 | 90.00 |
| 105 | 2026-10-05 | 60.00 | 230.50 | 15.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;| name | depth |
|---|---|
| Omar | 0 |
| Ravi | 1 |
| Maya | 2 |
- The anchor finds Omar.
- 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_rows | with_city | no_city |
|---|---|---|
| 4 | 3 | 1 |
The NOT IN trap:
sql
SELECT COUNT(*) FROM customers WHERE city NOT IN ('Leeds', NULL); -- returns 0Asha'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| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| Removes | Chosen rows | All rows | The table |
WHERE allowed | Yes | No | No |
| Speed on a big table | Slow, row by row | Fast | Fast |
| Fires row triggers | Yes | No | No |
| Table still exists | Yes | Yes | No |
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_id | customer_email | customer_city | items |
|---|---|---|---|
| 101 | asha@shop.example | London | shoes, socks |
| 102 | asha@shop.example | London | cap |
- Not 1NF:
itemsholds a list in one cell. Fix: anorder_itemstable with one row per item. - Repeated facts: Asha's city is stored on every order. If she moves, some rows will say London and some Leeds. Fix: a
customerstable, and orders store onlycustomer_id. This is what 2NF and 3NF force.
Denormalization done safely:
| Copy | Why | How to keep it correct |
|---|---|---|
orders.total | Avoid summing items on every list page | Update in the same transaction as the items |
invoice_lines.product_name | Legal record must not change if the product is renamed | Copy once, never update |
| A reporting table | Heavy analytics off the main tables | Rebuild 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)
);- Delete an order, and its items go with it.
- No
ON DELETErule, so deleting a product that appears on an order is refused. - No zero or negative quantities, whatever the API sends.
- A composite primary key: the same product appears at most once per order.
| Key term | Meaning |
|---|---|
| Candidate key | Any column set that could be the primary key, such as id or email |
| Natural key | A real-world value, such as an email or ISBN |
| Surrogate key | A made-up ID, such as a serial number or UUID |
| Composite key | A 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| View | Materialized view | |
|---|---|---|
| Stores data | No | Yes |
| Always current | Yes | Only after a refresh |
| Read speed | As slow as the query | Fast; can be indexed |
| Use for | Simplifying queries, limiting access | Dashboards, heavy reports |