Databases and SQL

Tables, records, fields and keys

A database is an organised collection of data. At GCSE it is made of tables, and a table is a grid where:

• each column is a field, a single kind of fact, with a data type
• each row is a record, all the facts about one thing
• one field is chosen as the primary key, whose value is unique to each record

StudentID is the primary key. There may be two students called Asha, and two with a mark of 56, but never two with the same StudentID, so a key finds exactly one record.

A real database in Python

Python has SQLite built in. ":memory:" makes a database that exists only while the program runs, which is ideal for learning. Everything in this topic can be run with the button.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE Student (StudentID INTEGER PRIMARY KEY, Name TEXT, Year INTEGER, Mark INTEGER)")
db.executemany("INSERT INTO Student VALUES (?, ?, ?, ?)", [
    (1, "Asha", 10, 56),
    (2, "Ben", 11, 71),
    (3, "Chloe", 10, 38),
    (4, "Dev", 11, 90),
    (5, "Esme", 10, 64),
    (6, "Alan", 11, 45),
])

for record in db.execute("SELECT * FROM Student"):
    print(record)

Each record comes back as a tuple, in the order of the fields. The data types SQLite uses are INTEGER for whole numbers, REAL for decimals and TEXT for strings.

Searching with SELECT

The exam asks you to search a table with SELECT. The three parts, always in this order:

SELECT * means every field. A query with no WHERE returns every record.

This program sets the same table up and runs five queries, printing each query and its result. Run it and match each result to the table.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE Student (StudentID INTEGER PRIMARY KEY, Name TEXT, Year INTEGER, Mark INTEGER)")
db.executemany("INSERT INTO Student VALUES (?, ?, ?, ?)", [
    (1, "Asha", 10, 56), (2, "Ben", 11, 71), (3, "Chloe", 10, 38),
    (4, "Dev", 11, 90), (5, "Esme", 10, 64), (6, "Alan", 11, 45),
])

def run(sql):
    print(sql)
    for record in db.execute(sql):
        print("    ", record)

run("SELECT Name, Mark FROM Student WHERE Mark >= 60")
run("SELECT * FROM Student WHERE Year = 10 AND Mark < 50")
run("SELECT Name FROM Student WHERE Year = 11 OR Mark > 60")
run("SELECT Name FROM Student WHERE Name LIKE 'A%'")
run("SELECT Name, Mark FROM Student ORDER BY Mark DESC")

The operators in a WHERE

LIKE 'A%' means "starts with A". '%a' ends with a, and '%an%' contains an anywhere. That is the whole of pattern matching at GCSE.

Changing data: INSERT, UPDATE and DELETE

Three statements change the table. All three come up in the exam.

INSERT INTO Student (StudentID, Name, Year, Mark) VALUES (7, 'Femi', 10, 58)
UPDATE Student SET Mark = Mark + 5 WHERE Year = 10
DELETE FROM Student WHERE Mark < 45

The program below counts the records each statement touches, which is a good habit before trusting an UPDATE or DELETE.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE Student (StudentID INTEGER PRIMARY KEY, Name TEXT, Year INTEGER, Mark INTEGER)")
db.executemany("INSERT INTO Student VALUES (?, ?, ?, ?)", [
    (1, "Asha", 10, 56), (2, "Ben", 11, 71), (3, "Chloe", 10, 38),
    (4, "Dev", 11, 90), (5, "Esme", 10, 64), (6, "Alan", 11, 45),
])

db.execute("INSERT INTO Student (StudentID, Name, Year, Mark) VALUES (7, 'Femi', 10, 58)")

changed = db.execute("UPDATE Student SET Mark = Mark + 5 WHERE Year = 10").rowcount
print(changed, "records updated")

removed = db.execute("DELETE FROM Student WHERE Mark < 45").rowcount
print(removed, "record deleted")

for record in db.execute("SELECT Name, Mark FROM Student WHERE Year = 10"):
    print(record)

Year 10 had four students once Femi joined, so four records were updated. Chloe's 38 became 43, which is under 45, so she was then deleted.

Two tables and a foreign key

A school has far more than students: subjects, results, teachers. Putting every fact in one table repeats the same details over and over. A relational database splits the facts across several tables and links them with a foreign key, a field in one table that holds the primary key of another.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE Student (StudentID INTEGER PRIMARY KEY, Name TEXT)")
db.execute('''CREATE TABLE Result (
    ResultID INTEGER PRIMARY KEY,
    StudentID INTEGER,
    Subject TEXT,
    Mark INTEGER,
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID))''')
db.executemany("INSERT INTO Student VALUES (?, ?)", [(1, "Asha"), (2, "Ben")])
db.executemany("INSERT INTO Result VALUES (?, ?, ?, ?)", [
    (1, 1, "Computing", 56), (2, 1, "Maths", 71),
    (3, 2, "Computing", 64), (4, 2, "Maths", 48),
])

sql = '''SELECT Student.Name, Result.Subject, Result.Mark
         FROM Student JOIN Result ON Student.StudentID = Result.StudentID
         WHERE Result.Mark >= 60'''
for record in db.execute(sql):
    print(record)

StudentID in Result is the foreign key. Asha's name is stored once, however many results she has. A JOIN joins the tables back together for a query. Joins go beyond what the GCSE examines, but the key idea, that a foreign key links two tables, does not.

SQL from a program, safely

Real programs build queries out of what the user typed. That is dangerous if done carelessly. Suppose a program looks a student up by name, and somebody types this as the name:

x' OR '1'='1

Joined into the query as text, the query becomes SELECT * FROM Student WHERE Name = 'x' OR '1'='1', and '1'='1' is always true, so every record is returned. This is SQL injection.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE Student (StudentID INTEGER PRIMARY KEY, Name TEXT, Mark INTEGER)")
db.executemany("INSERT INTO Student VALUES (?, ?, ?)",
               [(1, "Asha", 56), (2, "Ben", 71), (3, "Chloe", 38)])

name = "x' OR '1'='1"

unsafe = "SELECT * FROM Student WHERE Name = '" + name + "'"
print(len(db.execute(unsafe).fetchall()), "records returned by the unsafe query")

safe = db.execute("SELECT * FROM Student WHERE Name = ?", (name,))
print(len(safe.fetchall()), "records returned by the safe query")

The ? is a placeholder. The database is given the query and the value separately, and treats the value only ever as data, never as part of the instruction. Whatever the user types, it cannot change what the query does.

Key terms you must be able to define

Database

An organised collection of data that can be searched, sorted and updated.

Table

A grid in a database, with a column for each field and a row for each record.

Record

One row of a table, holding all the facts about one thing.

Field

One column of a table, holding one kind of fact.

Primary key

A field whose value is unique for every record, so it identifies exactly one.

Foreign key

A field in one table that holds the primary key of a record in another, linking the two.

SQL

Structured Query Language, used to search and change data in a relational database.

Wildcard

A symbol that stands for any characters in a pattern. In SQL LIKE, the % sign.

SQL injection

An attack in which text typed by a user is run as part of a database query.

Flashcards

Say the definition out loud before you flip the card. Retrieving it from memory is what builds recall, reading it again is not.

Reach the top grade on this topic

SQL questions are about precision: one wrong keyword costs the mark, and every clause is worth one.

• Write SELECT, FROM and WHERE in the right order, and a field name exactly as the table gives it
• Put quotation marks around text values and none around numbers
• Use AND and OR to build conditions, and LIKE with the percent sign for a pattern
• Never write UPDATE or DELETE without a WHERE unless every record is meant
• Explain a primary key as unique, and a foreign key as the link to another table

Mistakes that cost marks here

SELECT Title
FROM Book
WHERE Pages > 300
ORDER BY Pages DESC

UPDATE Book
SET Author = 'A. Khan'
WHERE BookID = 7