SQL Database Quiz Write the Right Query

Q: Why does my query fail when I use an aggregate in WHERE?

WHERE filters individual rows before aggregation happens. Aggregates like COUNT and SUM only exist after rows are grouped, so conditions on aggregates belong in HAVING . If you need both, use WHERE for row filters and HAVING for group filters.

Q: Why can’t I reference a SELECT alias in WHERE?

Logically, the WHERE clause is evaluated before the SELECT list is produced, so an alias from SELECT is not available yet. Common fixes are to repeat the expression in WHERE, use a subquery or CTE that defines the alias, or in some engines use the alias only in ORDER BY .

Q: My LEFT JOIN “works,” but rows disappear after I add a filter. What happened?

If you put a condition on the right table in WHERE , NULL-extended rows from the LEFT JOIN fail the predicate and get removed. Move that condition into the ON clause to keep unmatched left rows, or keep it in WHERE if you truly want an inner join effect.

Q: Why does NOT IN sometimes return no rows even when it “should”?

NOT IN (subquery) becomes tricky if the subquery can return NULL . Comparisons against NULL are “unknown,” which can make the whole predicate fail for every row. A safer pattern is NOT EXISTS with a correlated subquery, or explicitly filter NULLs out of the subquery.

Q: I used DISTINCT to remove duplicates after a JOIN. Is that a good fix?

It can hide a modeling or join-key problem. If you expect one row per entity, verify join cardinality and join predicates first. DISTINCT is appropriate when duplicates are a real part of the relationship and you intentionally want a set of unique values.

Q: Do SQL query skills transfer to application code in Python or JavaScript?

Yes. The SQL logic stays the same, but bugs often come from parameter binding, string building, and misunderstanding result shapes (one row vs many). If you want adjacent practice, Check Your Python Programming Code Skills and Test JavaScript Skills From Basics to Advanced .

8 – 16 Questions 6 min

This quiz focuses on writing correct SQL SELECT queries across filters, joins, aggregation, and ordering. It targets the subtle mistakes that change result sets, like misplaced predicates, incorrect GROUP BY logic, and NULL handling. Use it to confirm you can translate a data question into a precise query that returns the intended rows.

Start Quiz

Part 1 — Questions

Answer each question on paper. Do not turn the page until you are ready to check your work.

In SQL, `SELECT * FROM customers;` returns every column from the `customers` table.

True
False

You need the names of customers who live in Boston. Which query matches that request?

SELECT name FROM customers WHERE city = 'Boston';
SELECT name FROM customers ORDER BY city = 'Boston';
SELECT name FROM customers HAVING city = 'Boston';
SELECT name FROM customers GROUP BY city = 'Boston';

Your manager asks for the 10 most recent orders by `created_at`. Which query does that?

SELECT * FROM orders LIMIT 10 ORDER BY created_at DESC;
SELECT * FROM orders WHERE created_at DESC LIMIT 10;
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
SELECT * FROM orders ORDER BY created_at ASC LIMIT 10;

`SELECT DISTINCT first_name, last_name FROM employees;` removes duplicates based on the combination of `first_name` and `last_name` together.

True
False

You want all customers whose email ends with `@acme.com`. Which filter matches that?

WHERE email LIKE '%@acme.com%';
WHERE email LIKE '%@acme.com';
WHERE email LIKE '@acme.com';
WHERE email LIKE '@acme.com%';

You are asked to list customers who have never placed an order. Tables: `customers(id)`, `orders(customer_id)`. Which query is safest?

SELECT c.id FROM customers c INNER JOIN orders o ON o.customer_id = c.id WHERE o.customer_id IS NULL;
SELECT c.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.customer_id IS NULL;
SELECT c.id FROM customers c WHERE c.id NOT IN (SELECT customer_id FROM orders);
SELECT c.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.customer_id = NULL;

With a `LEFT JOIN`, putting a filter like `WHERE payments.status = 'PAID'` can remove customers who have no matching payment row.

True
False

You need the top 5 products by revenue. Tables: `order_items(product_id, qty, unit_price)`, `products(id, name)`. Which query matches?

SELECT p.name, COUNT(*) AS revenue FROM products p JOIN order_items oi ON oi.product_id = p.id GROUP BY p.name ORDER BY revenue DESC LIMIT 5;
SELECT p.name, SUM(oi.qty) AS revenue FROM products p JOIN order_items oi ON oi.product_id = p.id GROUP BY p.name ORDER BY revenue DESC LIMIT 5;
SELECT p.name, SUM(oi.qty * oi.unit_price) AS revenue FROM products p JOIN order_items oi ON oi.product_id = p.id GROUP BY p.name ORDER BY revenue DESC LIMIT 5;
SELECT p.name, oi.qty * oi.unit_price AS revenue FROM products p JOIN order_items oi ON oi.product_id = p.id ORDER BY revenue DESC LIMIT 5;

You need each customer’s most recent order date. Table: `orders(customer_id, created_at)`. Which query returns one row per customer?

SELECT customer_id, MAX(created_at) FROM orders;
SELECT o.customer_id, o.created_at FROM orders o WHERE o.created_at = (SELECT MAX(created_at) FROM orders WHERE customer_id = o.customer_id);
SELECT DISTINCT customer_id, created_at FROM orders WHERE created_at = MAX(created_at);
SELECT customer_id, created_at FROM orders ORDER BY created_at DESC;

10.

You need the 3 most recent orders per customer. Table: `orders(id, customer_id, created_at)`. Which query pattern is best?

SELECT * FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn FROM orders o) x WHERE rn <= 3;
SELECT customer_id, MAX(created_at) FROM orders GROUP BY customer_id LIMIT 3;
SELECT o.* FROM orders o WHERE created_at IN (SELECT created_at FROM orders ORDER BY created_at DESC LIMIT 3);
SELECT * FROM orders ORDER BY created_at DESC LIMIT 3;

11.

You need a running total of daily revenue. Table: `daily_sales(day, revenue)`. Which query returns `day`, `revenue`, and `running_revenue`?

SELECT day, revenue, SUM(revenue) OVER (PARTITION BY day) AS running_revenue FROM daily_sales;
SELECT day, revenue, SUM(revenue) AS running_revenue FROM daily_sales ORDER BY day;
SELECT day, revenue, SUM(revenue) GROUP BY day ORDER BY day;
SELECT day, revenue, SUM(revenue) OVER (ORDER BY day) AS running_revenue FROM daily_sales;

12.

You need customers who bought every product in the 'Coffee' category. Tables: `customers(id)`, `orders(id, customer_id)`, `order_items(order_id, product_id)`, `products(id, category)`. Which pattern correctly expresses “for all products”?

SELECT c.id FROM customers c WHERE EXISTS (SELECT 1 FROM products p WHERE p.category = 'Coffee');
SELECT c.id FROM customers c WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.category = 'Coffee' AND NOT EXISTS (SELECT 1 FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.customer_id = c.id AND oi.product_id = p.id));
SELECT c.id FROM customers c WHERE c.id IN (SELECT customer_id FROM orders);
SELECT c.id FROM customers c JOIN orders o ON o.customer_id = c.id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id WHERE p.category = 'Coffee' GROUP BY c.id HAVING COUNT(*) = (SELECT COUNT(*) FROM products WHERE category = 'Coffee');

Part 2 — Answer key

Compare your answers after you have finished Part 1.

In SQL, `SELECT * FROM customers;` returns every column from the `customers` table.
True
You need the names of customers who live in Boston. Which query matches that request?
SELECT name FROM customers WHERE city = 'Boston';
Your manager asks for the 10 most recent orders by `created_at`. Which query does that?
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
`SELECT DISTINCT first_name, last_name FROM employees;` removes duplicates based on the combination of `first_name` and `last_name` together.
True
You want all customers whose email ends with `@acme.com`. Which filter matches that?
WHERE email LIKE '%@acme.com';
You are asked to list customers who have never placed an order. Tables: `customers(id)`, `orders(customer_id)`. Which query is safest?
SELECT c.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.customer_id IS NULL;
With a `LEFT JOIN`, putting a filter like `WHERE payments.status = 'PAID'` can remove customers who have no matching payment row.
True
You need the top 5 products by revenue. Tables: `order_items(product_id, qty, unit_price)`, `products(id, name)`. Which query matches?
SELECT p.name, SUM(oi.qty * oi.unit_price) AS revenue FROM products p JOIN order_items oi ON oi.product_id = p.id GROUP BY p.name ORDER BY revenue DESC LIMIT 5;
You need each customer’s most recent order date. Table: `orders(customer_id, created_at)`. Which query returns one row per customer?
SELECT o.customer_id, o.created_at FROM orders o WHERE o.created_at = (SELECT MAX(created_at) FROM orders WHERE customer_id = o.customer_id);
You need the 3 most recent orders per customer. Table: `orders(id, customer_id, created_at)`. Which query pattern is best?
SELECT * FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn FROM orders o) x WHERE rn <= 3;
You need a running total of daily revenue. Table: `daily_sales(day, revenue)`. Which query returns `day`, `revenue`, and `running_revenue`?
SELECT day, revenue, SUM(revenue) OVER (ORDER BY day) AS running_revenue FROM daily_sales;
You need customers who bought every product in the 'Coffee' category. Tables: `customers(id)`, `orders(id, customer_id)`, `order_items(order_id, product_id)`, `products(id, category)`. Which pattern correctly expresses “for all products”?
SELECT c.id FROM customers c WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.category = 'Coffee' AND NOT EXISTS (SELECT 1 FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.customer_id = c.id AND oi.product_id = p.id));

1In SQL, `SELECT * FROM customers;` returns every column from the `customers` table.

True / False

2You need the names of customers who live in Boston. Which query matches that request?

3Your manager asks for the 10 most recent orders by `created_at`. Which query does that?

4`SELECT DISTINCT first_name, last_name FROM employees;` removes duplicates based on the combination of `first_name` and `last_name` together.

True / False

5You want all customers whose email ends with `@acme.com`. Which filter matches that?

6You are asked to list customers who have never placed an order. Tables: `customers(id)`, `orders(customer_id)`. Which query is safest?

7With a `LEFT JOIN`, putting a filter like `WHERE payments.status = 'PAID'` can remove customers who have no matching payment row.

True / False

8You need the top 5 products by revenue. Tables: `order_items(product_id, qty, unit_price)`, `products(id, name)`. Which query matches?

9You need each customer’s most recent order date. Table: `orders(customer_id, created_at)`. Which query returns one row per customer?

10You need the 3 most recent orders per customer. Table: `orders(id, customer_id, created_at)`. Which query pattern is best?

11You need a running total of daily revenue. Table: `daily_sales(day, revenue)`. Which query returns `day`, `revenue`, and `running_revenue`?

12You need customers who bought every product in the 'Coffee' category. Tables: `customers(id)`, `orders(id, customer_id)`, `order_items(order_id, product_id)`, `products(id, category)`. Which pattern correctly expresses “for all products”?

SQL Query Writing Pitfalls That Produce “Plausible” but Wrong Results

Intermediate SQL mistakes rarely throw syntax errors. They return rows that look reasonable, which is why they show up so often in skills checks.

Filtering at the wrong stage (WHERE vs HAVING)

Symptom: aggregates look off or groups disappear unexpectedly.
Fix: use WHERE for row-level filters before grouping, and HAVING only for conditions on aggregates like COUNT(*) or SUM(amount).

Accidentally turning a LEFT JOIN into an INNER JOIN

Symptom: “missing” parent rows that should stay even when the right table has no match.
Fix: keep right-table filters in the ON clause (for optional matches). Putting right_table.status = 'active' in WHERE removes NULL-extended rows.

GROUP BY mismatch and non-aggregated columns

Symptom: SQL Server errors, or MySQL returns arbitrary values when a mode is permissive.
Fix: every non-aggregate expression in SELECT must be in GROUP BY (or rewritten with an aggregate, or moved into a subquery).

NULL semantics and three-valued logic

Symptom: col = NULL never matches, NOT IN returns zero rows, or comparisons behave inconsistently.
Fix: use IS NULL, prefer NOT EXISTS over NOT IN for subqueries, and be explicit with COALESCE when NULL should act like a value.

Assuming result order without ORDER BY

Symptom: different “top” rows across runs or environments.
Fix: pair ORDER BY with LIMIT, TOP, or pagination. Without it, row order is not guaranteed.

Authoritative References for SELECT Syntax, Joins, and Query Semantics

Microsoft Learn: SELECT (Transact-SQL): SQL Server’s SELECT statement syntax, clauses, and common patterns in T-SQL.
Microsoft Learn: GROUP BY (Transact-SQL): Clear rules for grouping and the requirement that non-aggregates appear in GROUP BY.
PostgreSQL Documentation: SELECT: Detailed SELECT grammar, subqueries, set operations, and PostgreSQL-specific extensions.
MySQL 8.4 Reference Manual: SELECT Statement: MySQL’s SELECT syntax, including joins, LIMIT variants, and SELECT modifiers.
SQLite Documentation: SELECT: Practical SELECT statement structure and notes on evaluation and result ordering in SQLite.

SQL Query Writing FAQ: Joins, Aggregation, NULL Semantics, and “Right” Results

Why does my query fail when I use an aggregate in WHERE?

WHERE filters individual rows before aggregation happens. Aggregates like COUNT and SUM only exist after rows are grouped, so conditions on aggregates belong in HAVING. If you need both, use WHERE for row filters and HAVING for group filters.

Why can’t I reference a SELECT alias in WHERE?

Logically, the WHERE clause is evaluated before the SELECT list is produced, so an alias from SELECT is not available yet. Common fixes are to repeat the expression in WHERE, use a subquery or CTE that defines the alias, or in some engines use the alias only in ORDER BY.

My LEFT JOIN “works,” but rows disappear after I add a filter. What happened?

If you put a condition on the right table in WHERE, NULL-extended rows from the LEFT JOIN fail the predicate and get removed. Move that condition into the ON clause to keep unmatched left rows, or keep it in WHERE if you truly want an inner join effect.

Why does NOT IN sometimes return no rows even when it “should”?

NOT IN (subquery) becomes tricky if the subquery can return NULL. Comparisons against NULL are “unknown,” which can make the whole predicate fail for every row. A safer pattern is NOT EXISTS with a correlated subquery, or explicitly filter NULLs out of the subquery.

I used DISTINCT to remove duplicates after a JOIN. Is that a good fix?

It can hide a modeling or join-key problem. If you expect one row per entity, verify join cardinality and join predicates first. DISTINCT is appropriate when duplicates are a real part of the relationship and you intentionally want a set of unique values.

Do SQL query skills transfer to application code in Python or JavaScript?

Yes. The SQL logic stays the same, but bugs often come from parameter binding, string building, and misunderstanding result shapes (one row vs many). If you want adjacent practice, Check Your Python Programming Code Skills and Test JavaScript Skills From Basics to Advanced.

Looking for more? Browse QuizWiz Technology & IT collection or explore the full compliance and training quizzes on QuizWiz.