Relational databases and queries
Computer ScienceFiles, Storage & DatabasesAges 16–17
Use it with your class: students open it from a link or a QR code, with no account needed.
Next in Files, Storage & Databases
Two tables, STUDENTS and CLASSES, are linked by the foreign key ClassID pointing to the primary key ClassID of the CLASSES table. Students build a query with a set of selectors: which table to use, whether to join the two tables, what the filter condition is, which column to sort by, and how many rows to return. The program generates the matching SQL statement and runs it live on the data, so changing a condition instantly changes the result table; clicking a row shows which row of the other table it was joined with.
Lesson: Relational databases: primary keys, foreign keys, SELECT queries with conditions, joins and sorting
What it shows
A relational database stores data in tables made of records and fields. Each table has a primary key whose values are unique and never empty, and a foreign key in another table refers to that primary key to link records. Here the STUDENTS table holds ClassID, a foreign key pointing to the CLASSES table, so class details are stored once instead of being repeated for every student. The simulation turns your choices into a SQL SELECT statement with JOIN, WHERE and ORDER BY clauses and a row limit, then runs it on the data.
How to use
Start with Join set to STUDENTS table only, then switch to the joined option and note which columns are added. Choose a Column to filter on, pick a comparison operator and type a Comparison value, and ask students to predict which rows will be returned before pressing Run query. Add Sort by and lower Maximum rows to return to see how sorting and the limit work together. Click a result row to trace its foreign key to the CLASSES table.
Parameters you can change
- Join STUDENTS table only, Join STUDENTS with CLASSES on ClassID, CLASSES table only
- Column to filter on No filter, AvgScore, ClassID, Gender
- Comparison value
- Sort by No sorting, FullName ascending, AvgScore descending
- Maximum rows to return 1–12 rows
Questions to explore
- Why is ClassID a primary key in CLASSES but a foreign key in STUDENTS?
- With AvgScore sorted in descending order and a limit of 3 rows, which students are returned?
- Can you list each student's class name without joining the two tables? Why or why not?
