You're connected to a database, and each question asks you to find something out by writing an SQL query. Your queries run on your own copy of the database, in your browser, so try anything: you can't break it.
- SELECT the columns you want, FROM the table, WHERE a condition is true, then ORDER BY a column, ASC (smallest or earliest first) or DESC (largest or latest first).
- Join conditions with AND (both must be true) or OR (either can be). Text values go in quotation marks, spelt exactly as in the table:
WHERE Artist = 'Queen'.
- COUNT(*) counts the rows, and SUM(column) adds up a column.
Some questions show a query and ask what it outputs instead. Questions marked AS go beyond IGCSE: AVG, MIN, MAX, LIKE (with % standing for any characters) and GROUP BY.
2+ tables. These databases are normalised (third normal form): each table holds one kind of thing, and a foreign key in one table holds the primary key of a row in another. To answer a question about more than one table, join them where the keys match. There are two ways to write it, and both are right:
- with INNER JOIN:
SELECT Courses.Name FROM Courses INNER JOIN Teachers ON Courses.TeacherID = Teachers.TeacherID WHERE Teachers.LastName = 'Lee';
- or with the join condition in WHERE:
SELECT Courses.Name FROM Courses, Teachers WHERE Courses.TeacherID = Teachers.TeacherID AND Teachers.LastName = 'Lee';
Write a column as Table.Column when it's in more than one of the tables. A one-to-many relationship needs one join; a many-to-many one goes through a link table (such as Enrolments, between Students and Courses), so it needs two. Leave out a join condition and every row of one table is paired with every row of the other. When you get one right, both ways of writing it are shown.
Checking. Your query is run and its result is compared with the right answer's, so any query that gives the right rows is right, whatever its column names. When a question asks for an order, your rows must be in that order too. If it's not right, you're told what's wrong with it (never the answer), and you can try again. Stuck? Hint says which tables, columns and parts of SQL to use, and halves the question's mark; Show me shows a query that works, and the question scores nothing. Explore, in the title bar, lets you run any query on the database, unchecked.
Mysteries. A mystery is a case to solve with several tables of evidence: join them (INNER JOIN), count and add them up (GROUP BY, COUNT, SUM), and keep your own list of suspects, with CREATE TABLE, ALTER TABLE, INSERT INTO, UPDATE and DELETE FROM, as in Cambridge AS 9618. The evidence can't be changed, but your own tables can. Each step is checked against your database; everything you change is kept, so you can stop and carry on, or start the case again.
Signed in, your queries are saved: come back and carry on. Each question is worth a mark when you get it right on your own, half a mark after a hint, and none if you're shown the answer. Questions your teacher has set are marked, and come up first.
Objective: read, understand and complete SQL scripts to query data stored in a single database table (Cambridge IGCSE Computer Science 0478, 9.4), and write SQL to query data, including from more than one table with INNER JOIN (9618 8.3).
Where this fits
- AQA: AQA A Level Computer Science (7517)
- Cambridge: Cambridge IGCSE Computer Science (0478); Cambridge A Level Computer Science (9618); Cambridge AS Level Computer Science (9618) Goes beyond Cambridge IGCSE Computer Science (0478): Joins, GROUP BY, LIKE and the AS questions go beyond 0478, which uses one table; the IGCSE questions fit it.
- IB: IB Computer Science HL; IB Computer Science SL
- NCEA Level 2 Digital Technologies: 91892 Use advanced techniques to develop a database; 91892 Use advanced techniques to develop a database
- NCEA Level 3 Digital Technologies: 91902 Use complex techniques to develop a database; 91902 Use complex techniques to develop a database
- Pearson Edexcel International: Edexcel International A Level Information Technology
- USDP: USDP Computer Science
