QueryLab

Fictional classroom data · SQLite

Explore the tables, ask a question and investigate the result.

School library

6 rows
Students
StudentIDPKStudentNameYearGroup
1Asha8
2Ben9
3Cara8
4Dylan10
5Eva9
6Finn8

Fictional students, books and loans. ReturnedOn is NULL while a book is still on loan.

SQL editor

BASIC
Ctrl / ⌘ + Enter to run
96 / 8,000 characters

SELECT and WITH queries · 200-row preview · teaching data reset for each run.

QueryLab guide

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.