Checkpoint — Subqueries

Q: What does a scalar subquery return when it finds no rows?

NULL. Any comparison with it, such as salary > (SELECT …), is then unknown, so WHERE keeps no rows. Wrap the subquery in COALESCE when a default value is wanted instead.

Q: Why must a derived table have an alias in MySQL?

MySQL refers to every table in FROM by a name, and a subquery has none of its own, so it raises error 1248, "Every derived table must have its own alias". Write FROM (SELECT …) AS t and use t.column in the outer query.

25 min · +25 XP · 3 exercises · 12 questions

Exercises0/3

Quiz · 70% to clear12 q

This checkpoint covers the whole module: scalar subqueries in WHERE, the select list and HAVING, with their zero-row and many-row behaviour; membership with IN, existence with EXISTS, multi-column IN and ANY/ALL; correlated subqueries for per-group comparisons and the latest row per group; derived tables for aggregates of aggregates and for joining per-parent totals without fan-out; and the NOT IN trap with NULL.

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

What does a scalar subquery return for zero rows, and what does MySQL do for two?
How do you find the second highest distinct salary, returning NULL when there is none?
When would you choose IN, EXISTS or a join?
What makes a subquery correlated, and why should every inner column be qualified?
How do you return the latest row per group, and what happens with ties?
Why must a derived table have an alias, and how does aggregating before a join prevent fan-out?
Why does NOT IN return nothing when its subquery yields a NULL, and what do you write instead?

The three queries are products priced above their own category's average, customers with no order in 2024 when some orders have no customer, and the top-spending customer of each city using a derived table.

Common questions

What does a scalar subquery return when it finds no rows?

NULL. Any comparison with it, such as salary > (SELECT …), is then unknown, so WHERE keeps no rows. Wrap the subquery in COALESCE when a default value is wanted instead.

How do you compare each row with the average of its own group?

Use a correlated subquery that filters the inner table by the outer row's group: WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.dept = e.dept). Or compute the per-group averages once in a derived table and join it on the group column.

Why must a derived table have an alias in MySQL?

MySQL refers to every table in FROM by a name, and a subquery has none of its own, so it raises error 1248, "Every derived table must have its own alias". Write FROM (SELECT …) AS t and use t.column in the outer query.

SQL Subqueries Quiz: Correlated, EXISTS and NOT IN Practice

Test yourself: 12 questions and three queries on SQL subqueries: scalar subqueries, IN and EXISTS, correlated subqueries, derived tables and NOT IN with NULL.

Course: SQL study plan
Module: Subqueries
Kind: Checkpoint — cleared at 70%
Reading time: 25 min
Runtime: MySQL 8 syntax on SQLite 3.49

Checkpoint — Subqueries is the checkpoint that closes the Subqueries module: a graded quiz and whole-program exercises, passed at 70%.

Instructions

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

What does a scalar subquery return for zero rows, and what does MySQL do for two?
How do you find the second highest distinct salary, returning NULL when there is none?
When would you choose IN, EXISTS or a join?
What makes a subquery correlated, and why should every inner column be qualified?
How do you return the latest row per group, and what happens with ties?
Why must a derived table have an alias, and how does aggregating before a join prevent fan-out?
Why does NOT IN return nothing when its subquery yields a NULL, and what do you write instead?

Common questions

What does a scalar subquery return when it finds no rows?

NULL. Any comparison with it, such as salary > (SELECT …), is then unknown, so WHERE keeps no rows. Wrap the subquery in COALESCE when a default value is wanted instead.

How do you compare each row with the average of its own group?

Why must a derived table have an alias in MySQL?

Exercises

Priced above their category average

List the products priced above the average price of their own category.

Table: products(id, name, category, price).

id	name	category	price
1	Atlas	Books	40
2	Novel	Books	15
3	Guide	Books	30
4	Chess	Games	25
5	Go	Games	35
6	Dice	Games	5
7	Vinyl	Music	20

Output: columns product, category, price, ordered by category, then price descending, then product.

product | category | price
Atlas | Books | 40
Guide | Books | 30
Go | Games | 35
Chess | Games | 25

No order in 2024

List the customers who placed no order dated in 2024 (orders in other years do not count). Some orders have no customer (NULL customer_id).

Tables: customers(id, name) and orders(id, customer_id, order_date).

customers.id	name
1	Asha
2	Ben
3	Chen
4	Dina

orders.id	customer_id	order_date
10	1	2024-03-01
11	2	2023-12-31
12	2	2025-01-01
13	NULL	2024-05-05
14	4	2024-12-31

Output: columns id, name, ordered by id.

id | name
2 | Ben
3 | Chen

Top spender in each city

For each city, find the customer (or customers, if tied) with the highest total spend. Only customers with at least one order take part; customers with no city are ignored, and orders of unknown customers are ignored.

Tables: customers(id, name, city) and orders(id, customer_id, amount).

customers.id	name	city
1	Asha	Pune
2	Ben	Delhi
3	Chen	Pune
4	Dina	Delhi
5	Eli	Kochi

orders.id	customer_id	amount
1	1	200
2	1	100
3	3	250
4	2	90
5	4	60
6	4	30
7	9	1000

Output: columns city, customer, total, ordered by city, then customer.

city | customer | total
Delhi | Ben | 90
Delhi | Dina | 90
Pune | Asha | 300

In this module: Subqueries

← NOT IN and NULL — the subquery trap · UNION and UNION ALL — stacking result sets →