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.

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

Practise this lesson

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

bookIDtitleauthorLastpublishedinStockprice
1The Silent SeaOkafor1962-05-1048.99
2River SongHaddad1971-03-0206.50
3The Glass TowerOkafor1988-09-1579.99
4Northern Lights AgainLindqvist2004-11-01212.00
5Paper MoonsHaddad1965-01-0157.25
6The Long RoadMbeki1959-07-2015.99
7Stone GardenLindqvist2015-06-30914.50
8Quiet HoursOkafor1999-02-14310.00

tbl_book

Query patterns

SELECT title, price FROM tbl_book WHERE inStock > 0 ORDER BY price DESC;

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.

  1. SELECT title, price
  2. FROM tbl_book
  3. WHERE authorLast = 'Okafor' AND inStock > 0
  4. ORDER BY price ASC;

Answer: Returns 3 rows.

Key idea: SELECT fields FROM table WHERE condition ORDER BY field ASC/DESC. COUNT(*) counts; GROUP BY groups. LIKE with % (any characters) and _ (one character). Text and dates in quotes. A subquery is a SELECT inside another query.

Check yourself

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

  2. How many records does SELECT * FROM tbl_book WHERE title LIKE 'The%'; return?
    Show answer

    3. Titles beginning 'The': 3.

  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.

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

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

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

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

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

Practise with instant feedback

Sources

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