Database Designer: Ask a Database a Question

Before you start: try it once yourself

Run the lesson's queries yourself once before you start, about ten minutes.

1. Open the lesson. Go to mytekdev.com/try/drawdb?lesson=ask-a-database-a-question&ref=fam-drawdb-1. The diagram has four tables. Press the SQL Query bar at the bottom of the diagram.
2. See a table. Press the students button after Tables: in the bar above the editor. It writes SELECT * FROM "students"; and shows 6 rows.
3. Ask with WHERE. Type SELECT name FROM students WHERE grade = 9; and press Run: Ana Lopez and Dev Patel.
4. Follow the numbers. Run SELECT * FROM memberships WHERE student_id = 1;: club_id 1 and 2. Then the JOIN: SELECT clubs.name FROM memberships JOIN clubs ON clubs.id = memberships.club_id WHERE memberships.student_id = 1;: Chess and Robotics.
5. Break a key. Run INSERT INTO memberships (student_id, club_id, role) VALUES (4, 7, 'member');: Statement 1: FOREIGN KEY constraint failed. Change 7 to 3 and run it: 1 row changed.

The JOIN turns Ana's two club ids into names.

The ideas, in plain words

A database keeps each kind of thing in its own table: students in one, clubs in another. Each row is one person or one club, and every row has an id that no other row in that table has, its primary key. SQL is how you ask: SELECT names the columns you want, FROM names the table, WHERE keeps only the rows that match, and ORDER BY sorts them.

Facts that connect two things live in a table of their own. A person can be in many clubs, and a club has many members, so the database keeps a memberships table with one row per pairing: person 1 is in club 2. That table holds only ids, which is why reading it by hand means looking each id up. A JOIN does the looking up: it matches each membership to the club whose id equals its club_id, so the answer comes back as names.

The lines in the diagram are foreign keys: memberships.club_id must match some clubs.id. The database enforces that, so a membership in a club that does not exist is refused. In this database the lines are also set to delete a club's memberships when the club is deleted; that rule is part of the design, and the diagram is where it was decided. The queries run in SQLite, a real database engine, inside the browser.

Do it together

1. Open mytekdev.com/try/drawdb?lesson=ask-a-database-a-question&ref=fam-drawdb-1 together. The diagram shows four tables: students, clubs, memberships and meetings. A short guide runs along the bottom; follow it together. It has your child open the SQL Query bar, press the students button, and run a first query.

   You'll know it worked when: The students button shows six rows, from Ana Lopez to Fatima Noor, and the first query returns Ana Lopez and Dev Patel.

2. Read the diagram together: each box is a table, each line in it a column, and the key symbol marks id, the primary key. Then your child predicts and runs SELECT name FROM students WHERE grade = 10; and SELECT name, room FROM clubs ORDER BY name;

   If you get stuck: Names have to match the diagram exactly. A misspelling gives no such table or no such column, with a hint under it.

   You'll know it worked when: Ben Carter and Fatima Noor; then Art, Chess, Debate and Robotics with their rooms.

3. Which clubs is Ana in? Run SELECT * FROM memberships WHERE student_id = 1; and look up each club_id in the clubs table by hand.

   You'll know it worked when: Club 1 is Chess and club 2 is Robotics.

4. Now let the database do it: SELECT clubs.name FROM memberships JOIN clubs ON clubs.id = memberships.club_id WHERE memberships.student_id = 1;

   You'll know it worked when: Chess and Robotics, as names this time.

5. Break a rule on purpose: INSERT INTO memberships (student_id, club_id, role) VALUES (4, 7, 'member'); Then change 7 to 3 and run it again.

   You'll know it worked when: The first try says FOREIGN KEY constraint failed, because there is no club 7. With club 3 it works, and Dev is now in Chess and Art.

6. Finish with one question: why is it good that the database refused club 7?

   You'll know it worked when: Your child explains that the membership would have pointed at a club that does not exist, and the foreign key prevents that.

Talk about it

Ask these while you work together. A child who has understood the lesson can answer them without help:

• Before you press Run: what will this query return?
• Which part of your query picks the rows, and which picks the columns?
• Why does memberships hold numbers instead of names?
• In your JOIN, which two columns have to match?
• Why did the database refuse club 7?

Bring it into other subjects

• Math: COUNT(*) and GROUP BY give the number of members in each club. Make a bar chart of the result by hand.
• Data science: write a query that answers a question a club advisor might ask, such as which grade has the most club members.
• Computer science: open File, Export SQL, SQLite. Find the CREATE TABLE for memberships and the FOREIGN KEY lines that came from the diagram's lines.
• Careers: database administrators and data analysts write queries like these every day. Find one job listing that asks for SQL.

Quick check

Name: ______________________________

1. In SELECT name FROM students WHERE grade = 9; which part picks the rows? (A) SELECT name, (B) FROM students, (C) WHERE grade = 9.

2. Why does the memberships table hold student_id and club_id instead of names? (A) numbers are smaller, (B) one person can be in many clubs and a club has many members, so the pairings get their own table, (C) names are not allowed in databases.

3. A new row in memberships says club_id 12, and there is no club 12. What does the database do, and why is that useful?

Answers

Measured in the app on the lesson's data: grade 9 is Ana Lopez and Dev Patel (and in the same six rows, grade 10, the worksheet's first row, is Ben Carter and Fatima Noor); clubs by name are Art, Chess, Debate and Robotics; Ana's memberships are club_id 1 and 2, which the JOIN shows as Chess and Robotics; Robotics (club 2) meets on 2026-10-08 (Build the drivetrain) and 2026-10-15 (Program the arm); Robotics' members are Ana Lopez, Ben Carter and Fatima Noor; inserting club 7 gives FOREIGN KEY constraint failed, and with club 3 Dev's clubs are Chess and Art.