SQL Interview Questions: Practical and Conceptual

Published · by JOBB GROUP FZE

SQL interviews test two different abilities. The first is practical: given some tables and a question, can you write a query that returns the right rows? The second is conceptual: do you understand why a query behaves the way it does, so that you notice when the answer looks plausible but is wrong? Candidates who only drill practice problems tend to fail the second part, usually on joins and NULLs.

This guide explains the concepts interviewers probe most, with short example queries, then gives practice questions for freshers and for experienced candidates. Examples use standard SQL that runs in PostgreSQL; most of it works the same way in MySQL, SQL Server and other databases, but check the dialect the employer uses. The facts about query behaviour are taken from the PostgreSQL documentation.

The example tables

All examples below use two small tables:

  • customers: customer_id, name, city
  • orders: order_id, customer_id, order_date, amount

Some customers have no orders. Some orders have a NULL amount because they were cancelled before payment. Interviewers build exactly these traps into their test data.

Concept 1: the order a query is processed in

You write SELECT first, but the database does not process it first. The PostgreSQL documentation describes the WHERE, GROUP BY and HAVING clauses as “a pipeline of successive transformations” applied to the table produced by the FROM clause (PostgreSQL). The select list is evaluated after that, and ORDER BY after the select list.

This explains several classic interview questions:

  • Why can’t you use an aggregate such as SUM in WHERE? Because WHERE filters individual rows before any groups exist.
  • Why can’t you use a window function in WHERE? Because window functions are computed after WHERE, GROUP BY and HAVING (PostgreSQL).
  • Why does a column alias defined in SELECT often fail in WHERE? Because WHERE has already run.

Concept 2: joins, and the LEFT JOIN trap

Be ready to define each join precisely. From the PostgreSQL documentation (PostgreSQL):

  • INNER JOIN: for each row of the first table, a row for each row in the second table that satisfies the join condition.
  • LEFT OUTER JOIN: an inner join, plus a row for every unmatched row in the left table, with NULLs in the right table’s columns. The result always has at least one row for each row in the left table.
  • FULL OUTER JOIN: unmatched rows from both sides are kept, each padded with NULLs.
  • CROSS JOIN: every combination of rows. Two tables of N and M rows give N times M rows.

Typical question: “List every customer and the number of orders they have placed, including customers with none.”

SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

Note COUNT(o.order_id), not COUNT(*). Concept 4 explains why.

The trap: where you put a filter on the right-hand table. The documentation is explicit: a restriction in the ON clause is processed before the join, a restriction in the WHERE clause after it, and “that does not matter with inner joins, but it matters a lot with outer joins” (PostgreSQL).

So if you want all customers with their 2026 orders, this keeps customers with no 2026 orders:

SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id AND o.order_date >= DATE '2026-01-01';

Whereas putting the date condition in WHERE removes those customers, because their NULL order_date fails the test, and quietly turns your LEFT JOIN into an inner join. Explaining this unprompted is one of the clearest signals of real SQL experience.

Concept 3: WHERE versus HAVING

WHERE keeps or discards individual rows; HAVING eliminates groups after GROUP BY has run, and can refer to aggregates (PostgreSQL).

Typical question: “Which cities have more than 50 customers?”

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 50;

Good practice: filter rows in WHERE where you can, so fewer rows reach the grouping step, and use HAVING only for conditions on the aggregate.

Concept 4: NULL behaves differently from every other value

NULL means unknown, and SQL follows that strictly. PostgreSQL’s documentation states that ordinary comparison operators return NULL, not true or false, when either input is NULL. Even 7 = NULL yields NULL, and you should not write expression = NULL, because it is not known whether two unknown values are equal. Use IS NULL or IS NOT NULL instead (PostgreSQL).

Because WHERE keeps a row only if the condition is true, a row whose condition evaluates to NULL is discarded (PostgreSQL). That produces a classic surprise:

SELECT COUNT(*) FROM orders WHERE amount <> 100;

This does not count orders with a NULL amount, even though their amount is clearly “not 100” in everyday language. If you need a NULL-safe comparison, PostgreSQL offers IS DISTINCT FROM, which treats two NULLs as equal and a NULL and a value as different (PostgreSQL).

NULLs also affect aggregates:

  • COUNT(*) counts input rows; COUNT(expression) counts rows where the expression is not NULL (PostgreSQL). That is why the customer order count above uses COUNT(o.order_id): customers with no orders get 0 instead of 1.
  • Aggregates other than COUNT return NULL, not zero, when no rows are selected. The documentation suggests COALESCE to substitute zero where needed (PostgreSQL), for example COALESCE(SUM(amount), 0).

Concept 5: window functions

Window functions are the most common “experienced” topic. The key idea, in the PostgreSQL tutorial’s words, is that they “do not cause rows to become grouped into a single output row”: you get a calculation across related rows while every row stays in the result (PostgreSQL). PARTITION BY splits the rows into groups, and ORDER BY inside OVER controls the order in which they are processed.

Typical question: “Find each customer’s two largest orders.”

SELECT customer_id, order_id, amount
FROM (
  SELECT customer_id, order_id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
  WHERE amount IS NOT NULL
) ranked
WHERE rn <= 2;

Why the subquery? Window functions are only permitted in the select list and ORDER BY, so to filter on their result you wrap the query in a sub-select (PostgreSQL).

Follow-up questions you should be ready for:

  • ROW_NUMBER, RANK or DENSE_RANK? ROW_NUMBER gives every row a unique number; RANK gives ties the same rank and leaves gaps after them; DENSE_RANK gives ties the same rank without gaps. Ask the interviewer how ties should be handled.
  • Running totals: SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date). With ORDER BY, the default frame runs from the start of the partition to the current row, plus any following rows tied with it on order_date, which is why the sum accumulates (PostgreSQL).
  • Previous value: LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) to compare each order with the one before.

Concept 6: transactions and indexes

These come up more in engineering and data engineering interviews than in analyst ones.

Transactions. The PostgreSQL tutorial puts it simply: a transaction “bundles multiple steps into a single, all-or-nothing operation”. BEGIN starts it, COMMIT makes the changes permanent and visible together, and ROLLBACK cancels every change since it started. The documentation’s example is a bank transfer: debiting one account and crediting another must either both happen or neither (PostgreSQL).

Indexes. The documentation describes indexes as a common way to improve performance, letting the server find and retrieve specific rows much faster, but notes that they “add overhead to the database system as a whole, so they should be used sensibly” (PostgreSQL). A good interview answer gives both halves: indexes help reads on columns used in filters, joins and sorting, but each one costs storage and slows inserts and updates. Mention that you would check the query plan (EXPLAIN in PostgreSQL) before and after adding one.

SQL interview questions for freshers

  1. What is the difference between WHERE and HAVING?
  2. Explain INNER JOIN and LEFT JOIN with an example.
  3. Why does WHERE column = NULL return no rows?
  4. What is the difference between COUNT(*) and COUNT(column)?
  5. What is a primary key? What is a foreign key?
  6. Write a query to find the total order amount per city.
  7. Write a query to find customers who have never placed an order. (Hint: LEFT JOIN and IS NULL on the right table’s key, or NOT EXISTS.)
  8. What does DISTINCT do, and when is it a sign of a mistake? (When you add it to hide duplicate rows caused by a bad join.)

SQL interview questions for experienced candidates

  1. Find the top three products by revenue in each category.
  2. Calculate month-on-month revenue growth using LAG.
  3. Find customers whose first order was in the last 30 days.
  4. Explain why moving a filter from ON to WHERE changed the row count of a LEFT JOIN.
  5. When would you use a CTE (WITH clause) instead of a subquery? (Readability and reuse within one statement; some databases treat them differently for optimisation, so check the plan.)
  6. A query got slow after the table grew. How do you investigate? (Query plan, missing indexes on filter and join columns, functions on indexed columns, unnecessary SELECT *, row explosion from joins.)
  7. How would you find and remove duplicate rows while keeping the most recent one? (ROW_NUMBER partitioned by the duplicate key, ordered by timestamp descending.)
  8. What is the difference between UNION and UNION ALL? (UNION removes duplicates, which costs extra work; UNION ALL keeps every row.)

How to behave in a live SQL test

  • Ask about the data first. Can a customer have no orders? Can amount be NULL? Are there duplicate IDs? These questions are often what the interviewer is waiting for.
  • Say your plan aloud before typing: which tables, which join, what grain the result should be (one row per customer? per month?).
  • Check row counts after each join. Unexpected growth means the join key is not unique.
  • Test edge cases mentally: no orders, NULL values, ties.
  • Write readable SQL: one clause per line, meaningful aliases, comments if it is long.

If SQL is central to the jobs you are applying for, list the databases and the kinds of queries you write on your CV, not just “SQL”. A data analyst resume builder gives you sections for tools and project outcomes. If you are unsure how to word technical skills, the resume questions hub answers common wording problems.

Frequently asked questions

What SQL topics are asked most in interviews?

Joins, GROUP BY with WHERE and HAVING, NULL handling, aggregates, subqueries and CTEs, and window functions such as ROW_NUMBER and LAG. Engineering roles add transactions, indexes and query performance.

What SQL questions are asked for freshers?

Expect definitions of joins, keys, WHERE versus HAVING and COUNT(*) versus COUNT(column), plus simple queries such as totals per group and customers with no orders.

What SQL questions are asked for experienced candidates?

Expect top-N per group with window functions, growth calculations with LAG, de-duplication, outer join filter placement and diagnosing slow queries.

Why does a LEFT JOIN sometimes act like an INNER JOIN?

Because a filter on the right-hand table was placed in the WHERE clause. Rows with no match have NULLs there, fail the condition and are removed. Put the condition in the ON clause to keep them.

Does it matter which SQL dialect I practise?

Core SQL is similar across databases, but functions for dates, strings and limits differ. Ask the employer which database they use and practise in that dialect.

Sources

Ready to build your resume?

Start with a sample or a blank page. No sign-up needed. Free to edit; US$1.00 only if you download a PDF.

Build My ATS ResumeExplore Resume Templates