SQL: SELECT

SELECT, FROM, WHERE and ORDER BY on the class's robot runs.

F7.3Files and databasesGCSE15 min

Covers OCR J277 2.2.3 Additional programming techniques · AQA 8525 3.7.2 Structured query language (SQL)

Do this lesson in the simulator

Print it: worksheet (PDF) · answers (PDF) · as a web page

SQL, Structured Query Language, is how you ask a database a question. It reads almost like English, and one line of it can do what a loop and an if would take ten lines to do in Python. This lesson asks questions of the class's robot runs.

# logbook.py: the class's robot runs as a relational database
import sqlite3

ROBOTS = [(1, "Ada", "green"), (2, "Bolt", "red"), (3, "Cog", "blue")]
RUNS = [(1, 1, "wall", 42.0, 3.1), (2, 1, "square", 80.0, 9.4), (3, 2, "wall", 38.0, 2.7),
        (4, 1, "wall", 45.5, 3.0), (5, 3, "square", 76.5, 8.8), (6, 2, "square", 81.0, 10.2)]

def open_db():
    """A fresh database with the robots and runs tables."""
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE robots (robot_id INTEGER PRIMARY KEY, name TEXT, colour TEXT)")
    db.execute("CREATE TABLE runs (run_id INTEGER PRIMARY KEY, robot_id INTEGER REFERENCES robots(robot_id), "
               "task TEXT, cm REAL, seconds REAL)")
    db.executemany("INSERT INTO robots VALUES (?, ?, ?)", ROBOTS)
    db.executemany("INSERT INTO runs VALUES (?, ?, ?, ?, ?)", RUNS)
    return db

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

show(db, sql) runs a query and prints each record, so the SQL is all you need to write.

SELECT and FROM

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

import logbook
db = logbook.open_db()

logbook.show(db, "SELECT * FROM runs")
print("---")
logbook.show(db, "SELECT task, cm FROM runs")

Run this in the simulator

SELECT says which fields you want, FROM says which table. * means every field. Change the field list and run again: the query returns exactly the columns you name, in that order.

SELECT task, cm FROM runs takes the task and cm columns of every record in the runs tableSELECT task, cm FROM runsthe runs tablerun_idrobot_idtaskcmseconds11wall42.03.121square80.09.432wall38.02.741wall45.53.053square76.58.862square81.010.2what comes backtaskcmwall42.0square80.0wall38.0wall45.5square76.5square81.0every one of the 6 records, but only the fields named after SELECT
SELECT chooses columns. Every record in runs comes back, but with only the two fields the query names, task and cm, in that order. The other 3 fields are still in the table, just not in the answer.

SQL keywords are written in capitals by convention, which makes them easy to tell apart from table and field names. The query itself is just a string in Python.

WHERE

WHERE keeps only the records that match a condition:

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

import logbook
db = logbook.open_db()

logbook.show(db, "SELECT run_id, cm FROM runs WHERE cm > 50")
print("---")
logbook.show(db, "SELECT run_id, cm FROM runs WHERE task = 'wall'")
print("---")
logbook.show(db, "SELECT run_id, task, cm FROM runs WHERE task = 'square' AND cm > 78")

Run this in the simulator

Conditions use = (a single equals, unlike Python), <> or != for not equal, <, >, <=, >=, and join with AND and OR. Text values go in single quotes, so they do not clash with the Python string around the query.

WHERE cm > 50 tests each record's cm; the records where it is true come back, the others are left outSELECT run_id, cm FROM runs WHERE cm > 50every recordrun_idcm142.0280.0338.0445.5576.5681.0is cm > 50?42.0 > 50no80.0 > 50yes38.0 > 50no45.5 > 50no76.5 > 50yes81.0 > 50yeswhat comes backrun_idcm280.0576.5681.03 of the 6 records pass the test, and they stay in table order
WHERE chooses records. The database tests the condition on every record in turn: where it is true (green) the record comes back, and where it is false (red) the record is left out.

ORDER BY

ORDER BY sorts the results: ASC for ascending (the default), DESC for descending.

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

import logbook
db = logbook.open_db()

logbook.show(db, "SELECT run_id, cm FROM runs ORDER BY cm DESC")
print("---")
logbook.show(db, "SELECT run_id, seconds FROM runs WHERE task = 'wall' ORDER BY seconds ASC")

Run this in the simulator

The clauses always come in this order: SELECT, FROM, WHERE, ORDER BY. The second query answers a real question in one line: which wall runs were fastest? The sorting algorithms of module F5 are hidden inside the database.

Using the answers in Python

A query's results are a list of tuples, so the rest of the program can use them. Here the robot replays the longest run:

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

import logbook
db = logbook.open_db()

best = db.execute("SELECT run_id, cm FROM runs WHERE task = 'wall' ORDER BY cm DESC").fetchall()[0]
print("longest wall run:", best)
forward(50, distance=best[1])

Run this in the simulator

fetchall() gives every matching record as a list; [0] is the first, and here that is the longest.

The longest wall run query in three steps: WHERE keeps the three wall runs, ORDER BY cm DESC sorts them longest first, and [0] takes the first1 WHEREtask = 'wall'run_idtaskcm1wall42.02square80.03wall38.04wall45.55square76.56square81.02 ORDER BYcm DESCrun_idcm445.5142.0338.0longest first,only run_id and cm3 take the firstfetchall()[0](4, 45.5)then the robot drivesbest[1] cm:forward(50,distance=45.5)
The query from the cell above, one clause at a time. WHERE crosses out the runs that are not wall runs, ORDER BY cm DESC puts the 3 that are left longest first, and [0] picks the top one, run 4 at 45.5 cm.

Task: the longest square runs

Write one SQL query that finds the run_id and cm of every square run longer than 77 cm, longest first, and print each result as run 6: 81.0 cm. Then drive forward the distance of the shortest of those runs, divided by 4.

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

import logbook
db = logbook.open_db()

Challenges

  1. Find every run that took less than 5 seconds, fastest first.
  2. Find the robots whose colour is not green.
  3. Write the Python loop that does the same job as SELECT run_id FROM runs WHERE cm > 50. How many lines is it?

Past paper questions on this

Real exam questions that test this, each worked through with a program you can run.