GCSE SQL: queries and worked examples

These invented titles and numbers are a practice dataset, not library records. Use this same table for every example.

A field is one category of data, such as Pages, shown as a column. A record is one complete row: book 101 has the title Orbit, genre SciFi and 180 pages. This table has four fields and five records. BookID is the primary key: its value uniquely identifies each book.

A relational database can contain related tables. We use only one table here so you can concentrate on reading queries; this is not a complete guide to relational database design.

SELECT Title, Genre
FROM Books;

SELECT Title, Genre requests two columns, in that order. FROM Books names the source table. There is no filter, so all five records contribute to the result. The commas separate field names.

Without ORDER BY, SQL does not guarantee the order of the returned rows. This table shows the rows in one possible order; the selected values are what matter. The query reads data without changing the Books table.

SELECT Title, Pages
FROM Books
WHERE Pages >= 180;

Read the condition as “Pages is greater than or equal to 180”. Orbit and Forest are included because equality counts; Signal is included because 240 is greater. Tide and Comet are excluded.

WHERE selects which records qualify; SELECT still decides which fields appear. A result does not automatically include every field used in its condition. Again, row order is not guaranteed.

Put text values in straight single quotation marks, such as 'SciFi'. Numeric values such as 180 do not need quotation marks. Keep the field names exactly as supplied in the question. These examples use SQL = for equality, rather than Python ==.

SELECT Title, Pages
FROM Books
WHERE Pages >= 180
ORDER BY Title ASC;

This keeps the same three books as example 2 and sorts them by title from A to Z. ASC means ascending; DESC means descending. Sorting changes the order of the result, not which books satisfy the condition.

Write the clauses in the order shown: SELECT, FROM, WHERE, then ORDER BY. For OCR J277, this sorting example extends beyond the SQL keywords listed in the specification; for AQA 8525, ORDER BY is listed content.

SELECT Title, Pages
FROM Books
WHERE Genre = 'SciFi'
  AND Pages >= 180;

A book must be SciFi and have at least 180 pages. Orbit and Signal satisfy both conditions. Comet satisfies the genre condition but is too short. Forest has enough pages but the wrong genre. The displayed row order is one possible order.

SELECT Title, Pages
FROM Books
WHERE Genre = 'Adventure'
  OR Pages < 100;

A book qualifies if it is Adventure or has fewer than 100 pages, or both. Tide and Forest qualify by genre; Comet qualifies by length. Each qualifying record appears once. The displayed row order is one possible order.

Write a complete comparison on each side of AND or OR. For example, use WHERE Title = 'Orbit' followed by OR Title = 'Comet'. Repeat Title = for each comparison.

SELECT Title, Pages
FROM Books
WHERE Genre = 'SciFi'
  AND Pages <= 180
ORDER BY Pages DESC;

Orbit and Comet qualify. Signal is SciFi but exceeds 180 pages. Tide and Forest fail the genre condition. DESC puts 180 before 90. Changing <= to < would incorrectly remove Orbit: “no more than” includes the boundary value.

Compare your answer in three ways: the selected columns, the surviving records and their requested order. A query can run successfully and still answer the wrong question.