A3.3 Database Programming

The Shop Database

All examples use one small online art shop. It is the same Negozio shop you normalized to 3NF in A3.2. Artists make items, customers buy items, and every sale is a row in PURCHASE.

Underlined = primary key. * = foreign key. PURCHASE has a composite key: one customer can buy the same item again, but not twice on the same day.

Try your first query. The * means "every column".

A3.3.1 The Four SQL Languages

SQL (Structured Query Language) is the language used to talk to a relational database. Its commands fall into four groups, each with a different job. The DBMS (database management system) is the umbrella that runs all four.

DCL in action. These give a staff account permission to read and add items, then take the adding permission away again:

GRANT SELECT, INSERT ON Item TO shop_staff;
REVOKE INSERT ON Item FROM shop_staff;

DELETE vs TRUNCATE vs DROP

A favourite exam trap. All three "remove" something, but not the same thing.

The live boxes run SQLite, which has no user accounts and no TRUNCATE. So GRANT, REVOKE and TRUNCATE only work in full systems like MySQL. In SQLite, DELETE FROM Item; does the job of TRUNCATE.

Building Tables (DDL)

CREATE DATABASE and CREATE TABLE

CREATE DATABASE ShopDB;

CREATE TABLE TableName (
    Column1 DATATYPE constraints,
    Column2 DATATYPE constraints,
    ...
    PRIMARY KEY (Column1),
    FOREIGN KEY (Column2) REFERENCES OtherTable(OtherColumn)
);

Common data types: INT whole numbers, REAL or DECIMAL(8,2) decimals, VARCHAR(n) text up to n characters, CHAR(n) text of exactly n characters, DATE written as 'YYYY-MM-DD', BOOLEAN. More in A3.2.

Here a new table is created, linked to CUSTOMER with a foreign key, given one row, then displayed:

ALTER TABLE

Changes the structure of a table that already exists.

ALTER TABLE Customer ADD Phone VARCHAR(20);        -- add a column
ALTER TABLE Customer DROP COLUMN Phone;            -- remove a column
ALTER TABLE Customer RENAME TO Client;             -- rename the table

The new column is empty (NULL) for every existing row until you fill it in.

DROP TABLE

A3.3.2 Queries

A query is an SQL instruction that extracts information from a database. Queries do three things: filter (find exactly the rows you want), pattern match (search when you only know part of a value) and order (sort the results).

The shape of every query

SELECT   columns            -- what to show
FROM     table              -- where it lives
JOIN     table2 ON link     -- glue on a second table (if needed)
WHERE    condition          -- which rows
GROUP BY column             -- make groups (if needed)
HAVING   condition          -- which groups
ORDER BY column            -- sort

Only SELECT and FROM are required. The rest are optional, but they must always come in this order. Remember it with Some French Jugglers Wear Green Hats Often.

Choosing columns: SELECT, *, DISTINCT, AS

List the columns you want, separated by commas.

DISTINCT removes duplicate rows. Ten customers, but only four different countries:

AS renames a column in the output (an alias). It does not change the table.

Filtering rows: WHERE

WHERE works like an if statement. Only rows where the condition is true are shown.

Text and dates go in single quotes. Numbers don't. 'France', '2025-07-28', but 200.

BETWEEN includes both ends, so an item costing exactly 25 or exactly 45 is included:

Pattern matching: LIKE

Use LIKE when you only know part of the value. It has two wildcards.

In SQLite and MySQL, LIKE ignores upper and lower case. LIKE without any wildcard behaves exactly like =.

Empty values: NULL

NULL means "no value". It is not zero and not an empty string. Nothing is ever = NULL, not even NULL itself, so you must write IS NULL.

Combining conditions: AND, OR, NOT

Brackets fix it. Now only Lakeview customers appear:

Rule: whenever a WHERE mixes AND with OR, add brackets.

Sorting: ORDER BY

ASC is smallest first (A to Z, oldest date first) and is the default. DESC is largest first. Add a second column to break ties: here items with the same price are sorted alphabetically.

Not in the syllabus but handy: LIMIT n keeps only the first n rows. Perfect for "top 3" questions.

A3.3.2 Queries Between Two Tables: JOIN

Normalization split the data into separate tables. ITEM only stores ArtistID = 105, not the artist's name. To show the item and the name, you join the tables back together. The join matches rows where the foreign key equals the primary key.

SELECT Table1.column, Table2.column
FROM Table1
INNER JOIN Table2 ON Table1.ForeignKey = Table2.PrimaryKey;

Item.ItemDesc means "the ItemDesc column of the Item table". This dot notation is required when both tables have a column with the same name (both have ArtistID, so plain ArtistID gives an "ambiguous column" error). Using it everywhere in a join is good practice and examiners like it.

A join is still a normal query. You can add WHERE and ORDER BY after it:

Joining PURCHASE to CUSTOMER to see what Italian customers bought and when:

INNER, LEFT and RIGHT joins

With an INNER JOIN, Clodagh Traditional vanished because she has no items. A LEFT JOIN from ARTIST keeps her, with NULL for the item:

A RIGHT JOIN is the same idea from the other side. Adding WHERE ... IS NULL keeps only the rows with no partner. This is the classic way to answer "which artists have no items?" or "which items were never sold?".

Bonus: you can keep joining. Each extra table needs its own JOIN ... ON. PURCHASE links CUSTOMER and ITEM, so three tables give "who bought what":

How to write any query in six questions

A3.3.3 Updating Data

There are three ways to update data in a database: add, modify and remove. All three are DML.

INSERT INTO

The values go in the same order as the column list. You can leave the column list out, but then you must give a value for every column in the table's order, so naming the columns is safer.

The database checks constraints on every insert. Here the foreign key blocks an item from an artist that does not exist. This is referential integrity protecting the data:

UPDATE ... SET ... WHERE

Change one or more columns at once, separated by commas. WHERE picks which rows change.

The new value can be a calculation using the old one:

DELETE FROM ... WHERE

Customer 4 has purchases pointing at them. Deleting them would leave orphan rows in PURCHASE, so the foreign key refuses. With ON DELETE CASCADE on the foreign key, their purchases would be deleted too (see cascading in A3.2).

Indexes: why updates have a cost

An index is a separate, sorted lookup structure that stores the values of one column with a pointer to each row. It works like the index at the back of a book: instead of reading every page, you jump straight to the right one.

Primary keys are indexed automatically. You add others yourself on columns you search often:

A3.3.4 Aggregate Functions and Calculations

An aggregate function takes many rows and returns one value calculated from them.

COUNT(*) counts rows. COUNT(column) skips NULLs. Clodagh has no email:

Calculations in SELECT

You can do maths with + - * / on columns and name the result with AS. ROUND(value, 2) rounds to 2 decimal places. A calculated column exists only in the output, not in the table.

GROUP BY: one result per group

On its own, COUNT(*) gives one number for the whole table. GROUP BY splits the rows into groups that share a value, then runs the aggregate once per group. The output has one row per group.

HAVING vs WHERE

Both filter, but at different times.

This is the order the database actually runs a query. WHERE filters single rows before they are grouped. HAVING filters whole groups after grouping. Counts and averages don't exist yet when WHERE runs, so an aggregate in a condition always needs HAVING.

Both together: WHERE keeps only items under 50, then HAVING keeps only artists with at least two of them.

The Mistakes That Cost Marks

How SQL Questions Are Marked

"Construct" questions give roughly one mark per correct part of the query. Small slips like a missing semicolon are usually not penalised, but a wrong table, a missing join condition or a missing quote around text is.

On paper: put each clause on its own line, use dot notation in joins, and check that every table you name in SELECT or WHERE appears in FROM or a JOIN.

Practice Exercises

Write your answer in the editor and run it. Compare your output with the answer. Different SQL that gives the same correct output is also right.

SELECT ItemDesc, ItemCost
FROM Item
ORDER BY ItemCost ASC;

SELECT CustomerEmail, CustomerCity
FROM Customer
WHERE CustomerCountry = 'UK';

SELECT ItemDesc, ItemCost
FROM Item
WHERE ItemCost BETWEEN 15 AND 30;

SELECT ItemDesc
FROM Item
WHERE ItemDesc LIKE 'S%';

SELECT ArtistID, COUNT(*) AS Items
FROM Item
GROUP BY ArtistID
ORDER BY Items DESC;

SELECT ArtistID, ROUND(AVG(ItemCost), 2) AS AvgCost
FROM Item
GROUP BY ArtistID
HAVING AVG(ItemCost) > 100;

SELECT Artist.ArtistName, Item.ItemDesc
FROM Item
INNER JOIN Artist ON Item.ArtistID = Artist.ArtistID
ORDER BY Artist.ArtistName;

SELECT Purchase.PurchaseDate, Customer.CustomerEmail, Purchase.Quantity
FROM Purchase
INNER JOIN Customer ON Purchase.CustomerID = Customer.CustomerID
WHERE Customer.CustomerCountry = 'France'
  AND Purchase.PurchaseDate BETWEEN '2025-07-01' AND '2025-07-31';

UPDATE Customer
SET CustomerEmail = '[email protected]'
WHERE CustomerID = 9;

SELECT * FROM Customer WHERE CustomerID = 9;

SELECT Item.ItemID, Item.ItemDesc
FROM Item
LEFT JOIN Purchase ON Item.ItemID = Purchase.ItemID
WHERE Purchase.ItemID IS NULL;

SELECT Artist.ArtistName,
       SUM(Purchase.Quantity * Item.ItemCost) AS Revenue
FROM Purchase
INNER JOIN Item   ON Purchase.ItemID = Item.ItemID
INNER JOIN Artist ON Item.ArtistID = Artist.ArtistID
GROUP BY Artist.ArtistName
ORDER BY Revenue DESC;

CREATE TABLE Curio (
    Curio_ID   INT NOT NULL PRIMARY KEY,
    Curio_Name VARCHAR(50),
    Curio_Type VARCHAR(30)
);

CREATE TABLE Cabinet (
    Cabinet_ID          INT NOT NULL PRIMARY KEY,
    Cabinet_Description VARCHAR(100),
    Cabinet_Location    VARCHAR(50)
);

CREATE TABLE Collection (
    Cabinet_ID INT NOT NULL,
    Curio_ID   INT NOT NULL,
    PRIMARY KEY (Cabinet_ID, Curio_ID),
    FOREIGN KEY (Cabinet_ID) REFERENCES Cabinet(Cabinet_ID),
    FOREIGN KEY (Curio_ID) REFERENCES Curio(Curio_ID)
);

SELECT name FROM sqlite_master WHERE type = 'table';