Running a join query from code
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. A JOIN query is run from Python code. How is the SQL passed to cur.execute()?
2. After cur.execute() runs a join of students and results, what does each row from fetchall() hold?
3. Which line correctly unpacks a joined row that has a first name and a mark?
4. The query is: SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId. How many values are in each fetched tuple?
5. Which is the better way to store a long JOIN query in Python?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1cur.execute(join_sql) | AA single-quoted string with double quotes cannot span several lines, so Python raises a syntax error. Use triple quotes for a multi-line SQL string. | |
| 2for first, subject, mark in rows: | BRuns a JOIN query held in one string variable and gets the joined rows ready to fetch | |
| 3A long JOIN query can be stored as one readable string by using a multi-line string | Crows = cur.fetchall() | |
| 4Fetching all rows of a join gives a list of tuples, each holding one student field and one subject field | DUnpacks each joined row tuple into variables, with fields that came from different tables | |
| 5join_sql = "SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId" | Ejoin_sql = """SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId""" | |
| 6for first, subject in rows: # rows have 3 fields | FThe loop unpacks two variables, but each joined row tuple holds three fields, so Python raises a ValueError. Unpack one variable per selected field. |
C
Definitions
cur.execute(join_sql)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
cur._______(sql)
rows = cur.fetchall()
2.
sql = """SELECT firstName, mark
FROM students ____ results
ON students.studentId = results.studentId"""
3.
cur.execute(sql)
for row in cur.________():
print(row)
4.
row = cur.fetchone()
name, mark = ___
print(name, "got", mark)
5.
sql = """SELECT name, quantity
FROM products JOIN orders
ON products.productId = orders._________"""
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
sql = """
B
SELECT s.firstName, r.mark
C
FROM students s JOIN results
D
ON s.studentId = r.studentId
E
"""
F
cur.execute(sql)
Question 2
A
join_sql = "
B
SELECT p.name, o.quantity
C
FROM orders o JOIN products p
D
ON o.productId = p.productId
E
"""
F
cur.execute(join_sql)
Question 3
A
cur.execute(join_sql)
B
rows = cur.fetchall()
C
for name, mark, grade in rows:
D
print(name, "got", mark)
Question 4
A
cur.execute(join_sql)
B
rows = cur.fetchall()
C
for row in rows:
D
name, mark = row[0]
E
print(name, mark)
Question 5
A
cur = conn.cursor()
B
cur.execute("join_sql")
C
rows = cur.fetchall()
D
print(rows[0])
Answer Key
Running a join query from code
Section A: Quick Questions
Q1
B — As one string, which can be split over several lines with triple quotes
Q2
A — A tuple with fields from both tables, such as ('Ava', 88)
Q3
A — for first_name, mark in rows:
Q4
A — 2, one for each column in the SELECT
Q5
B — join_sql = """SELECT s.firstName, r.mark
FROM students s
JOIN results r ON s.studentId = r.studentId"""
Section B: Match the Pairs
Q1
cur.execute(join_sql) → Runs a JOIN query held in one string variable and gets the joined rows ready to fetch
Q2
for first, subject, mark in rows: → Unpacks each joined row tuple into variables, with fields that came from different tables
Q3
A long JOIN query can be stored as one readable string by using a multi-line string → join_sql = """SELECT students.firstName, results.mark
FROM students
JOIN results ON students.studentId = results.studentId"""
Q4
Fetching all rows of a join gives a list of tuples, each holding one student field and one subject field → rows = cur.fetchall()
Q5
join_sql = "SELECT students.firstName, results.mark
FROM students JOIN results ON students.studentId = results.studentId" → A single-quoted string with double quotes cannot span several lines, so Python raises a syntax error. Use triple quotes for a multi-line SQL string.
Q6
for first, subject in rows: # rows have 3 fields → The loop unpacks two variables, but each joined row tuple holds three fields, so Python raises a ValueError. Unpack one variable per selected field.
Section C: Definitions
Q1
cur.execute(join_sql) — Runs a JOIN query stored in one string. Each row fetched back is a tuple holding fields from both tables, which you can unpack into variables.Section D: Fill in the Blanks
Q1
cur.execute(sql)
rows = cur.fetchall()Q2
sql = """SELECT firstName, mark
FROM students JOIN results
ON students.studentId = results.studentId"""Q3
cur.execute(sql)
for row in cur.fetchall():
print(row)Q4
row = cur.fetchone()
name, mark = row
print(name, "got", mark)Q5
sql = """SELECT name, quantity
FROM products JOIN orders
ON products.productId = orders.productId"""Section E: Spot the Error
Q1
C — The alias r is never declared, so r.mark and r.studentId are unknown. It should be FROM students s JOIN results r.
Q2
A — A query written across several lines needs triple quotes. A single " cannot span lines. It should be join_sql = """.
Q3
C — Each joined row is a tuple with two fields, so it cannot be unpacked into three variables. It should be for name, mark in rows:.
Q4
D — row[0] is only the first field, a single value, so it cannot be unpacked into two variables. Unpack the whole tuple: name, mark = row.
Q5
B — Quotes turn the variable name into the text join_sql, which is not valid SQL. Pass the variable that holds the query: cur.execute(join_sql).