SQL interview practice: queries, sample data and answers.

Start with two small tables

Each customer row identifies one customer. Each order row identifies one order and points to a customer through customer_id. A customer can have several orders or none. Amounts are whole rupees in this teaching dataset, and status tells us whether an order is paid or cancelled.

Run the setup below in a fresh SQLite database if you want to execute the examples. These lesson tables are independent of the InterviewSkills exercise database. First inspect the rows: Asha has two paid orders, Bilal has one paid and one cancelled order, Chen has no order, and Diya has one paid order.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount INTEGER NOT NULL,
  status TEXT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers VALUES
  (1, 'Asha'), (2, 'Bilal'), (3, 'Chen'), (4, 'Diya');
INSERT INTO orders VALUES
  (101, 1, 600, 'paid'),
  (102, 1, 400, 'paid'),
  (103, 2, 800, 'paid'),
  (104, 2, 300, 'cancelled'),
  (105, 4, 200, 'paid');

Filter individual orders with WHERE

Question: Which paid orders have an amount of at least 500? One result row should represent one order. Both conditions apply to that order, so combine them with AND in WHERE. The cancelled order is excluded by its status; the smaller paid orders are excluded by their amounts.

Predict the identifiers before running the query, then compare your answer with the result.

SELECT order_id, customer_id, amount
FROM orders
WHERE status = 'paid' AND amount >= 500
ORDER BY order_id;

Filter customer totals with HAVING

Question: Which customers have a paid-order total of at least 900? Now one result row represents a customer’s paid total. WHERE first keeps paid orders; GROUP BY gathers them by customer; SUM calculates each total; HAVING selects the qualifying groups.

SELECT customer_id, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) >= 900
ORDER BY customer_id;

Keep customers with no paid orders

Question: Show every customer, their paid-order count and their paid total. Start from customers and LEFT JOIN qualifying orders. Put the paid-status condition in ON so it controls which orders match while preserving every customer.

SELECT c.customer_id, c.name,
       COUNT(o.order_id) AS paid_orders,
       COALESCE(SUM(o.amount), 0) AS paid_total
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid'
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

Find missing matches with IS NULL

Question: Which customers have no paid order? Reuse the paid-order LEFT JOIN, then keep the rows whose order identifier is NULL. Because order_id is a primary key, an actual matching order cannot have a NULL identifier. The NULL therefore identifies a missing match.

SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid'
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Change the data to test your explanation

Add Esha with one cancelled order using the statements below. Before rerunning the previous queries, predict the changes. The every-customer report should gain Esha with zero paid orders and a zero paid total. The no-paid-order query should now return Chen and Esha.

INSERT INTO customers VALUES (5, 'Esha');
INSERT INTO orders VALUES (106, 5, 900, 'cancelled');

• State what one output row represents before writing a join.
• Check whether conditions apply to source rows, matching rows or grouped totals.
• Test a missing match, repeated customer and exact boundary value.
• Explain each returned row and use explicit ordering when the task requires it.