A join with WHERE and ORDER BY

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which clause order is correct for a join query that filters and sorts?

A SELECT, FROM, JOIN ... ON, WHERE, ORDER BY

B SELECT, FROM, ORDER BY, JOIN ... ON, WHERE

2. Which query lists only students who bought a Notebook? (tables: orders, products)

A SELECT orders.orderId FROM orders WHERE products.name = 'Notebook' JOIN products ON orders.productId = products.productId;

B SELECT orders.orderId FROM orders JOIN products ON orders.productId = products.productId WHERE products.name = 'Notebook';

3. Which query sorts results by the subject name from the second table?

A SELECT students.firstName, subjects.name FROM results ORDER BY subjects.name JOIN students ON results.studentId = students.studentId JOIN subjects ON results.subjectId = subjects.subjectId;

B SELECT students.firstName, subjects.name FROM results JOIN students ON results.studentId = students.studentId JOIN subjects ON results.subjectId = subjects.subjectId ORDER BY subjects.name;

4. True or false: in a join query, WHERE can filter on a field that comes from the second table.

A True

B False

5. Where does the WHERE clause go in a join query?

A After JOIN ... ON and before ORDER BY

B Between FROM and JOIN

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId WHERE results.mark > 80		AJoins students to results, then sorts the joined rows by mark from highest to lowest
2SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId ORDER BY results.mark DESC		BJoins students to results, then keeps only the joined rows where the mark (a field from the second table) is above 80
3The correct clause order for a join query is SELECT, FROM, JOIN ... ON, WHERE, ORDER BY		CORDER BY is placed before WHERE. WHERE must come first, then ORDER BY at the end.
4A join can be filtered using a field from the second table, so only matching joined rows are kept		DSELECT products.name, orders.quantity FROM orders JOIN products ON orders.productId = products.productId WHERE products.category = 'Stationery'
5SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId ORDER BY results.mark WHERE results.mark > 50		ESELECT students.firstName, subjects.name FROM results JOIN subjects ON results.subjectId = subjects.subjectId WHERE subjects.name = 'Maths' ORDER BY students.firstName
6SELECT students.firstName, results.mark FROM students JOIN results WHERE results.mark > 80 ON students.studentId = results.studentId		FWHERE is placed before the ON condition. ON must directly follow JOIN, and WHERE must come after it.

Definitions

JOIN ... ON ... WHERE ... ORDER BY

Fill in the Blanks

Complete each line of code by filling in the missing parts.

SELECT name, price FROM orders JOIN products ON orders.productId = products.productId _____ category = 'Tech' ORDER BY price

SELECT name, mark FROM results JOIN subjects ON results.subjectId = subjects.subjectId WHERE name = 'Maths' ________ mark DESC

SELECT name, mark FROM results JOIN subjects ON results.subjectId = subjects.subjectId WHERE ____ = 'Maths' ORDER BY mark DESC

SELECT name, price FROM orders JOIN products ON orders.productId = products.productId WHERE category = 'Tech' ORDER BY _____

SELECT name, price FROM orders JOIN products __ orders.productId = products.productId WHERE category = 'Tech' ORDER BY price

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A SELECT s.firstName, r.mark

B FROM students s JOIN results r

C ON s.studentId = r.subjectId

D WHERE r.mark > 80

E ORDER BY r.mark DESC

Question 2

A SELECT p.name, o.quantity

B FROM products p JOIN orders o

C WHERE p.productId = o.productId

D WHERE o.quantity > 2

E ORDER BY p.name

Question 3

A SELECT s.name, r.mark

B FROM results r JOIN subjects s

C ON r.subjectId = s.subjectId

D WHERE s.name = Maths

E ORDER BY r.mark DESC

Question 4

A SELECT s.firstName, r.mark

B FROM students s JOIN results

C ON s.studentId = r.studentId

D WHERE r.mark < 50

E ORDER BY r.mark

Question 5

A SELECT s.firstName r.mark

B FROM students s JOIN results r

C ON s.studentId = r.studentId

D WHERE s.house = 'Red'

E ORDER BY r.mark DESC

Answer Key

A join with WHERE and ORDER BY

Section A: Quick Questions

A — SELECT, FROM, JOIN ... ON, WHERE, ORDER BY

B — SELECT orders.orderId FROM orders JOIN products ON orders.productId = products.productId WHERE products.name = 'Notebook';

B — SELECT students.firstName, subjects.name FROM results JOIN students ON results.studentId = students.studentId JOIN subjects ON results.subjectId = subjects.subjectId ORDER BY subjects.name;

A — True

A — After JOIN ... ON and before ORDER BY

Section B: Match the Pairs

SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId WHERE results.mark > 80 → Joins students to results, then keeps only the joined rows where the mark (a field from the second table) is above 80

SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId ORDER BY results.mark DESC → Joins students to results, then sorts the joined rows by mark from highest to lowest

The correct clause order for a join query is SELECT, FROM, JOIN ... ON, WHERE, ORDER BY → SELECT students.firstName, subjects.name FROM results JOIN subjects ON results.subjectId = subjects.subjectId WHERE subjects.name = 'Maths' ORDER BY students.firstName

A join can be filtered using a field from the second table, so only matching joined rows are kept → SELECT products.name, orders.quantity FROM orders JOIN products ON orders.productId = products.productId WHERE products.category = 'Stationery'

SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId ORDER BY results.mark WHERE results.mark > 50 → ORDER BY is placed before WHERE. WHERE must come first, then ORDER BY at the end.

SELECT students.firstName, results.mark FROM students JOIN results WHERE results.mark > 80 ON students.studentId = results.studentId → WHERE is placed before the ON condition. ON must directly follow JOIN, and WHERE must come after it.

Section C: Definitions

JOIN ... ON ... WHERE ... ORDER BY — After JOIN ... ON links two tables, WHERE filters the joined rows and ORDER BY sorts them. Clause order: SELECT, FROM, JOIN ON, WHERE, ORDER BY.

Section D: Fill in the Blanks

SELECT name, price FROM orders JOIN products
ON orders.productId = products.productId
WHERE category = 'Tech' ORDER BY price

SELECT name, mark FROM results JOIN subjects
ON results.subjectId = subjects.subjectId
WHERE name = 'Maths' ORDER BY mark DESC

SELECT name, mark FROM results JOIN subjects
ON results.subjectId = subjects.subjectId
WHERE name = 'Maths' ORDER BY mark DESC

SELECT name, price FROM orders JOIN products
ON orders.productId = products.productId
WHERE category = 'Tech' ORDER BY price

SELECT name, price FROM orders JOIN products
ON orders.productId = products.productId
WHERE category = 'Tech' ORDER BY price

Section E: Spot the Error

C — The ON condition must match the foreign key to the primary key it refers to. results.studentId links to students.studentId, so it should be ON s.studentId = r.studentId.

C — A JOIN must be followed by ON, not WHERE. WHERE is saved for filtering the joined rows. The line should be ON p.productId = o.productId.

D — Text values in a WHERE condition need quotes. Without them SQL reads Maths as a column name. It should be WHERE s.name = 'Maths'.

B — The query uses the alias r in SELECT, ON, WHERE and ORDER BY, but the alias was never given to results. It should be FROM students s JOIN results r.

A — The fields in a SELECT list must be separated by commas. It should be SELECT s.firstName, r.mark.