A11.4 SQL: defining tables and joining them worksheet

CREATE TABLE with types and keys, ALTER and DROP, then SELECT with INNER JOIN, wildcards, aggregates, GROUP BY and nested queries.

Questions 6 marks in all

[1 mark]Which SQL statement defines a new table?

ACREATE TABLE
BINSERT INTO
CALTER TABLE
DSELECT INTO

[1 mark]Which condition matches names that start with D and have exactly three letters?

Aname LIKE 'D__'
Bname LIKE 'D%'
Cname LIKE '%D%'
Dname = 'D__'

[1 mark]What does this print?

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE robots (robot_id INTEGER PRIMARY KEY, name TEXT)")
db.execute("CREATE TABLE runs (run_id INTEGER PRIMARY KEY, robot_id INTEGER, cm REAL)")
db.executemany("INSERT INTO robots VALUES (?, ?)", [(1, "Ada"), (2, "Bolt"), (3, "Cog")])
db.executemany("INSERT INTO runs VALUES (?, ?, ?)", [(1, 1, 42.0), (2, 2, 38.0), (3, 1, 80.0)])
for row in db.execute("""SELECT robots.name, COUNT(*), MAX(runs.cm)
                         FROM runs JOIN robots ON runs.robot_id = robots.robot_id
                         GROUP BY robots.name ORDER BY robots.name"""):
    print(*row)

[1 mark]What does this print?

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE runs (run_id INTEGER PRIMARY KEY, task TEXT, cm REAL)")
db.executemany("INSERT INTO runs VALUES (?, ?, ?)", [(1, "wall", 42.0), (2, "square", 80.0), (3, "wall", 45.5), (4, "square", 81.0)])
print(db.execute("SELECT run_id FROM runs WHERE cm = (SELECT MAX(cm) FROM runs WHERE task = 'wall')").fetchall())

[1 mark]What is the difference between WHERE and HAVING?

AWHERE filters records before grouping; HAVING filters the groups after GROUP BY
BThey are the same
CHAVING filters records; WHERE filters groups
DHAVING can only be used with ORDER BY

[1 mark]What does DROP TABLE Booking do?

ADeletes the Booking table and all its records
BDeletes all records but keeps the empty table
CDeletes the last record
DRemoves the primary key

The task: build it, then join it

Build this design and query it. The lists give the records: teams holds (team_id, team_name), robots holds (robot_id, name, team_id) and runs holds (run_id, robot_id, task, cm), where ids are whole numbers, names and tasks are text and cm is a float. - Team (team_id, team_name) - Robot (robot_id, name, team_id\*) - Run (run_id, robot_id\*, task, cm) 1. CREATE TABLE all three, with a primary key on each and both foreign keys declared with REFERENCES. 2. Insert the records. 3. With one SELECT that joins all three tables with two JOIN ... ON clauses, uses GROUP BY, COUNT and MAX, and sorts by team name, print one line per team: <team_name>: <number of runs> runs, longest <longest cm> cm, for example Owls: 2 runs, longest 76.5 cm. The robot does not move.

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import sqlite3

teams = [(1, "Hawks"), (2, "Owls")]
robots = [(1, "Ada", 1), (2, "Bolt", 1), (3, "Cog", 2), (4, "Dot", 2)]
runs = [(1, 1, "wall", 42.0), (2, 1, "square", 80.0), (3, 2, "wall", 38.0), (4, 1, "wall", 45.5),
        (5, 3, "square", 76.5), (6, 2, "square", 81.0), (7, 4, "wall", 51.0)]

db = sqlite3.connect(":memory:")

Plan your program here, then type it in and press Run.

Do it on the robot
www.bugbotlab.com/learn/a11-4-sql-defining-and-joining/
The simulator checks it and tells you when it passes. Nothing to install, no account.

Challenges

Write the CREATE TABLE for Booking (StudentID\*, RobotID\*, SessionDate).

Using club.py, list every team with the number of robots it has, including Kites, which has none. Why does an inner join lose Kites? Look up LEFT JOIN.

Find the name of the robot that made the longest run, using a nested SELECT and no ORDER BY.

What this lesson is about

Questions 6 marks in all

The task: build it, then join it

Challenges