Erudex sealErudex

SQL & Relational Databases

SQL Practice Questions: JOIN, GROUP BY and NULL with Answers

6 min read24 October 2026

Study it properly: SQL & Relational Databases

Query, design and manage relational databases with professional SQL.

View the course, included with All-AccessFree practice test

Practise SQL with a dataset small enough to check by hand. These five exercises connect to the querying, relationships, aggregation and analytics modules in SQL & Relational Databases. The sample is invented for practice, not taken from customer records.

Run the setup in a disposable PostgreSQL database. Read each question before the answer. Compare both the returned values and the number of rows: a query that returns plausible totals can still drop a customer or count the same order twice.

Key points

  • •Predict the row count as well as the totals.
  • •Use HAVING for aggregate filters.
  • •Preserve customers without orders deliberately.
  • •Test each query after changing the input data.

Set up two tables

Customers 1, 2 and 3 are Ada, Ben and Cy. Ada has two orders, Ben has one, and Cy has none. Amounts are arbitrary currency units. Keep the customer primary key and the order primary key distinct.

CREATE TABLE customers (id integer PRIMARY KEY, name text);
CREATE TABLE orders (id integer PRIMARY KEY, customer_id integer REFERENCES customers(id), amount numeric);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy');
INSERT INTO orders VALUES (101, 1, 40), (102, 1, 60), (103, 2, 30);

1. Which orders exceed 35?

Filter the orders table, not a customer total. The result should contain orders 101 and 102, with amounts 40 and 60. ORDER BY makes the expected output deterministic. Changing > to >= matters when an amount is exactly 35.

SELECT id, amount FROM orders WHERE amount > 35 ORDER BY id;

2. Show each order with its customer

Join on customer_id = customers.id. Expect three rows: Ada/101/40, Ada/102/60 and Ben/103/30. Ada appearing twice is correct because the requested grain is one row per order. Joining orders.id to customers.id would compare unrelated identifiers.

SELECT c.name, o.id, o.amount
FROM customers c JOIN orders o ON o.customer_id = c.id
ORDER BY o.id;

3. Include customers without orders

Use a LEFT JOIN with customers on the left. Cy must remain visible. COUNT(o.id) returns zero for Cy; COUNT(*) would count the preserved unmatched row and return one. SUM over no matched amounts is NULL, so COALESCE supplies zero for this report.

SELECT c.id, c.name, COUNT(o.id) AS order_count,
       COALESCE(SUM(o.amount), 0) AS total
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name ORDER BY c.id;
  • •Ada: 2 orders, total 100
  • •Ben: 1 order, total 30
  • •Cy: 0 orders, total 0

4. Which customers spent at least 80?

HAVING filters groups after aggregation. WHERE filters input rows before aggregation. Filtering each order to amount >= 80 would remove every order and miss Ada's combined 100.

SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id
HAVING SUM(amount) >= 80 ORDER BY customer_id;

5. Find customers with no orders

NOT EXISTS tests whether any matching order exists and returns only Cy. It avoids the NULL surprises that can occur with NOT IN when its subquery includes NULL. Another valid method is a LEFT JOIN followed by a test on the non-null order primary key.

SELECT c.id, c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
ORDER BY c.id;

Check your method, then change the data

Add an order for Cy and rerun all five queries. Predict each changed result before running it. Add a second Ben order below 35 and check that total spending changes while the first filter still excludes that order.

Before using these patterns in a real report, define the reporting grain, decide how refunds and missing amounts are represented, and check the relationships. Joining another one-to-many table can multiply rows before SUM runs. Return to the linked course for CTEs, window functions and query-plan practice.

Frequently asked questions

Which SQL dialect do the examples use?
PostgreSQL. The basic joins and aggregates are broadly portable, but check your database's syntax and data-type rules.
Why does COUNT(*) differ from COUNT(order id)?
COUNT(*) counts rows, including an unmatched row preserved by a LEFT JOIN. COUNT of a column counts its non-NULL values.

Study it properly: SQL & Relational Databases

Query, design and manage relational databases with professional SQL.

View the course, included with All-AccessFree practice test

More on this subject

References

All articles · Sitemap