A games shop wants every racing game, cheapest first. Instead of reading the whole table, you write one short SQL query and the database returns exactly the rows and columns you asked for, in the order you asked for them.
SQL (structured query language) is used to search a database. SELECT lists the fields to show. FROM names the table. WHERE gives a condition, so only the records that meet it are shown. ORDER BY sorts the results by a field, ASC (ascending: A to Z, smallest first) or DESC (descending: Z to A, largest first). To find the output, check every record against the WHERE condition, keep the matching ones, show only the SELECTed fields, then sort.
In the lab, build a query on the Books table: tick fields for SELECT and run it. Then add a condition and a sort and watch the result change. In this lesson every query uses the table GAMES below.
| GameID | Title | Genre | Price | Year | Online |
|---|---|---|---|---|---|
| G01 | Star Racer | Racing | 25.00 | 2021 | TRUE |
| G02 | Castle Quest | Adventure | 40.00 | 2019 | FALSE |
| G03 | Puzzle Pop | Puzzle | 10.00 | 2023 | TRUE |
| G04 | Speed Kings | Racing | 35.00 | 2022 | TRUE |
| G05 | Dragon Isle | Adventure | 30.00 | 2020 | FALSE |
| G06 | Block Town | Puzzle | 15.00 | 2018 | FALSE |
SELECT Title, Price FROM Games;
SELECT names fields, not records. "SELECT Racing" is wrong: the word Racing is a value in the Genre field, so it belongs in a WHERE condition.
WHERE compares a field with a value. Only records for which the condition is true appear in the output. Text values go in quotes; numbers and TRUE/FALSE do not.
| Operator | = | < | > | <= | >= | <> |
|---|---|---|---|---|---|---|
| Meaning | equal to | less than | greater than | less than or equal to | greater than or equal to | not equal to |
SELECT Title, Price FROM Games WHERE Genre = 'Racing';
SELECT Title FROM Games WHERE Price >= 30;
SELECT Title, Genre FROM Games WHERE Online = TRUE;Output: Star Racer Racing; Puzzle Pop Puzzle; Speed Kings Racing
Read < and <= carefully. WHERE Price > 30 would leave out Dragon Isle at exactly 30.00; WHERE Price >= 30 includes it.
Four quick checks. Each correct answer earns XP and records this skill on your profile.
Which part of an SQL query decides which records appear in the output?
In the lab, the Books table is sorted with ORDER BY Year DESC. Which book is listed first?
How many records does SELECT Title FROM Games WHERE Price >= 30; return from the GAMES table?
Which keyword sorts the output from largest to smallest?
ORDER BY comes last. It sorts the output by one field, ASC (ascending) or DESC (descending). Try the lab's "what will it output?" questions: write your answer down before you reveal it.
SELECT Title, Year FROM Games WHERE Price < 30 ORDER BY Year ASC;
The keywords must be in this order: SELECT, FROM, WHERE, ORDER BY. And ORDER BY Price DESC sorts the output, it does not choose which records appear.
Unlocks once the four checks above are done. Worth more XP, written to IGCSE Paper 1 standard.
What is the output of SELECT Title FROM Games WHERE Genre = 'Adventure' ORDER BY Year ASC; on the GAMES table?
Which query shows the title of every game that costs less than 20.00?
SELECT Title FROM Games WHERE Year <> 2021 ORDER BY Price DESC; Which title is listed last?
A query ends ORDER BY Surname; with no ASC or DESC. How is the output sorted?
This skill is now recorded on your profile. Keep the chain going.