Answering a question with a multi-table query
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. A question asks for each member's name and the titles of the books they have on loan. Members are in one table and loans in another. What is the first step?
2. Which ON condition correctly joins loans to members?
3. Which query lists each class with its teacher's name?
4. You want only the loans for the member named 'Ava'. Where does that condition go?
5. A question says: 'list the members' loans in order of due date'. Which clause finishes the query?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1SELECT members.name, loans.title FROM members JOIN loans ON members.memberId = loans.memberId; | AON loans.memberId = members.memberId | |
| 2SELECT classes.className, teachers.name FROM classes JOIN teachers ON classes.teacherId = teachers.teacherId ORDER BY classes.className; | BWHERE members.name = 'Ava Nguyen' | |
| 3Follow the keys: join the two tables where the foreign key equals the primary key shown on the ERD relationship line. | CLists each member's name beside the titles of the loans they have, by matching the key in both tables. | |
| 4After joining the tables, add WHERE to keep only the rows the question asks for, such as one member's loans. | DThe ON condition is missing, so every member is paired with every loan and the results include loans that member never borrowed. | |
| 5SELECT members.name, loans.title FROM members JOIN loans; | EThe join compares the wrong fields: classId is the primary key of classes, not the foreign key that points to teachers. | |
| 6FROM classes JOIN teachers ON classes.classId = teachers.teacherId | FLists every class with its teacher's name, sorted by class name. |
C
Definitions
multi-table query
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT members.name, loans.title
FROM members ____ loans
ON members.id = loans.memberId;
2.
SELECT classes.className, teachers.name
FROM classes JOIN teachers
ON classes._________ = teachers.id;
3.
SELECT m.name, l.title FROM members m
JOIN loans l ON m.id = l.memberId
_____ l.returned = 'No' ORDER BY m.name;
4.
SELECT c.className, t.name FROM classes c
JOIN teachers t ON c.teacherId = t.id
WHERE c.yearLevel = 9 ________ t.name;
5.
SELECT classes.className, teachers.name
FROM classes JOIN ________
ON classes.teacherId = teachers.id;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT m.name, l.dueDate
B
FROM members m JOIN loans l
C
ON m.memberId = l.loanId
D
ORDER BY m.name
Question 2
A
SELECT c.className, t.name
B
FROM classes c
C
JOIN teachers t
D
ON c.classId = t.teacherId
E
ORDER BY c.className
Question 3
A
SELECT m.name, l.dueDate
B
FROM members m JOIN loans l
C
ON m.memberId = memberId
D
WHERE m.name = 'Ava'
Question 4
A
SELECT t.name, c.className
B
FROM teachers t JOIN classes
C
ON t.teacherId = c.teacherId
D
ORDER BY t.name
Question 5
A
SELECT m.name, l.title
B
FROM members m JOIN loans l
C
ON m.memberId = l.memberId
D
JOIN books b
E
ON l.bookId = b.bookId
F
WHERE m.name = 'Ava'
Answer Key
Answering a question with a multi-table query
Section A: Quick Questions
Q1
B — Work out which tables hold the fields you need
Q2
A — ON loans.memberId = members.memberId
Q3
A — SELECT classes.className, teachers.name FROM classes JOIN teachers ON classes.teacherId = teachers.teacherId;
Q4
B — In a WHERE clause after the JOIN ... ON
Q5
A — ORDER BY loans.dueDate
Section B: Match the Pairs
Q1
SELECT members.name, loans.title FROM members JOIN loans ON members.memberId = loans.memberId; → Lists each member's name beside the titles of the loans they have, by matching the key in both tables.
Q2
SELECT classes.className, teachers.name FROM classes JOIN teachers ON classes.teacherId = teachers.teacherId ORDER BY classes.className; → Lists every class with its teacher's name, sorted by class name.
Q3
Follow the keys: join the two tables where the foreign key equals the primary key shown on the ERD relationship line. → ON loans.memberId = members.memberId
Q4
After joining the tables, add WHERE to keep only the rows the question asks for, such as one member's loans. → WHERE members.name = 'Ava Nguyen'
Q5
SELECT members.name, loans.title FROM members JOIN loans; → The ON condition is missing, so every member is paired with every loan and the results include loans that member never borrowed.
Q6
FROM classes JOIN teachers ON classes.classId = teachers.teacherId → The join compares the wrong fields: classId is the primary key of classes, not the foreign key that points to teachers.
Section C: Definitions
Q1
multi-table query — A query that gets fields from more than one table: find which tables hold them, JOIN them on matching keys, then add WHERE and ORDER BY to match the question.Section D: Fill in the Blanks
Q1
SELECT members.name, loans.title
FROM members JOIN loans
ON members.id = loans.memberId;Q2
SELECT classes.className, teachers.name
FROM classes JOIN teachers
ON classes.teacherId = teachers.id;Q3
SELECT m.name, l.title FROM members m
JOIN loans l ON m.id = l.memberId
WHERE l.returned = 'No' ORDER BY m.name;Q4
SELECT c.className, t.name FROM classes c
JOIN teachers t ON c.teacherId = t.id
WHERE c.yearLevel = 9 ORDER BY t.name;Q5
SELECT classes.className, teachers.name
FROM classes JOIN teachers
ON classes.teacherId = teachers.id;Section E: Spot the Error
Q1
C — The join must follow the foreign key. loans.memberId is the foreign key that matches members.memberId, not loans.loanId. The ON line should be ON m.memberId = l.memberId.
Q2
D — The foreign key in classes is teacherId, so it must be matched to the primary key teachers.teacherId. classId is the wrong field. It should be ON c.teacherId = t.teacherId.
Q3
C — Both members and loans have a memberId field, so the second one is ambiguous. Each field in the ON condition needs its table alias, giving ON m.memberId = l.memberId.
Q4
B — The query uses the alias c in SELECT and ON, but the classes table was never given that alias. It should be FROM teachers t JOIN classes c.
Q5
A — The title field is held in the books table, not in loans. The query must select b.title, because b is the alias for books.