All subjects · Information Technology · WIT11 2 hour written exam on IT systems, networks and data
SQL queries: SELECT, WHERE, wildcards, ORDER BY, GROUP BY, COUNT and subqueries
Edexcel IAL Information Technology · WIT11 2 hour written exam on IT systems, networks and data
Spec points 5.3.1 and 5.3.2 (a), (e) and (f): how and why SQL is used, queries and subqueries, the wildcards % and _, and grouping, ordering and counting. Every result in this lesson comes from running the SQL on the tables shown.
Why SQL
SQL (Structured Query Language) is the standard language for working with relational databases. It is used to define structures (create tables), manipulate data (insert, update, delete) and query data (select records that match criteria). It is declarative (you say what you want, not how to find it), works across many database systems, and can be embedded in programs and web server scripts.
The tables
| bookID | title | authorLast | published | inStock | price |
|---|---|---|---|---|---|
| 1 | The Silent Sea | Okafor | 1962-05-10 | 4 | 8.99 |
| 2 | River Song | Haddad | 1971-03-02 | 0 | 6.50 |
| 3 | The Glass Tower | Okafor | 1988-09-15 | 7 | 9.99 |
| 4 | Northern Lights Again | Lindqvist | 2004-11-01 | 2 | 12.00 |
| 5 | Paper Moons | Haddad | 1965-01-01 | 5 | 7.25 |
| 6 | The Long Road | Mbeki | 1959-07-20 | 1 | 5.99 |
| 7 | Stone Garden | Lindqvist | 2015-06-30 | 9 | 14.50 |
| 8 | Quiet Hours | Okafor | 1999-02-14 | 3 | 10.00 |
tbl_book
Query patterns
SELECT title, price FROM tbl_book WHERE inStock > 0 ORDER BY price DESC;- SELECT fields (or * for all) FROM table WHERE condition. Combine conditions with AND, OR, NOT. Comparisons: =, <>, <, >, <=, >=.
- Text and dates go in quotation marks: WHERE published >= '1965-01-01' (year, month and day, as Pearson's June 2024 mark scheme required). Field names do not.
- ORDER BY field ASC (default) or DESC.
- COUNT(*) counts matching records: SELECT COUNT(*) FROM tbl_book WHERE published >= '1965-01-01' gives 6. The brackets are required.
- GROUP BY groups records and applies a function to each group: SELECT authorLast, COUNT(*) FROM tbl_book GROUP BY authorLast gives Haddad 2, Lindqvist 2, Mbeki 1, Okafor 3.
- Wildcards with LIKE: % matches any number of characters, _ matches exactly one. title LIKE 'The%' finds titles starting 'The' (3 here). Do not use LIKE to compare dates.
- Subquery: a query inside another. SELECT title FROM tbl_book WHERE price > (SELECT AVG(price) FROM tbl_book) finds books dearer than the average price (9.4025), giving 4 books.
Worked example
Write a query to list the titles and prices of books by Okafor that are in stock, cheapest first, and state how many rows it returns.
- SELECT title, price
- FROM tbl_book
- WHERE authorLast = 'Okafor' AND inStock > 0
- ORDER BY price ASC;
Answer: Returns 3 rows.
Check yourself
- Using tbl_book, how many records does SELECT COUNT(*) FROM tbl_book WHERE published >= '1965-01-01'; return as its count?
Show answer
6. Books published on or after 1 January 1965: 6.
- How many records does SELECT * FROM tbl_book WHERE title LIKE 'The%'; return?
Show answer
3. Titles beginning 'The': 3.
- How many records does SELECT title FROM tbl_book WHERE price < 8 AND inStock > 0; return?
Show answer
2. Books under 8.00 that are in stock: 2.
- Which title is in the first row of SELECT title FROM tbl_book ORDER BY price DESC; ?
- The Silent Sea
- The Long Road
- The Glass Tower
- Stone Garden
Show answer
Stone Garden. DESC puts the highest price first: Stone Garden.
- How many rows does SELECT title FROM tbl_book WHERE price > (SELECT AVG(price) FROM tbl_book); return?
Show answer
4. The average price is 9.4025; 4 books cost more.
- What does the _ wildcard match in a LIKE pattern?
- Any number of characters
- Only digits
- The end of the field
- Exactly one character
Show answer
Exactly one character. For example LIKE '_kafor' matches 'Okafor' (3 books here).
- Which statement returns the number of books by each author?
- SELECT COUNT authorLast FROM tbl_book;
- SELECT authorLast, COUNT(*) FROM tbl_book GROUP BY authorLast;
- SELECT authorLast FROM tbl_book ORDER BY COUNT;
- SELECT * FROM tbl_book WHERE COUNT > 1;
Show answer
SELECT authorLast, COUNT(*) FROM tbl_book GROUP BY authorLast;. GROUP BY makes one group per author and COUNT(*) counts each.
- Which WHERE clause correctly finds books published in 1965 or later?
- WHERE published >= '1965-01-01'
- WHERE published LIKE '1965%'
- WHERE published = 1965
- WHERE 'published' >= 1965
Show answer
WHERE published >= '1965-01-01'. Compare full dates in quotes; LIKE earned no credit for dates in June 2024, and field names do not go in quotes.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (2018), Unit 1
- Pearson Edexcel IAL Information Technology WIT11/01 question paper, June 2024
- Pearson Edexcel IAL Information Technology WIT11/01 mark scheme, June 2024
- Pearson Edexcel IAL Information Technology WIT11/01 examiner report, June 2024
Last updated 8 November 2026. Spotted a mistake? Tell us.