What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes the rows you choose (WHERE) and can be rolled back. TRUNCATE empties the whole table quickly and keeps its structure. DROP removes the table itself, with its structure and data.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row from the left table and fills the right side with NULL where nothing matches.

What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

All three number rows in an order. RANK gives ties the same number and skips the next numbers. DENSE_RANK gives ties the same number and does not skip. ROW_NUMBER never repeats a number.

Which SQL dialect do these answers use?

PostgreSQL. LIMIT/OFFSET, window functions, CTEs and joins work almost everywhere. In SQL Server use TOP or OFFSET FETCH instead of LIMIT, and in Oracle use FETCH FIRST.

SQL interview questions, with answers you can run

Most SQL interviews repeat the same dozen ideas. Below are the usual ones, with a short answer and a query that works in PostgreSQL. The examples use a small bakery database (customers, products, orders and order_items), so you can try each one in a lesson on this site.

Find the second highest value

A simple answer is to sort in descending order and skip one row. It returns a single row even if several rows share the second price.

If ties matter, rank with DENSE_RANK and keep rank 2. DENSE_RANK does not leave gaps after ties, so rank 2 is always the second distinct value.

SELECT name, unit_price
FROM (
  SELECT name, unit_price, DENSE_RANK() OVER (ORDER BY unit_price DESC) AS r
  FROM products
) AS ranked
WHERE r = 2;

Result: The Chocolate cake slice, at 4.20.

Find duplicates

Group by the column that should be unique and keep the groups that have more than one row.

SELECT order_date, COUNT(*) AS orders
FROM orders
GROUP BY order_date
HAVING COUNT(*) > 1
ORDER BY order_date;

Result: Three dates, each with two orders: 1, 2 and 6 September.

Find rows with no match

Use a LEFT JOIN and keep the rows where the right side is NULL, or use NOT EXISTS. Both answer "which customers never ordered?".

Avoid NOT IN with a subquery that can return NULL: a single NULL makes the whole condition unknown and you get no rows.

SELECT c.first_name, c.last_name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Result: Grace Kim and Hugo Meyer.

Top row per group

Rank inside each group with PARTITION BY, then keep rank 1 in an outer query. Use RANK to keep ties, or ROW_NUMBER to keep exactly one row per group.

SELECT category, name, units
FROM (
  SELECT p.category, p.name, SUM(oi.quantity) AS units,
         RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity) DESC) AS rk
  FROM products AS p
  JOIN order_items AS oi ON oi.product_id = p.product_id
  GROUP BY p.category, p.name
) AS ranked
WHERE rk = 1
ORDER BY category, name;

Result: The best seller of each category. Cake shows two products because they tie at 3 units.

Running total

Use SUM as a window function with ORDER BY. Order by a column without ties, or rows that tie are added together.

SELECT order_id,
       SUM(quantity) AS units,
       SUM(SUM(quantity)) OVER (ORDER BY order_id) AS running_units
FROM order_items
GROUP BY order_id
ORDER BY order_id;

Result: Units per order and the total so far, ending at 56.

WHERE versus HAVING

WHERE filters rows before they are grouped, so it cannot use an aggregate. HAVING filters groups after the aggregation. In the example, WHERE removes cheap products first, and HAVING keeps categories with at least two of the remaining ones.

SELECT category, COUNT(*) AS products
FROM products
WHERE unit_price > 2.50
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY category;

Result: Bread and cake, with two products each above 2.50. Pastry and drinks have only one product above 2.50 and are dropped.

UNION versus UNION ALL

UNION removes duplicate rows, which needs extra work. UNION ALL keeps everything and is faster. Use UNION ALL unless you need the duplicates removed.

SELECT city FROM customers
UNION
SELECT city FROM customers
ORDER BY city;

Result: Five cities, each once. With UNION ALL there would be 16 rows.

Common questions

What is the difference between DELETE, TRUNCATE and DROP?: DELETE removes the rows you choose (WHERE) and can be rolled back. TRUNCATE empties the whole table quickly and keeps its structure. DROP removes the table itself, with its structure and data.
What is the difference between INNER JOIN and LEFT JOIN?: INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row from the left table and fills the right side with NULL where nothing matches.
What is the difference between RANK, DENSE_RANK and ROW_NUMBER?: All three number rows in an order. RANK gives ties the same number and skips the next numbers. DENSE_RANK gives ties the same number and does not skip. ROW_NUMBER never repeats a number.
Which SQL dialect do these answers use?: PostgreSQL. LIMIT/OFFSET, window functions, CTEs and joins work almost everywhere. In SQL Server use TOP or OFFSET FETCH instead of LIMIT, and in Oracle use FETCH FIRST.