QueryLab uses real SQLite with four fictional datasets. Source tables are on the left, your query or design in the centre, and results or challenge instructions on the right. Use Fields to inspect types and keys. Basic offers introductory scaffolds; Advanced adds more examples and the SQLite query plan. The engine supports the same SQL in both views.
Running SQL
Run one SELECT or WITH query at a time. Teaching data reset for every run and cannot be edited. The preview shows up to 200 rows. Queries run in a separate worker and stop after three seconds of execution; Cancel query is available while loading or running. If the engine cannot load, check your connection and try again. Ctrl/⌘ + Enter runs the query. SQL syntax errors appear in Results.
SQLite LIKE is case-insensitive for ordinary ASCII letters by default. NULL is missing/unknown: compare with IS NULL, not = NULL. JOIN may duplicate rows when several children match one parent. COUNT(*) counts rows; COUNT(column) skips NULL. Without ORDER BY, row order is not guaranteed. The plan panel is SQLite’s physical query strategy.
Relationships
Choose a child field and a referenced parent primary key, then Test integrity. The inspector checks type compatibility and orphan values in the current sample. Declared relationships are schema rules; sample matches alone do not prove that a relationship is intended. Several child rows may share one parent key. The schema permits optional NULL keys only where indicated.
Normalisation
The case study has declared dependencies StudentID → StudentName, ClubID → ClubName/StaffID and StaffID → StaffName. Each student can join each club once. UNF contains a repeating group. 1NF makes the fields atomic, with the membership key (StudentID, ClubID). 2NF separates dependencies on only part of this composite key. 3NF also removes the staff-name transitive dependency. Assign non-key fields to the proposed tables and inspect their projected rows. Duplicate primary keys identify an invalid grouping.
Challenges and saving
All 24 challenges are independent: 16 SQL tasks, four relationship tasks and four normalisation tasks. Predict first, then repair/modify/write a query or build the requested design. SQL answers are compared with reference results on the displayed and a second fictional dataset, preserving duplicate counts and any requested ordering. Equivalent SQL is accepted; output aliases can differ. Worked help records a helped completion. Wrong predictions and answer checks count towards the attempt. Reattempts keep the best independent completion where possible.
Practice records and projects stay on this browser and do not award course XP or appear on teacher dashboards. Files offers a device save, JSON projects and downloadable SQL. Imports validate the project before replacing work. Focus Mode hides outer navigation; Escape exits. Narrow screens stack panels, and table data scroll within their panels.
Teacher reference: all challenge solutions
1 · Choose the columns
Return StudentName only for every student.
SELECT StudentName FROM Students;
2 · The Year 8 group
Return StudentID and StudentName for students in YearGroup 8.
SELECT StudentID, StudentName FROM Students WHERE YearGroup = 8;
3 · Longest books first
Return Title and Pages for all books, ordered by Pages descending, then Title ascending to break ties.
SELECT Title, Pages FROM Books ORDER BY Pages DESC, Title ASC;
4 · Still on loan
Return LoanID and BookID for loans with no ReturnedOn value.
SELECT LoanID, BookID FROM Loans WHERE ReturnedOn IS NULL;
5 · Starts with The
Return BookID and Title for titles beginning with 'The '.
SELECT BookID, Title FROM Books WHERE Title LIKE 'The %';
6 · One of each genre
Return each distinct Genre once.
SELECT DISTINCT Genre FROM Books;
7 · Within a range
Return BookID and Pages for books with Pages between 120 and 240 inclusive.
SELECT BookID, Pages FROM Books WHERE Pages BETWEEN 120 AND 240;
8 · Count the loans
Return one column containing the total number of loan rows.
SELECT COUNT(*) FROM Loans;
9 · Who borrowed what?
Return StudentName and Title for each loan, including returned loans and repeated titles.
SELECT s.StudentName,b.Title FROM Students s JOIN Loans l ON l.StudentID=s.StudentID JOIN Books b ON b.BookID=l.BookID;
10 · Keep every club
Return ClubName and StaffName for every club, including clubs without an assigned staff member.
SELECT c.ClubName,s.StaffName FROM Clubs c LEFT JOIN Staff s ON c.StaffID=s.StaffID;
11 · Total points per player
Return PlayerName and the total Points for each player who has scores.
SELECT p.PlayerName,SUM(s.Points) FROM Players p JOIN Scores s ON s.PlayerID=p.PlayerID GROUP BY p.PlayerID,p.PlayerName;
12 · Repeat players
Return PlayerID and number of scores only for players with at least two score rows.
SELECT PlayerID,COUNT(*) FROM Scores GROUP BY PlayerID HAVING COUNT(*)>=2;
13 · Tickets for each film
Return FilmTitle and total Tickets for each film with bookings. Count tickets, not bookings.
SELECT f.FilmTitle,SUM(b.Tickets) FROM Films f JOIN Showings s ON s.FilmID=f.FilmID JOIN Bookings b ON b.ShowingID=s.ShowingID GROUP BY f.FilmID,f.FilmTitle;
14 · Above the average
Return ScoreID and Points for scores strictly above the average Points across all scores.
SELECT ScoreID,Points FROM Scores WHERE Points > (SELECT AVG(Points) FROM Scores);
15 · Combine conditions
Return FilmID and FilmTitle for films that are PG or U, and have Minutes below 120.
SELECT FilmID,FilmTitle FROM Films WHERE (Certificate='PG' OR Certificate='U') AND Minutes<120;
16 · No loans yet
Return StudentID and StudentName for students who have no loan rows.
SELECT s.StudentID,s.StudentName FROM Students s WHERE NOT EXISTS (SELECT 1 FROM Loans l WHERE l.StudentID=s.StudentID);
17 · Link a borrower
Build the declared relationship from Loans.StudentID to Students. Choose the referenced key, then check the relationship.
Loans.StudentID → Students.StudentID
18 · Link a borrowed book
Build the declared relationship from Loans.BookID to Books. Choose the referenced key, then check the relationship.
Loans.BookID → Books.BookID
19 · Link a club enrolment
Build the declared relationship from Enrolments.ClubID to Clubs. Choose the referenced key, then check the relationship.
Enrolments.ClubID → Clubs.ClubID
20 · Link a cinema booking
Build the declared relationship from Bookings.ShowingID to Showings. Choose the referenced key, then check the relationship.
Bookings.ShowingID → Showings.ShowingID
21 · Split the repeating group
Choose the change that turns the repeating ClubIDs group into atomic rows.
One row per student–club pair
22 · Choose the composite key
Select the declared composite key of the atomic membership table. A student may join each club once.
StudentID, ClubID
23 · Remove partial dependencies
Assign the four non-key fields to Students, Clubs or Enrolments to produce 2NF. Leave the staff-name transitive dependency for the next stage.
StudentName → Students; ClubName, StaffID, StaffName → Clubs
24 · Remove the transitive dependency
Assign the four fields to produce the 3NF model. StaffID is already the fixed primary key of Staff; keep its foreign-key copy in Clubs.
StudentName → Students; ClubName, StaffID → Clubs; StaffName → Staff
Engine and teaching boundaries
This version focuses on querying and relational design. It does not edit database records or create a production database. sql.js runs SQLite locally in the browser. Normalisation exercises use the declared case-study rules; they do not infer all possible dependencies from a few sample rows.