Querying and Filtering Data with SQL

AQA 4.10.2 — Querying and filtering with SQL

Retrieval: the previous lesson established what tables, rows, columns and keys are. This lesson writes genuine SQL — SQL, the language used to actually retrieve data from a relational database, has not been taught before this point in the course at all.

Key vocabulary

• SELECT — specifies which columns to retrieve.
• FROM — specifies which table to retrieve them from.
• WHERE — filters rows, keeping only those matching a condition.
• ORDER BY — sorts the result rows (ASC ascending, the default; DESC descending).
• DISTINCT — removes duplicate rows from the result.

Understand — SELECT, the core of every query

SELECT name, price FROM products;

Reads as: "retrieve the name and price columns from every row of products."

Exam-style worked example 1

Question: Predict the result of the following query, then state how many rows it returns.

SELECT name, price FROM products WHERE category = 'Accessories' ORDER BY price ASC;

Model answer: Filtering products to category = 'Accessories' keeps: Wireless Mouse (14.99), Mechanical Keyboard (59.99), USB-C Hub (24.50), Laptop Stand (19.99) — 4 rows. Sorted ascending by price: Wireless Mouse (14.99), Laptop Stand (19.99), USB-C Hub (24.50), Mechanical Keyboard (59.99). 4 rows.

See it — comparison operators and logical combination

SELECT name, price, stock FROM products
WHERE price < 30 AND stock > 0;

AND requires both conditions to be true for a row to be kept; OR requires at least one; NOT inverts a condition.

Exam-style worked example 2

Question: Predict the result of:

SELECT name FROM products WHERE stock = 0 OR price > 100;

Model answer: Checking every product: Webcam HD has stock = 0 → included. 27" Monitor has price = 189.00 > 100 → included. No other product satisfies either condition. Result: 2 rows — "27" Monitor" and "Webcam HD" (the exact row order is not guaranteed without an ORDER BY clause) — note OR includes a row if EITHER condition holds, not only when both do.

See it — pattern matching with LIKE, and BETWEEN/IN

SELECT name FROM customers WHERE name LIKE 'A%';        -- starts with 'A'
SELECT name FROM products WHERE price BETWEEN 15 AND 30; -- inclusive range
SELECT name FROM customers WHERE city IN ('London', 'Leeds');

% in a LIKE pattern matches any number of characters (including none); _ matches exactly one character. BETWEEN a AND b is inclusive of both a and b. IN (...) is a compact way of writing several OR-connected equality checks.

Exam-style worked example 3

Question: Predict the result of:

SELECT DISTINCT city FROM customers WHERE city LIKE '%n%';

Model answer: Checking each customer's city for containing the letter 'n' anywhere: London (yes), Manchester (yes), Birmingham (yes), Leeds (no), London (yes, repeated). Without DISTINCT this would return London, Manchester, Birmingham, London (4 rows, London repeated because two customers share that city). With DISTINCT, the repeat is removed: 3 distinct rows — "London", "Manchester", "Birmingham".

Debug it — diagnose, explain, fix, test, justify (incorrect WHERE logic)

A student intends "customers with more than 100 loyalty points, from either London or Manchester" and writes:

SELECT name FROM customers
WHERE loyalty_points > 100 AND city = 'London' OR city = 'Manchester';

This returns Tomasz Nowak (Manchester, 120 points) — correctly — but also returns every other Manchester customer regardless of their points, and could silently include a low-points Manchester customer if one existed.

1. Diagnose: without brackets, does SQL evaluate AND and OR with equal priority, or does one bind more tightly?
2. Explain: what does this query actually mean, read strictly by precedence (AND binds tighter than OR)?
3. Fix: add the grouping the student intended.
4. Test: confirm the fixed version excludes a hypothetical low-points Manchester customer that the original would have wrongly included.
5. Justify: explain why this is a logical error, not a syntax error — the original query runs perfectly validly, it simply answers a different question than intended.

(SQL evaluates AND before OR when no brackets are given, exactly like * before + in arithmetic - so this means "(loyalty_points > 100 AND city = 'London') OR city = 'Manchester'", which includes EVERY Manchester customer regardless of points, not just the ones with over 100. The fix adds explicit brackets: WHERE loyalty_points > 100 AND (city = 'London' OR city = 'Manchester'). This is a logical error because the original SQL is completely valid and runs without any error - it just doesn't mean what was intended, exactly the same class of mistake as the regex precedence error from Sequence 15.)

Common mistake

Forgetting that ORDER BY without DESC sorts ascending by default, and that string columns sort alphabetically (not by length or any other implicit rule) — ORDER BY name sorts "Amara" before "Priya" for exactly the same reason a dictionary does.

Check your understanding

Predict the result of the following query, explaining your reasoning: SELECT name FROM products WHERE category = 'Accessories' AND price < 20 ORDER BY name DESC; (3 marks)

(Filtering to Accessories under £20: Wireless Mouse (14.99) and Laptop Stand (19.99) qualify; Mechanical Keyboard (59.99) and USB-C Hub (24.50) are too expensive. Sorted by name descending (Z to A): "Wireless Mouse", "Laptop Stand".)

Challenge

Write a query that finds the names of every customer whose loyalty points are between 100 and 400 inclusive, sorted from highest points to lowest. Predict the result before running it in the SQL Lab, then verify.

Looking ahead: the next lesson combines data from multiple tables in a single query — joins — and summarises it with aggregate functions.