SQL: defining tables and joining them

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

A11.4Databases and big dataA level60 min

Do this lesson in the simulator

At GCSE (F7.3 and F7.4) you wrote SELECT, FROM, WHERE and ORDER BY, joined two tables with a WHERE condition, and changed data. At A level SQL does the whole job: it defines the tables from your normalised design, and it answers questions that need several tables, counts, totals and queries inside queries.

SQL has two halves. The data definition language (DDL) creates and changes the structure: CREATE TABLE, ALTER TABLE, DROP TABLE. The data manipulation language (DML) works with the records: SELECT, INSERT, UPDATE, DELETE.

CREATE TABLE

Here is the club's 3NF design, Team (TeamID, TeamName), Robot (RobotID, Name, Colour, TeamID*), built in SQL:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("""CREATE TABLE Team (
                  TeamID INT NOT NULL,
                  TeamName VARCHAR(20) NOT NULL,
                  PRIMARY KEY (TeamID)
              )""")
db.execute("""CREATE TABLE Robot (
                  RobotID INT NOT NULL,
                  Name VARCHAR(20) NOT NULL,
                  Colour VARCHAR(10),
                  TeamID INT,
                  PRIMARY KEY (RobotID),
                  FOREIGN KEY (TeamID) REFERENCES Team(TeamID)
              )""")

for (sql,) in db.execute("SELECT sql FROM sqlite_master WHERE type = 'table'"):
    print(sql)
    print()

Run this in the simulator

Each field has a name, a data type and optionally a constraint:

Data type	Holds
`INT` or `INTEGER`	a whole number
`REAL` or `FLOAT`	a number with a fractional part
`CHAR(n)`	text of exactly `n` characters, padded if shorter
`VARCHAR(n)`	text of up to `n` characters
`DATE`, `TIME`, `DATETIME`	a date, a time, or both
`BOOLEAN`	true or false

Constraint	Means
`NOT NULL`	the field must have a value
`PRIMARY KEY (a)` or `PRIMARY KEY (a, b)`	the primary key, single or composite
`FOREIGN KEY (a) REFERENCES T(b)`	`a` must match a `b` in table `T`
`UNIQUE`	no two records may share the value

The key can also be written straight after its field, as in TeamID INT PRIMARY KEY, and a foreign key as TeamID INT REFERENCES Team(TeamID). Both styles mean the same. Create the tables in order: a table that a foreign key refers to must exist first.

SQLite accepts all these type names, although it stores values more loosely than most databases: it does not stop a 30-character name going into a VARCHAR(20). Exam answers should still give sensible types and lengths.

ALTER TABLE and DROP TABLE

ALTER TABLE Robot ADD Battery INT
DROP TABLE Booking

ALTER TABLE changes the structure of an existing table, here adding a field. DROP TABLE deletes a table and all its records; unlike DELETE, nothing is left behind, not even the empty table.

The club database

The rest of this lesson and the next use this file. It builds four tables in 3NF:

# club.py: the robotics club's database, in third normal form
import sqlite3

TEAMS = [(1, "Hawks"), (2, "Owls"), (3, "Kites")]
ROBOTS = [(1, "Ada", "green", 1), (2, "Bolt", "red", 1), (3, "Cog", "blue", 2), (4, "Dot", "yellow", 2)]
TASKS = [("W", "wall"), ("S", "square"), ("L", "line")]
RUNS = [(1, 1, "W", 42.0, 3.1), (2, 1, "S", 80.0, 9.4), (3, 2, "W", 38.0, 2.7), (4, 1, "W", 45.5, 3.0),
        (5, 3, "S", 76.5, 8.8), (6, 2, "S", 81.0, 10.2), (7, 4, "L", 60.0, 6.5)]

def open_db():
    """A fresh database with the teams, robots, tasks and runs tables."""
    db = sqlite3.connect(":memory:")
    db.execute("PRAGMA foreign_keys = ON")
    db.execute("CREATE TABLE teams (team_id INTEGER PRIMARY KEY, team_name VARCHAR(20) NOT NULL)")
    db.execute("CREATE TABLE robots (robot_id INTEGER PRIMARY KEY, name VARCHAR(20) NOT NULL, colour VARCHAR(10), "
               "team_id INTEGER REFERENCES teams(team_id))")
    db.execute("CREATE TABLE tasks (task_code CHAR(1) PRIMARY KEY, task_name VARCHAR(20) NOT NULL)")
    db.execute("CREATE TABLE runs (run_id INTEGER PRIMARY KEY, robot_id INTEGER REFERENCES robots(robot_id), "
               "task_code CHAR(1) REFERENCES tasks(task_code), cm REAL, seconds REAL)")
    db.executemany("INSERT INTO teams VALUES (?, ?)", TEAMS)
    db.executemany("INSERT INTO robots VALUES (?, ?, ?, ?)", ROBOTS)
    db.executemany("INSERT INTO tasks VALUES (?, ?)", TASKS)
    db.executemany("INSERT INTO runs VALUES (?, ?, ?, ?, ?)", RUNS)
    db.commit()
    return db

def show(db, sql, params=()):
    """Run a query and print each record on its own line."""
    for row in db.execute(sql, params):
        print(*row)

Searching: LIKE, BETWEEN and IN

import club
db = club.open_db()

club.show(db, "SELECT name FROM robots WHERE name LIKE 'D%'")
print("---")
club.show(db, "SELECT name FROM robots WHERE colour LIKE '_e%'")
print("---")
club.show(db, "SELECT run_id, cm FROM runs WHERE cm BETWEEN 40 AND 60")
print("---")
club.show(db, "SELECT run_id, task_code FROM runs WHERE task_code IN ('S', 'L')")

Run this in the simulator

In LIKE, the wildcard % matches any number of characters (including none) and _ matches exactly one. So '_e%' means "any one character, then e, then anything", which matches red and yellow but not green or blue. BETWEEN 40 AND 60 includes both ends.

INNER JOIN

A join combines records from two tables where a condition holds, usually a foreign key matching a primary key:

import club
db = club.open_db()

club.show(db, """SELECT runs.run_id, robots.name, tasks.task_name, runs.cm
                 FROM runs
                 INNER JOIN robots ON runs.robot_id = robots.robot_id
                 INNER JOIN tasks ON runs.task_code = tasks.task_code
                 WHERE runs.cm > 50
                 ORDER BY runs.cm DESC""")

Run this in the simulator

INNER JOIN ... ON keeps only the pairs of records that match; plain JOIN means the same. Each extra table needs one more join, so a query across four tables has three. The GCSE form, FROM runs, robots WHERE runs.robot_id = robots.robot_id, gives the same result. An inner join drops records with no match: the Kites team has no robots, so it never appears in a join of teams and robots.

Aggregates and GROUP BY

Aggregate functions turn many records into one value: COUNT, SUM, AVG, MIN and MAX. GROUP BY makes one group per distinct value, and the aggregate is worked out for each group. HAVING filters the groups, the way WHERE filters records.

import club
db = club.open_db()

club.show(db, "SELECT COUNT(*), ROUND(AVG(cm), 1), MAX(cm) FROM runs")
print("---")
club.show(db, """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("---")
club.show(db, """SELECT robots.name, COUNT(*)
                 FROM runs JOIN robots ON runs.robot_id = robots.robot_id
                 GROUP BY robots.name
                 HAVING COUNT(*) >= 2""")

Run this in the simulator

The order of the clauses is fixed: SELECT, FROM, joins, WHERE, GROUP BY, HAVING, ORDER BY.

Nested SELECT

A query can use the result of another query, written in brackets. The inner query runs first:

import club
db = club.open_db()

club.show(db, """SELECT name FROM robots
                 WHERE robot_id IN (SELECT robot_id FROM runs WHERE task_code = 'S')""")
print("---")
club.show(db, """SELECT run_id, cm FROM runs
                 WHERE cm = (SELECT MAX(cm) FROM runs)""")

Run this in the simulator

The first finds the robots that have tried the square; the second finds the longest run without knowing its distance in advance.

In the exam

Questions give the tables of a database and ask you to write SQL: a CREATE TABLE with suitable types and keys, or a SELECT that needs data from two or more tables. Other questions give SQL and ask what it returns, or ask you to correct or complete it. Name every table in a join, and match each foreign key to its primary key.

AQA A level 4.10.4

AQA asks you to be able to use SQL to retrieve, update, insert and delete data from multiple tables of a relational database, and to use SQL to define a database table. A join can be written with JOIN ... ON or with a WHERE condition; both link the tables.

SELECT Robot.Name, Run.Cm
FROM Run
INNER JOIN Robot ON Run.RobotID = Robot.RobotID
WHERE Run.Cm > 50
ORDER BY Run.Cm DESC

OCR A level 1.3.2

OCR asks you to interpret and modify SQL, including SELECT with FROM and WHERE, LIKE with wildcards, AND and OR, INNER JOIN, nested SELECT, INSERT, DELETE and DROP.

Eduqas A level 2.5

Eduqas includes SQL as part of its databases topic. Be ready to write queries that define tables and that retrieve data from more than one table.

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)

CREATE TABLE all three, with a primary key on each and both foreign keys declared with REFERENCES.
Insert the records.
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:")

Challenges

Write the CREATE TABLE for Booking (StudentID*, RobotID*, SessionDate).
Using club.py, list every task with the number of runs at it, including tasks nobody has tried. Why does an inner join lose them? Look up LEFT JOIN.
Find the name of the robot that made the longest run, using a nested SELECT and no ORDER BY.