COMP3311 20T3 The University of New South Wales Database Systems
COMP3311 Database Systems
20T3
[Instructions] [Notes] [Database] [Course Website] [Cheat Sheets]
[Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8] [Q9] [Q10] [Q11] [Q12] Question 10 (7 marks)
Consider the following partial Python script that uses the same soccer database as for the Prac Part of the exam:
qT = “select id,country from Teams order by country”
qC = “””
select m.city, count(*)
from Matches m join Involves i on (m.id = i.match)
where i.team = %s
group by m.city
order by m.city
“””
db = psycopg2.connect(“dbname=footy”)
cur = db.cursor()
cur.execute(qT)
results1 = cur.fetchall()
for tuple1 in results1:
tid, t = tuple1
cur.execute(qC, [tid])
results2 = cur.fetchall()
for tuple2 in results2:
c, n = tuple2
print(f”{t} {c} {n}”)
Assume that all statements in the above script execute without generating an exception.
Exercises:
a. Explain briefly (2-3 lines) what this script is attempting to achieve (i.e. what does its output contain).
b. Assuming that we had a much larger database with the same schema but with 100 teams and 500 cities in the database, calculate how many calls to cur.execute() will be issued in executing the script.
c. Write a single SQL query that could be used to achieve the same result.
Instructions:
Type your answers to this question into a file called q10.txt Submit via: submit q10.txt
End of Question