All subjects · Information Technology · WIT14 3 hour practical exam on database solutions
Select queries: criteria, wildcards and sorting
Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions
Retrieving exactly the right records is at the heart of exam question 2 (specification 19.1.3: queries, filters and reports). SQL syntax was introduced in Unit 1; here the focus is building queries that meet a scenario exactly, in a design grid or in SQL. The logic is identical: choose fields, set criteria, sort.
SELECT, FROM, WHERE, ORDER BY
- SELECT lists the fields to show (* means all).
- FROM names the table.
- WHERE sets criteria: = <> < <= > >=, combined with AND / OR / NOT.
- ORDER BY sorts: ASC (lowest first, the default) or DESC.
SELECT Model, BikeType, DailyRate
FROM tblBike
WHERE DailyRate >= 18
ORDER BY DailyRate DESCResult (4 rows):
Model | BikeType | DailyRate
----------+----------+----------
Volt E2 | Electric | 35.00
Ridge Pro | Mountain | 22.00
Speedster | Road | 20.00
Trail 500 | Mountain | 18.00In an Access design grid: tick Show for each field, type >=18 in the Criteria row under DailyRate, and choose Descending in the Sort row.
Ranges, AND and OR
BETWEEN includes both ends. AND needs every condition true; OR needs any one:
SELECT HireID, HireDate, Days
FROM tblHire
WHERE HireDate BETWEEN '2026-03-01' AND '2026-03-31' AND Days > 1
ORDER BY HireDateResult (2 rows):
HireID | HireDate | Days
-------+------------+-----
101 | 2026-03-02 | 2
103 | 2026-03-14 | 3In the design grid, criteria on the same Criteria row are joined with AND; criteria on different rows (the 'or' row) are joined with OR. Read thresholds carefully: 'over 150' means >150, 'at least 1000' means >=1000. The June 2024 and 2025 examiner reports both note marks lost for using the wrong one.
Wildcards and missing values
LIKE matches patterns. In standard SQL, % matches any number of characters and _ matches exactly one. IS NULL finds empty fields (= NULL never matches):
SELECT FirstName, Surname, Email
FROM tblCustomer
WHERE Surname LIKE 'K%' OR Email IS NULL
ORDER BY SurnameResult (3 rows):
FirstName | Surname | Email
----------+----------+-------------------
Farah | Ahmed | NULL
Dana | Kowalski | dana.k@example.com
Ben | Okafor | NULLIn Access's own query grid the wildcards are * and ? instead, for example Like "K*", and dates are written between # signs. In the design grid Access reads a typed date in the computer's regional format, so with UK settings Between #01/03/2026# And #31/03/2026# means March (the form Pearson's mark schemes use), but in SQL view and VBA a # date is always read month first, so #01/03/2026# there means 3 January. The year-first form #2026-03-01# is read the same way everywhere.
Filters, and evidence the examiner can read
Specification 19.1.3 also lists filters. A filter limits the records shown in an open table, query, form or report without changing its design: Filter by Selection (records matching the value you click), Filter by Form (type criteria into a blank copy of the form), the common filters on a column's drop-down, and Advanced Filter/Sort for your own criteria. Toggle Filter switches it on and off. A filter suits a quick one-off look; a saved query suits a task that is repeated or feeds a report.
For a query task, the mark scheme looks for each field, each criterion and the sort, and the correct results. So:
- Screen print the design view (or SQL) with every criterion fully visible: widen columns so nothing is truncated.
- Screen print the results (datasheet view).
- Check the results against the data by hand for a couple of records.
Worked example
List the model and rate of Hybrid or Electric bikes costing under 30 per day, cheapest first.
- ```sql SELECT Model, DailyRate FROM tblBike WHERE BikeType IN ('Hybrid', 'Electric') AND DailyRate < 30 ORDER BY DailyRate ```
- Result (2 rows): ``` Model | DailyRate -----------+---------- Commuter | 10.00 City Glide | 12.50 ```
- IN is a neat way to write BikeType = 'Hybrid' OR BikeType = 'Electric'. Without brackets around an OR, AND is applied first, which is a common logic error.
Answer: Two hybrids qualify; the Volt E2 is excluded by the price.
Check yourself
- How many rows does this query return on the Harbour Bike Hire database?
```sql
SELECT Model, BikeType, DailyRate
FROM tblBike
WHERE DailyRate >= 18
ORDER BY DailyRate DESC
```
Show answer
4. 4 bikes cost at least 18: Volt E2, Electric, 35.00; Ridge Pro, Mountain, 22.00; Speedster, Road, 20.00; Trail 500, Mountain, 18.00.
- How many rows does this query return on the Harbour Bike Hire database?
```sql
SELECT HireID, HireDate, Days
FROM tblHire
WHERE HireDate BETWEEN '2026-03-01' AND '2026-03-31' AND Days > 1
ORDER BY HireDate
```
Show answer
2. March hires longer than one day: 101, 2026-03-02, 2; 103, 2026-03-14, 3. That is 2 rows.
- How many rows does this query return on the Harbour Bike Hire database?
```sql
SELECT FirstName, Surname, Email
FROM tblCustomer
WHERE Surname LIKE 'K%' OR Email IS NULL
ORDER BY Surname
```
Show answer
3. Surnames starting with K, or no email: Farah, Ahmed, NULL; Dana, Kowalski, dana.k@example.com; Ben, Okafor, NULL. That is 3 rows.
- Which result does this query give on the Harbour Bike Hire database?
```sql
SELECT Model FROM tblBike WHERE BikeType = 'Mountain' ORDER BY DailyRate DESC
```
- Trail 500; Ridge Pro
- Ridge Pro; Trail 500
- Ridge Pro
- Trail 500
Show answer
Ridge Pro; Trail 500. Ridge Pro (22.00) comes before Trail 500 (18.00) in descending order: Ridge Pro; Trail 500.
- A receptionist wants a quick, one-off look at only the Portview customers in the open customer table, without creating a new object. What should they use?
- A new make-table query
- A delete query
- An input mask on Town
- A filter, such as Filter by Selection on a Portview value
Show answer
A filter, such as Filter by Selection on a Portview value. A filter limits the records shown without changing the design or creating an object; a saved query suits repeated tasks.
- A task asks for works whose price difference is over 150. Which criterion is correct?
- >=150
- >150
- <150
- =150
Show answer
>150. 'Over' excludes 150 itself. The June 2024 mark scheme accepted >150 (or >=151), and the examiner report notes candidates losing the mark by using the wrong comparison operator.
- Which WHERE clause finds customers with no email address?
- WHERE Email = NULL
- WHERE Email = ''
- WHERE Email LIKE '%'
- WHERE Email IS NULL
Show answer
WHERE Email IS NULL. NULL is not a value, so = NULL never matches; use IS NULL.
- How many rows does this query return on the Harbour Bike Hire database?
```sql
SELECT * FROM tblCustomer WHERE Email LIKE '%.%@%'
```
Show answer
4. Emails with a dot before the @: 1, Amira, Hassan, Portview, amira.h@example.com; 3, Chen, Li, Portview, chen.li@example.com; 4, Dana, Kowalski, Eastfield, dana.k@example.com; 5, Ellis, Brown, Millbrook, ellis.b@example.com. That is 4 rows.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (Issue 2, February 2021): Unit 4, Topics 17 to 19
- Pearson: WIT14/01 examiner report, June 2024
- Pearson: WIT14/01 examiner report, June 2025
- Pearson: IAL Information Technology scheme of work for Unit 4
Last updated 8 November 2026. Spotted a mistake? Tell us.