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.

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

SELECT, FROM, WHERE, ORDER BY

SELECT Model, BikeType, DailyRate
FROM tblBike
WHERE DailyRate >= 18
ORDER BY DailyRate DESC

Result (4 rows):

Model     | BikeType | DailyRate
----------+----------+----------
Volt E2   | Electric | 35.00
Ridge Pro | Mountain | 22.00
Speedster | Road     | 20.00
Trail 500 | Mountain | 18.00

In 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 HireDate

Result (2 rows):

HireID | HireDate   | Days
-------+------------+-----
101    | 2026-03-02 | 2
103    | 2026-03-14 | 3

In 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 Surname

Result (3 rows):

FirstName | Surname  | Email
----------+----------+-------------------
Farah     | Ahmed    | NULL
Dana      | Kowalski | dana.k@example.com
Ben       | Okafor   | NULL

In 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:

Worked example

List the model and rate of Hybrid or Electric bikes costing under 30 per day, cheapest first.

  1. ```sql SELECT Model, DailyRate FROM tblBike WHERE BikeType IN ('Hybrid', 'Electric') AND DailyRate < 30 ORDER BY DailyRate ```
  2. Result (2 rows): ``` Model | DailyRate -----------+---------- Commuter | 10.00 City Glide | 12.50 ```
  3. 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.

Key idea: SELECT fields FROM table WHERE criteria ORDER BY field ASC|DESC. BETWEEN includes ends; AND on one grid row, OR on separate rows; LIKE with % and _ (Access grid: * and ?); IS NULL for empty fields; read 'over' (>) and 'at least' (>=) carefully; a filter gives a quick temporary view without changing the design; screen print full criteria and results.

Check yourself

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

Practise with instant feedback

Sources

Last updated 8 November 2026. Spotted a mistake? Tell us.