# SQL for QA: 8 exercises with answers

QA Hub · Version 1 · 22 October 2026

Practice filtering, JOIN, GROUP BY, HAVING, NULL and data-quality checks. Use a disposable SQL session with [the shared fixture](../sql-for-qa-fixture.sql). It creates two temporary tables and fictional data; no application tables are modified. Execute the fixture and exercises in the same session. Closing the session removes the temporary tables. The examples use syntax supported by PostgreSQL and SQLite.

If you download this document separately, also download `https://qahub.pro/resources/sql-for-qa-fixture.sql`.

## Dataset and business rules

`qa_customers(customer_id, name, email)` contains Ada (1), Boris (2), Chen (3), Dina (4). Ada and Chen deliberately share an email; Dina has NULL email.

`qa_orders(order_id, customer_id, status, amount_cents)` contains five orders. Amounts are integer cents. Orders 101, 103 and 105 are paid; order 102 is cancelled; order 104 is pending. Order 105 intentionally has zero value.

For these exercises, paid order amounts must be positive, non-null emails must be unique, and reports must include customers without orders. These are example requirements; real product rules may differ.

## Tasks

1. List paid orders, ordered by their ID.
2. Count all orders per customer, including customers with none.
3. Calculate paid revenue per customer, including zero-revenue customers.
4. Find customers who have never placed an order.
5. Find duplicate non-null email addresses.
6. Find paid orders with an invalid amount under the stated rule.
7. Find customers with at least two orders, regardless of status.
8. Find customers with a missing email.

Before checking the answers, predict the number of rows and exact values. A query that runs successfully can still be logically incorrect.

## Answers and expected results

### 1. Paid orders
```sql
SELECT order_id, customer_id, amount_cents
FROM qa_orders WHERE status = 'paid'
ORDER BY order_id;
```
Expected: `(101, 1, 1200)`, `(103, 2, 700)`, `(105, 3, 0)`.

### 2. Order count including customers with zero orders
```sql
SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count
FROM qa_customers c
LEFT JOIN qa_orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;
```
Expected: `(1, Ada, 2)`, `(2, Boris, 2)`, `(3, Chen, 1)`, `(4, Dina, 0)`.
`COUNT(*)` would incorrectly count Dina's unmatched LEFT JOIN row as one order.

### 3. Paid revenue in cents
```sql
SELECT c.customer_id,
       COALESCE(SUM(o.amount_cents), 0) AS paid_cents
FROM qa_customers c
LEFT JOIN qa_orders o
  ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id
ORDER BY c.customer_id;
```
Expected: `(1, 1200)`, `(2, 700)`, `(3, 0)`, `(4, 0)`.
Putting `o.status = 'paid'` in WHERE would remove customers without matching paid orders. Dina's absence is a reporting defect even though the total revenue remains 1900 cents.

### 4. Customers without orders
```sql
SELECT c.customer_id, c.name
FROM qa_customers c
WHERE NOT EXISTS (
  SELECT 1 FROM qa_orders o WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;
```
Expected: `(4, Dina)`.

### 5. Duplicate emails
```sql
SELECT email, COUNT(*) AS occurrences
FROM qa_customers WHERE email IS NOT NULL
GROUP BY email HAVING COUNT(*) > 1
ORDER BY email;
```
Expected: `(ada@example.invalid, 2)`. Case folding and whitespace normalization would require an additional agreed product rule.

### 6. Invalid paid amounts
```sql
SELECT order_id, amount_cents
FROM qa_orders WHERE status = 'paid' AND amount_cents <= 0
ORDER BY order_id;
```
Expected: `(105, 0)`. This is a defect only because our exercise explicitly disallows zero-value paid orders.

### 7. Customers with at least two orders
```sql
SELECT customer_id, COUNT(*) AS order_count
FROM qa_orders GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;
```
Expected: `(1, 2)`, `(2, 2)`.

### 8. Missing email
```sql
SELECT customer_id, name FROM qa_customers
WHERE email IS NULL ORDER BY customer_id;
```
Expected: `(4, Dina)`. `email = NULL` does not test for a missing value.

## QA follow-up

Attach the requirement, query, environment, expected and actual rows to a defect. Check whether duplicates are real business violations before deleting anything. These exercises only read prepared data after setup.

Continue: https://qahub.pro/en/backend-api/sql-for-qa