Spec & Goals 3 min
AQA Spec 3.7.2 — Combining SELECT, FROM, WHERE, LIKE, AND/OR and ORDER BY
By the end of this lesson you can:
- Build a full query combining filtering, pattern matching and sorting.
- Turn a plain-English request into correct SQL.
- Trace a query against a table and state its exact result.
Warm-Up 5 min
You now know every clause AQA needs. This lesson puts them together — the skill the exam really tests: turning a question into a working query.
Quick starter
Recall the clause order. Fill the gaps: SELECT … ___ … ___ … ORDER BY …
Reveal the idea
SELECT … FROM … WHERE … ORDER BY … — always in that order.
Key Concept — the full toolkit 14 min
| Clause | Job |
|---|---|
SELECT | which fields (columns) — or * for all. |
FROM | which table. |
WHERE | which rows (condition); combine with AND/OR; pattern-match with LIKE. |
ORDER BY | sort the results (ASC/DESC). |
SELECT fields FROM table WHERE condition (AND/OR ... / LIKE ...) ORDER BY field ASC|DESC;
A reading recipe
- What columns? →
SELECT - From which table? →
FROM - Which rows / what conditions? →
WHERE(+AND/OR/LIKE) - Any sorting? →
ORDER BY
Our Student table
| StudentID | Name | Form | Town | Mark |
|---|---|---|---|---|
| 101 | Aisyah | 10A | Ipoh | 78 |
| 102 | Wei Jie | 10B | Penang | 65 |
| 103 | Priya | 10A | Ipoh | 91 |
| 104 | Arjun | 11C | Johor Bahru | 54 |
| 105 | Mei Ling | 11C | Penang | 88 |
| 106 | Hafiz | 10B | Kuala Lumpur | 72 |
Worked Example — English → SQL → result 12 min
Request: "List the name and mark of every year-10 student who scored at least 70, highest mark first."
- Columns:
Name, Mark - Table:
Student - Rows: year-10 forms (
Form LIKE '10_') andMark >= 70 - Sort:
Mark DESC
SELECT Name, Mark FROM Student WHERE Form LIKE '10_' AND Mark >= 70 ORDER BY Mark DESC;
Result:
| Name | Mark |
|---|---|
| Priya | 91 |
| Aisyah | 78 |
| Hafiz | 72 |
(Wei Jie is 10B but scored 65, so excluded; the year-11 students are excluded by the form pattern.)
Try It Yourself 12 min
Goal: "List all students from Penang." Write the query.
Goal: "Show the names of students whose town starts with I, sorted A–Z." Write the query and state the result.
Goal: "Show name and mark of students in 10A or 11C who scored over 80, highest first." Write the query and state the result.
📝 Exam Practice 10 min
Write an SQL query to display the Name and Town of every student from Penang, sorted by name alphabetically.
Mark scheme
SELECT Name, Town(1).FROM Student(1).WHERE Town = 'Penang'(1).ORDER BY Name ASC;(1).
State the result of:
SELECT Name FROM Student WHERE Town = 'Ipoh' AND Mark > 80;
Mark scheme
- Priya (1).
- Only Priya — Aisyah is from Ipoh but scored 78 (1).
Write a query for the names of students whose name contains "a", sorted alphabetically.
Mark scheme
SELECT Name FROM Student(1).WHERE Name LIKE '%a%'(1).ORDER BY Name;(1).
Recap & Key Terms 3 min
Build queries with the recipe: columns → table → conditions → sort, written as SELECT … FROM … WHERE … ORDER BY …;. Combine conditions with AND/OR and patterns with LIKE. Quote text, keep clause order, check wildcard placement.
- Query
- An SQL statement that retrieves data matching given criteria.
- Clause order
- SELECT → FROM → WHERE → ORDER BY.
- Combining conditions
- Use AND / OR in WHERE, and LIKE with % / _ for patterns.
Homework 1 min
Task (≤ 15 min): Write SQL for each request on the Student table: (a) all fields of students in 11C; (b) names of students not from Penang, sorted A–Z; (c) name and mark of students scoring 70–90, lowest first.
Model answer
-- (a) SELECT * FROM Student WHERE Form = '11C'; -- (b) SELECT Name FROM Student WHERE Town <> 'Penang' ORDER BY Name; -- (c) SELECT Name, Mark FROM Student WHERE Mark >= 70 AND Mark <= 90 ORDER BY Mark ASC;
Award marks for: correct (a) WHERE (1); correct (b) <> + sort (1); correct (c) range + sort (1).