A3.3 Database programming

The property that a transaction takes the database from one valid state to another, so that every constraint and rule holds after the transaction commits.

SQL grew out of SEQUEL, a language built at IBM in the early 1970s for its System R research database. Oracle V2, released in June 1979, was one of the first commercial SQL products. SQL became an ANSI standard in 1986 and an ISO standard in 1987, and the standard has been revised many times since, most recently in 2023. Every major DBMS still adds its own extensions and leaves some features out, so a statement that runs on one system may need small changes on another.

The cinema database

The queries throughout run against one small, hypothetical database for a cinema called the Riverside Picturehouse. It has four tables. Each film is stored once in Film; each showing of a film at a date and time is a row in Screening; each customer is a row in Customer; and each purchase of seats is a row in Booking. In the table definitions, the primary key is in bold and each foreign key is marked with an asterisk.

The relationships are one-to-many: one film has many screenings, one screening has many bookings, and one customer makes many bookings. SeatsLeft is derived data (it could be worked out from the screen's capacity and the bookings), kept in the table so the box office can check availability without adding up bookings every time. Keeping it correct is one of the jobs of the transactions studied at HL.

The Film table holds seven films. The Last Lighthouse has no screenings yet, which matters when tables are joined.

The Screening table lists ten showings over three days. Dates are written YYYY-MM-DD and times HH:MM, so sorting them as text also sorts them in time order. Screening 106 is sold out.

The Customer table has six members. Elena Santos gave no email address, so her Email field holds NULL, the marker SQL uses for "no value". Farah Smithson has not booked anything yet.

The Booking table records twelve bookings. Seats is the number of seats bought in one booking, and BookedOn is the date it was made.

SQL and its data language types

SQL as a declarative language

SQL works differently from Python or Java. A Python or Java program lists the steps the computer must follow: start at the first item, test it, move to the next, and so on. An SQL statement describes the result that is wanted (which rows, which columns, in what order) and leaves the steps to the DBMS. Inside the DBMS, a component called the query optimiser selects how to find the rows, for example whether to read the whole table or to use an index.

The contrast is easiest to see with one task done both ways. The Python program finds every film longer than 120 minutes by visiting each element of two parallel lists in turn and testing it.

The program prints the two long films. It works, but the programmer chose the loop, the index variable and the order of the tests.

The SQL version states only the result: the title and running time of each film whose RunMins is greater than 120. There is no loop, no counter and no index variable, and the same statement still works when the table holds a million films and the query optimiser selects an index instead of reading every row.

Data definition language

SQL statements fall into groups by what they act on. Some statements change the structure of the database, the schema: which tables exist, which fields each has, their data types, keys and rules. Others work on the data stored inside that structure. The first group is the data definition language.

The CREATE TABLE statements that built the cinema database show DDL at work. Each statement names the table, lists every field with its data type (such as VARCHAR, DECIMAL and DATE), and adds rules called integrity constraints: PRIMARY KEY, FOREIGN KEY … REFERENCES, NOT NULL and CHECK.

The constraints are part of the structure, so they belong to DDL. Once CHECK (SeatsLeft >= 0) exists, the DBMS refuses any change that would make SeatsLeft negative, whichever program makes it. Email has no NOT NULL, so it is the only field that may be left empty.

Two more DDL statements change or remove structure that already exists. ALTER TABLE adds, removes or changes a field; DROP TABLE deletes a whole table, its structure and every row in it. The three statements show the pattern: adding a Phone field to Customer, then removing it, then removing a whole (hypothetical) Promotion table.

CREATE INDEX and CREATE VIEW are DDL statements too, because they add an index or a view to the schema rather than rows to a table.

Data manipulation language

DML is where almost all day-to-day work happens. The cinema's website runs SELECT statements to list screenings, INSERT to record a booking, UPDATE to change the seats left, and DELETE to cancel a booking. None of these changes a table's fields, data types or constraints. Some books put SELECT in a separate group called the data query language (DQL), because it only reads data; the more common two-way split, DDL and DML, counts SELECT as DML alongside the statements that change data.

Other parts of SQL

Two further groups of SQL statements complete the picture. The transaction control language (TCL) groups several DML statements into one unit that succeeds or fails as a whole (BEGIN TRANSACTION, COMMIT, ROLLBACK); TCL is HL only. The data control language (DCL) gives and removes permissions, for example GRANT SELECT ON Film TO box_office; and REVOKE. DCL shows that SQL also handles access control.

Querying one table

Queries, clauses and result sets

A SELECT statement is built from parts, each starting with a keyword. SELECT Title, RunMins names the columns, FROM Film names the table and WHERE RunMins > 120 sets the condition. Each part is a clause, and the order in which clauses are written is fixed: SELECT, FROM, JOIN … ON, WHERE, GROUP BY, HAVING, ORDER BY. Only SELECT and FROM are compulsory. SQL keywords are not case sensitive, but writing them in capitals and putting each clause on its own line makes a query far easier to read and check.

The answer to a query is not stored anywhere. The DBMS builds it as a temporary table for that query alone and discards it once the rows have been sent back.

Choosing columns: SELECT and DISTINCT

The SELECT clause lists the columns of the result set, separated by commas and in the order wanted. SELECT * returns every column. Listing only the columns needed is better practice: less data is sent, and the query keeps working in the same way if fields are later added to the table.

A result set can contain repeated rows. Asking for the Genre of every film returns seven rows, one per film, so Sci-fi and Drama each appear twice.

Adding DISTINCT straight after SELECT removes repeated rows from the result set, leaving each genre once.

DISTINCT applies to the whole row of the result set, not to the first column only. With two columns, a row is removed only if both values repeat another row. Sci-fi with rating 12 appears once (The Silent Orbit and Starfall collapse into one row), but Drama appears twice, because one drama is rated PG and the other 12.

Filtering rows: WHERE and relational operators

Most queries need only some rows. The WHERE clause holds a condition that the DBMS tests against every row; a row is kept in the result set only if the condition is true for it.

Conditions compare a field with a value or with another field, using the relational operators. Text and dates go in single quotes ('Drama', '2026-11-07'); numbers do not.

Comparisons work on text and dates as well as numbers. Text is compared character by character, so 'Chen' < 'Haddad' is true. Dates stored as YYYY-MM-DD compare correctly as text, because the most significant part (the year) comes first.

Combining conditions: AND, OR and NOT

Conditions are joined with the Boolean operators AND, OR and NOT, which work exactly as in logic and in Python and Java. AND needs both conditions to be true, OR needs at least one, and NOT reverses a condition. As in Boolean algebra, NOT is applied first, then AND, then OR. Forgetting that AND binds more tightly than OR is a common source of wrong results.

The cinema manager wants sci-fi or drama films shorter than 115 minutes. Written without brackets, the condition is read as "Sci-fi, or (Drama and shorter than 115 minutes)", so both long sci-fi films slip in.

Brackets make the OR happen first, so the length test applies to both genres and only the two short dramas remain.

NOT reverses the condition that follows it. To find the films suitable for a children's party (neither 12 nor 15), NOT is applied to a bracketed OR:

The same rows come from WHERE AgeRating <> '12' AND AgeRating <> '15', an example of De Morgan's law from subtopic A1.2.

Ranges: BETWEEN

BETWEEN low AND high tests whether a value lies in a range, and it is inclusive: both end values count. Price BETWEEN 8.00 AND 11.00 means Price >= 8.00 AND Price <= 11.00, so screenings priced exactly 8.00 and 11.00 are included.

BETWEEN works for dates and text too. ShowDate BETWEEN '2026-11-07' AND '2026-11-08' returns the six screenings on 7 and 8 November. The lower value must come first: BETWEEN 11.00 AND 8.00 returns no rows.

Pattern matching: LIKE and the % wildcard

Sometimes only part of a value is known: names beginning with "Smith", titles containing "night". The = operator compares whole values, so LastName = 'Smith%' looks for a surname that really is the six characters Smith% and returns nothing. Pattern matching needs the LIKE operator and a wildcard.

The position of % determines what is matched:

The query for customers whose surname begins with "Smith" returns both Daniel Smith and Farah Smithson, because % can match zero characters as well as many.

Standard SQL also has the _ wildcard, which matches exactly one character ('Sm_th' matches Smith and Smyth). Case sensitivity differs between systems: in SQLite and in MySQL with its usual settings, LIKE 'smith%' matches Smith, whereas in PostgreSQL it does not. Writing the pattern with the same capitals as the stored data works everywhere.

Sorting: ORDER BY

The rows of a result set have no guaranteed order unless the query asks for one. A DBMS often returns rows in the order they were stored, but it is free to return them in any order, especially after rows have been updated or an index has been used.

ORDER BY RunMins DESC lists films from longest to shortest. Leaving out ASC or DESC gives ascending order, so ORDER BY Title sorts titles from A to Z.

With several sort keys, the DBMS sorts by the first, and uses the second only to order rows that tie on the first. Each key has its own direction. Sorting by genre A–Z and then, within each genre, by length from longest to shortest puts The Last Lighthouse (110) above Paper Lanterns (104) inside Drama.

How a DBMS processes a query

A query is written in one order but processed in another. The DBMS first builds the rows it is working on (FROM and any JOIN), then filters them (WHERE), then forms groups (GROUP BY) and filters the groups (HAVING), and only then chooses the columns (SELECT) and sorts the result (ORDER BY). The processing order diagram shows the six steps.

The processing order explains several rules that otherwise look arbitrary. WHERE can only test single rows, because groups do not exist yet when it runs. ORDER BY can sort by a name given to a column in SELECT, because it runs afterwards. The DBMS may in practice carry out the work in a cleverer order, but the result is always the same as if it followed these steps.

Joining two tables

Many useful requests need data from more than one table. "Which films are showing on 7 November?" needs Title from Film and ShowDate from Screening. The two tables are linked by the foreign key Screening.FilmID, which holds the primary key of a film. A join uses that link to put matching rows side by side.

The Cartesian product

When two tables are listed in FROM with nothing to link them, SQL pairs every row of the first table with every row of the second.

Most of those 70 rows are nonsense. The first rows pair The Silent Orbit with every screening, including screening 103, which is a showing of Paper Lanterns:

A join keeps only the meaningful pairs: those where the foreign key in one row equals the primary key in the other. For the cinema tables, that means Film.FilmID = Screening.FilmID.

INNER JOIN … ON

In SQL the join is written in the FROM clause: FROM Film INNER JOIN Screening ON Film.FilmID = Screening.FilmID. The word INNER is optional, since JOIN on its own means an inner join. The matching diagram shows which pairs survive.

The full inner join returns ten rows: films with two screenings appear twice, and The Last Lighthouse is missing because no screening refers to it.

Once the tables are joined, every other clause works on the joined rows exactly as on a single table. The query for the screening times of sci-fi films filters on a Film column and sorts on two Screening columns.

DISTINCT is often needed after a join, because a row of one table can match several rows of the other. Listing the films showing on 7 November without DISTINCT would list a film twice if it had two screenings that day.

Qualified names and aliases

When both tables have a field with the same name, such as FilmID, the name alone is ambiguous and the DBMS reports an error. Writing Table.Field (a qualified name) says which one is meant. Qualifying every field in a join query, even unambiguous ones, also shows the reader where each value comes from.

Long table names make joins wordy, so SQL lets a query give a table or a computed column a short temporary name with AS.

The query lists screenings priced between 9.00 and 12.00 inclusive of films not rated 15, most expensive first, using the table aliases f and s.

Column aliases are useful with calculations. A SELECT list may contain arithmetic on fields, which is worked out for each row. The cost of each booking for 6 November is the number of seats times the ticket price, named Cost and used to sort the result.

Older join syntax and outer joins

An older style of join, still common in existing code, lists both tables in FROM (which on its own gives the Cartesian product) and puts the join condition in WHERE. This query returns the same title and date pairs as the INNER JOIN version of the sci-fi query:

The older form is still accepted by the major DBMSs and is worth recognising. The JOIN … ON form is clearer because it keeps the link between the tables apart from the filtering conditions, so a missing join condition is easier to spot.

An inner join drops rows that have no match, which is sometimes not wanted. A LEFT JOIN keeps every row of the first (left) table and fills the columns of the second with NULL where there is no match. The result shows the difference clearly: The Last Lighthouse now appears with no screening.

Query P lists the genres of the more expensive screenings.

Choosing which tables to join starts from the fields. List every field the request mentions, note which table holds each one, and join exactly those tables on their key. A request about bookings in one screen, for example, needs Booking for the seats and Screening for the screen number.

Grouping rows: GROUP BY and HAVING

Forming groups with GROUP BY

Many requests are about groups rather than single rows: how many films there are in each genre, how many screenings each film has. GROUP BY collects the rows that share a value into one group, and the query then returns one row per group.

The function COUNT(*) counts the rows in each group. It is the simplest of the aggregate functions, which all summarise a group into one value; the full set (AVG, COUNT, MAX, MIN, SUM) is HL only. The grouping diagram shows how the seven film rows become five result rows.

After grouping, each result row stands for a whole group. A column in SELECT must therefore have one value per group: either it is a grouping column (every row in the group has the same Genre) or it is a summary such as COUNT(*). Asking for Title in this query makes no sense, because the Drama group holds two different titles.

Filtering groups with HAVING

WHERE cannot filter groups, because it runs before the groups exist. A second filter, HAVING, runs after grouping and tests each group as a whole.

A query can use both filters. WHERE removes rows before grouping; HAVING removes groups after it. The query counts the evening screenings (starting at 18:00 or later) on each date, then keeps only the dates with at least two.

The WHERE clause first removes the daytime screenings. Of the five evening screenings that remain, three are on 6 November (101, 102, 104) and two on 7 November (106, 108); 8 November has none left, so it never forms a group. HAVING then keeps both groups, since each has at least two rows.

Grouping works on joined tables too. The query counts the screenings of each film and keeps the films shown at least twice. It groups by Film.FilmID as well as Film.Title, because two different films could share a title, while the key identifies each film uniquely.

Using SQL from Python and Java

Real applications rarely send SQL typed by a person. A program written in a general-purpose language (the host language) connects to the database through a driver, sends SQL statements as strings, and receives the result set as rows it can loop over. Python's standard library includes the sqlite3 module for SQLite databases; Java programs use JDBC (Java Database Connectivity) with a driver for the chosen DBMS.

The program lists the screenings of one genre. The genre is not pasted into the SQL string: the statement contains a placeholder ?, and the value is passed separately. This is a parameterised query, and it means a value typed by a user is always treated as data, never as part of the SQL command, which blocks SQL injection attacks.

Both versions print the same four rows. In Python, (genre,) is a tuple holding one value for the one placeholder, and each row is a tuple read by index. In Java, setString(1, genre) fills placeholder number 1, and rows.next() moves to the next row of the result set, returning false when none are left. The Java version needs the SQLite JDBC driver on the class path.

Changing data: INSERT, UPDATE and DELETE

SELECT only reads. Three DML statements change the stored data: INSERT INTO adds new rows, UPDATE … SET changes values in existing rows, and DELETE FROM removes rows. The examples run one after another on a copy of the cinema database; every other query uses the original tables.

Adding rows: INSERT INTO

INSERT INTO names the table, lists the fields being filled in brackets, and gives the values in the same order after VALUES. Text and dates go in single quotes.

A field left out of the list is set to NULL (or to a default value if the table defines one). Hugo Moreau joins without giving an email address:

The field list can be left out (INSERT INTO Customer VALUES (…)) if a value is given for every field in the order the table defines them. Naming the fields is safer: the statement still works if the table's fields are later reordered or a new optional field is added.

Changing values: UPDATE … SET

UPDATE names the table, SET gives each field its new value, and WHERE chooses the rows to change. A new value can be calculated from the old one. A 50-cent rise on every screening on 8 November changes two rows:

Several fields can be changed in one statement, separated by commas. When Elena Santos gives an email address and changes her surname, the WHERE clause uses the primary key so that exactly one row is changed:

Removing rows: DELETE FROM

DELETE FROM names the table and WHERE chooses the rows to remove. Whole rows are removed; to clear a single value, UPDATE sets it to NULL instead.

A condition can match many rows at once. DELETE FROM Customer WHERE MemberSince > '2026-11-01'; removed the two customers added earlier in the sequence (207 and 208) and nobody else.

How constraints protect the data

Every change is checked against the constraints in the table definitions, and a change that would break one is rejected with an error. This is how data integrity is enforced inside the database rather than trusted to each program that uses it. The table shows four rejected statements and the messages SQLite gave.

By contrast, DELETE FROM Film WHERE FilmID = 6; succeeds, because no screening refers to The Last Lighthouse. The rule protects only rows that other rows depend on. SQLite checks foreign keys only after PRAGMA foreign_keys = ON; has been run on the connection, an example of how DBMSs differ.

Indexes and the cost of updating data

What a database index is

Without help, the only way to find the customers called Smith is to read every row of Customer and test it, a linear search. With six customers that is instant; with ten million it means reading the whole table for every search. An index solves this the way the index at the back of a textbook does: a separate, sorted list of key values, each with a pointer to where the full entry is stored.

The index diagram shows an index on LastName beside the table it points into. The table is stored in CustomerID order; the index holds the same surnames sorted alphabetically.

Because the keys are sorted, the DBMS can find a value by repeatedly halving the range to search, as a binary search does, instead of checking every row. Real DBMSs store most indexes as a B-tree, a balanced tree whose wide nodes fit disk pages, so even a table of millions of rows needs only a few page reads to reach any key. The DBMS creates an index on every primary key automatically, which is why looking up a row by its ID is fast. Other indexes are added with a DDL statement:

An index is used automatically: queries are written exactly as before, and the query optimiser selects the index only when it helps. SQLite's EXPLAIN QUERY PLAN shows the decision. Before the index exists, a search by surname reads the whole table (SCAN); afterwards, it searches the index (SEARCH … USING INDEX).

The second row shows a limit of indexes. A pattern that starts with % can begin anywhere in the value, so the sorted order does not help and every entry must still be checked; SQLite reads the smaller index instead of the table, but it still scans. Indexes also speed up ORDER BY on the indexed column, since the values are already sorted, and joins, since the DBMS can look up the matching row for each foreign key.

Why indexed columns are slower to update

An index is a copy of some of the data, so every change to that data must also change the index. Inserting a customer adds an entry to the LastName index in the right sorted place; deleting one removes an entry; updating a surname moves the entry to a new position. A table with five indexes has six structures to change for every new row. The extra work makes writes slower, and the indexes take extra storage space.

The program measures both effects. It fills a table of 200,000 tickets twice, once with no extra index and once with three, then times 200 searches by customer on each table. SQLite runs in memory (":memory:") so that disk speed does not affect the times, and time.perf_counter() (Java: System.nanoTime()) reads a precise clock before and after each task.

One run of the Python version on a laptop printed the times shown. Exact times change from run to run and machine to machine, but the pattern was the same in every run, in both languages: with the three indexes, loading the table took about one and a half to two and a half times as long, while each search became thousands of times faster.

Without an index, each of the 200 searches read all 200,000 rows, about 22 ms per search. With the index, each search went straight to the right entries. An index therefore adds work to every insert, update and delete on its columns and saves work on every search that can use it. The best columns to index are those searched, joined or sorted often and changed rarely, such as a foreign key or a surname; a column that changes constantly and is seldom searched, such as SeatsLeft, is a poor choice.

Rebuilding and reorganising indexes

An index stays correct after every change, but not always efficient. Large numbers of inserts, updates and deletes leave gaps and half-empty pages in a B-tree, and the pages of the index end up scattered across the disk instead of stored in order. This is index fragmentation: the index takes more space than it needs and reading it takes more disk operations. The DBMS also keeps statistics about each index (how many rows, how many distinct values) to help the query optimiser choose a plan, and after a big change those statistics are out of date, so the optimiser may choose a slow plan.

The commands depend on the DBMS, a clear case of syntax varying between systems.

A full rebuild can lock the table or slow it down while it runs, so it is scheduled when few users are active. For a very large one-off load, a common alternative is to drop the indexes, load the data, and create the indexes again: building an index once from sorted data is faster than updating it millions of times. That approach is only safe if nothing else needs the indexes during the load.

Aggregate functions (HL only)

Summarising many rows in one value

A manager rarely needs every booking; they need totals, averages and extremes: how many seats were sold, what an average ticket costs, which film earns most. An aggregate function turns many rows into one summary value.

The average price is the sum of the ten prices divided by ten:

Several aggregates can appear in one SELECT, each with a column alias so the result has readable headings:

COUNT, NULL and the other functions

COUNT(*) counts rows, whatever they contain. COUNT(Email) counts only the rows where Email has a value, so it returns 5 because Elena's email is NULL. Every aggregate except COUNT(*) ignores NULL values: AVG divides by the number of non-NULL values, not by the number of rows. Asking how many customers have an email address is therefore one query with two counts:

MIN and MAX work on any type that can be ordered. On dates they give the earliest and latest (the first member joined on 2022-06-30, the newest on 2026-10-02); on text, the first and last in alphabetical order. A WHERE clause filters rows before they are summarised, so this query summarises only the six bookings made from 1 to 3 November inclusive:

The six bookings (3 to 8) hold 1 + 2 + 2 + 3 + 5 + 2 = 15 seats, and 15 ÷ 6 = 2.5. Some DBMSs (SQL Server, for example) return a whole number when AVG is applied to an integer column, which would cut 2.5 down to 2; multiplying by 1.0 first, as in AVG(Seats * 1.0), avoids that.

Aggregates on groups

Aggregate functions become a reporting tool when combined with GROUP BY: the function is then calculated once per group. Joined with Screening, the bookings give the number of seats sold and the ticket revenue for each film. Revenue for one booking is Seats * Price, and SUM adds those up within each film's group.

The result supports a decision. The Silent Orbit (film 1) sold the most seats, but Starfall (film 5) earned the most, because its screenings cost 12.50 and 10.00. Film 6 is missing: it has no screenings, so the inner join gives it no rows. Showing titles here needs a third table (Film), which adds a second INNER JOIN … ON clause written in the same way.

HAVING filters on the aggregate. The query finds the genres whose average ticket price is above 9.00, joining Film (for the genre) with Screening (for the price):

The Sci-fi average is (9.50 + 11.00 + 12.50 + 10.00) ÷ 4 = 43.00 ÷ 4 = 10.75. Drama (8.00), Comedy (7.50) and Animation (6.00) fail the HAVING condition.

A daily report for the cinema's owner combines COUNT, SUM and a calculated SUM for each date:

Sunday 8 November has sold only 2 seats so far, worth 20.00 in total: a signal to advertise those screenings or move them to a smaller screen. Reports like this, run regularly over far larger tables, are the starting point for the data warehouses and business intelligence tools of subtopic A3.4.

The rules for grouped queries

Three rules prevent most errors in grouped queries.

1. Every column in SELECT must be either in GROUP BY or inside an aggregate function. Each result row stands for a group, so a plain column must have one value per group.

2. Aggregates cannot go in WHERE. WHERE filters rows before groups exist; conditions on an aggregate go in HAVING.

3. Aggregates ignore NULL, except COUNT(*), which counts rows.

Rule 1 is enforced strictly by PostgreSQL and SQL Server, which reject SELECT Genre, Title, MAX(RunMins) FROM Film GROUP BY Genre;. SQLite and some MySQL settings accept it and fill Title with a value from one row of each group, which can mislead. Following the rule keeps a query correct on every system.

Views (HL only)

A stored query that behaves like a table

The box office runs the same join of Film and Screening dozens of times a day. Instead of writing it every time, the join can be saved in the database under a name and then used as if it were a table.

The view ScreeningGuide packages the join, keeping only the columns the box office needs:

A query on the view needs no join and no knowledge of foreign keys. Listing Sunday's screenings becomes a one-table query:

The tables that actually hold the data (Film, Screening, Customer, Booking) are called base tables, to distinguish them from views. A view can be built on base tables, on other views, or on both.

Virtual and materialised views

There are two ways a DBMS can provide a view's rows, and the difference affects freshness, speed and storage. The view diagram compares them.

A virtual view uses no storage for rows and can never be out of date. Its cost is time: the full query, joins and all, runs every time the view is read. For a view that joins and aggregates millions of rows, that can take seconds or minutes per use.

PostgreSQL creates one with CREATE MATERIALIZED VIEW FilmSales AS SELECT …; and brings it up to date with REFRESH MATERIALIZED VIEW FilmSales;. SQLite and MySQL have no materialised views; the same effect is produced by saving a query's result in a table. The experiment uses that method in SQLite to show staleness. FilmSales is a virtual view of seats sold per film; FilmSalesSnapshot is a stored copy made from it. A new booking of 3 seats for Pixel Pets (film 7) is then added.

The result of the virtual view included the new booking immediately, while the stored copy still held 4 until it was rebuilt. Whether staleness matters depends on the use. A monthly sales dashboard can be refreshed overnight; the seat count shown to a customer at the moment of booking must never be stale.

What views are for

Views serve several purposes at once, and one view often serves more than one.

• Hiding complexity and simplifying queries. ScreeningGuide hides a join; a user writes FROM ScreeningGuide without knowing how the tables are linked. Long queries can be built from simpler views.

• Data consistency. If "revenue" is defined once in a view, every report that uses the view calculates it the same way, instead of each analyst writing a slightly different formula.

• Data independence. Programs written against a view keep working when the base tables change. If Film were split into two tables, only the view's defining query would need rewriting; every program using ScreeningGuide would be unaffected. This is logical data independence.

• Security. A view can expose only some columns and rows. Box office staff can be given permission to read a view of bookings with customer names but not email addresses, and no permission on the base table at all.

• Performance. A materialised view stores the result of an expensive query, so frequent reads are fast. (A virtual view brings no speed gain: its query still runs in full.)

• Read-only or updatable data. A view can let users read data without being able to change it, or, if it is simple enough, allow changes that pass through to the base table.

The security role in practice: the CustomerList view shows names but never emails.

A view is easiest to update when each of its rows corresponds to exactly one row of one base table, so every change to the view maps to exactly one stored row. CustomerList meets that condition and is automatically updatable in PostgreSQL, MySQL and SQL Server. FilmSales does not: its row "film 7, 7 seats" summarises two bookings, and changing it to 9 has no meaning in the base tables. Views that join tables fall in between and the rules differ by DBMS: PostgreSQL refuses changes through them unless extra rules are written, while MySQL and SQL Server accept a change that affects the columns of only one base table. SQLite treats every view as read-only (unless a trigger is written to say how a change should be applied), so UPDATE CustomerList SET LastName = 'Smyth' WHERE CustomerID = 204; fails with "cannot modify CustomerList because it is a view". Read-only access is also a deliberate design choice: a user given permission only to SELECT from a report view cannot use it to change the data.

Transactions and ACID (HL only)

Why one change needs several statements

Booking two seats for screening 104 changes two tables: a row is added to Booking, and SeatsLeft for the screening drops by 2. Both changes describe one real event, so they must happen together. If the program inserts the booking and then crashes before updating SeatsLeft, the database claims more free seats than there are; if the update happens without the insert, seats vanish. Either way the data no longer matches reality, a failure of data consistency.

The solution is to group the statements into one transaction, a single logical unit of work that the DBMS treats as indivisible. The guarantees a DBMS gives for transactions are summed up by the acronym ACID, coined by Andreas Reuter and Theo Härder in 1983, building on Jim Gray's earlier work on transactions.

Atomicity

Atomicity is what makes the booking safe. The DBMS records every change a transaction makes so that it can undo them. When something goes wrong (a constraint is broken, the program detects a problem, the power fails), every change since the start of the transaction is reversed and the database is back exactly where it was. "Atomic" means "cannot be split". The same word describes the atomic values required by first normal form in subtopic A3.2, but the two ideas are unrelated: an atomic value cannot be divided into smaller data items, while an atomic transaction cannot be partly carried out.

Consistency

The DBMS enforces consistency through the constraints in the schema: primary keys, foreign keys, NOT NULL and CHECK. Rules that are too complex for a constraint (for example, "the number of seats booked plus SeatsLeft must equal the screen's capacity") are the responsibility of the transaction's own logic. During a transaction, the database may pass through states that break a rule, such as the moment after the booking is inserted but before SeatsLeft is updated. Consistency requires only that the state at COMMIT is valid; isolation makes sure that no other user reads the in-between states.

Isolation

When many customers book at the same moment, their transactions run concurrently. Without control, they can interfere. The isolation diagram follows two customers each trying to book 2 of the last 3 seats for screening 104, in a program that reads SeatsLeft, checks it, and then writes the new value.

On the left, each transaction reads 3, finds that 2 seats are available, and writes 3 − 2 = 1. Four seats have been sold for three places, and SeatsLeft says 1: Amira's update has been overwritten and lost. This is a lost update, one form of race condition.

On the right, Amira's transaction locks the row while it works. Ben's transaction must wait; when it reads the row, the value is the committed 1, the check fails, and his transaction rolls back cleanly. The outcome is exactly what would have happened if Amira had booked first and Ben second. This is the concurrency control introduced in subtopic A3.1. Stronger isolation is safer but makes transactions wait for one another, so DBMSs offer several isolation levels that trade safety for speed; choosing between them is a decision for the database administrator.

Durability

Changes held only in RAM would be lost in a power cut, so a DBMS makes them durable by writing them to non-volatile storage before it confirms a commit. The usual method is a log.

The log serves both atomicity and durability. After a crash, the DBMS reads the log: a transaction with a commit record is redone in full, even if its changes had not yet reached the data files, and a transaction with no commit record is undone. A booking that was committed one millisecond before the power failed is kept; one that had not committed disappears entirely.

Transaction control language

The name "transaction" is shared with the general idea from subtopic A3.1: a transaction is the unit of work, and TCL is the set of commands that marks where that unit begins and ends. The successful booking of two seats for screening 104 by Farah (customer 206) runs as a single transaction:

After the commit, screening 104 has 1 seat left and booking 13 is stored. Both changes became visible to other users at the same instant, at COMMIT.

Wei (customer 203) then tries to book 2 seats for the same screening. The insert succeeds, but the update would make SeatsLeft equal to 1 − 2 = −1, which breaks the CHECK constraint. SQLite printed the error, and a SELECT inside the still-open transaction showed that booking 14 was still there: the failed statement was undone, but the earlier insert was not.

After ROLLBACK, booking 14 no longer exists, the Booking table holds 13 rows, and SeatsLeft is still 1. The flowchart of a transaction shows the two possible endings.

The SQLite run shows why the program must decide to roll back: a DBMS that rejects one statement does not always cancel the whole transaction by itself. In application code, the decision sits in exception handling. The Python and Java programs try both statements, commit if both succeed, and roll back if either raises an error.

The first booking commits; the second rolls back, so the database keeps 13 bookings and 1 free seat. Python's sqlite3 is opened with isolation_level=None so that the program sends BEGIN TRANSACTION, COMMIT and ROLLBACK itself. JDBC uses method calls instead of TCL statements: setAutoCommit(false) starts a transaction, and commit() and rollback() end it. Java's error message also names SQLite's error code.

Exam focus

Database programming is examined in Paper 1, Section A, usually inside a question built around one scenario with sample tables printed in the paper. HL papers add extra parts on aggregates, views and transactions to the same scenario as the SL parts.

Questions may also ask you to state the output of a given query, which is answered by tracing it in processing order, or to correct a faulty query.

Expect the sample data to be small enough to trace by hand. Write SQL with one clause per line, keywords in capitals and text values in single quotes; small syntax slips are not normally penalised if the logic is clear, but a missing join condition or a condition in the wrong clause is.

The most common lost marks in SQL questions come from putting a condition in the wrong place. Before writing a condition, ask whether it is about one row (WHERE) or about a group or a total (HAVING), and whether it compares whole values (=) or a pattern (LIKE).

Practice questions

1. State one statement from the data definition language and one from the data manipulation language, and outline what each acts on. [2]

2. State the result set of the query. [2]

SELECT Title FROM Film WHERE Title LIKE 'The%' OR AgeRating = 'U' ORDER BY Title;

3. Construct an SQL query that lists the title, genre and running time of every film that is not a drama and lasts from 90 to 120 minutes inclusive, shortest first. [4]

4. Construct an SQL query that lists the first name of each customer who booked screening 101 or screening 106, with the number of seats and the booking date, in order of booking date. [5]

5. A student writes SELECT Film.Title, Screening.ShowDate FROM Film, Screening; to list each film with its screening dates. Explain why the output is wrong, and construct a corrected query. [4]

6. Construct a statement that increases by 1.00 the price of every screening in screen 3, and explain what would happen if the WHERE clause were left out. [3]

7. Explain one advantage and one disadvantage of adding an index on Booking.CustomerID. [4]

8. (HL only) Construct an SQL query that shows the customer ID and the total amount spent (seats × price) by each customer who has spent more than 50.00, the highest spender first. [5]

9. (HL only) Distinguish between a virtual view and a materialised view, and suggest, with a reason, which suits a revenue report that the owner reads each morning. [4]

10. (HL only) Describe how the isolation property of transactions prevents two customers from buying the same last seat. [3]

Summary

• SQL is a declarative language: a statement says what result is wanted and the DBMS works out how to produce it; Python and Java are imperative.

• DDL (CREATE, ALTER, DROP) defines and changes the structure of the database; DML (SELECT, INSERT, UPDATE, DELETE) reads and changes the data.

• A query is written SELECT … FROM … JOIN … ON … WHERE … GROUP BY … HAVING … ORDER BY … and processed in the order FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

• WHERE filters rows with relational operators, AND, OR (with brackets), NOT, inclusive BETWEEN and LIKE with the % wildcard; DISTINCT removes repeated result rows; ORDER BY sorts, ascending by default.

• An inner join pairs rows whose foreign key matches the primary key; without the join condition the result is the Cartesian product.

• GROUP BY returns one row per group; HAVING filters groups, after grouping, while WHERE filters rows before it.

• INSERT INTO, UPDATE … SET and DELETE FROM change data; constraints reject changes that would break integrity; a missing WHERE changes every row.

• An index is a sorted structure with pointers that speeds searching, joining and sorting but slows every insert, update and delete on its columns and uses storage; after large changes, indexes are rebuilt or reorganised.

• (HL) AVG, COUNT, MAX, MIN and SUM summarise a table or each group; they ignore NULL except COUNT(*), and conditions on them go in HAVING.

• (HL) A view is a stored query used like a table: a virtual view is always current but recomputed on each use; a materialised view stores its result, is fast but can be stale. Views hide complexity, give consistency, independence and security, and may be updatable or read-only.

• (HL) Transactions group statements into one unit; ACID (atomicity, consistency, isolation, durability) guarantees reliable processing, controlled by BEGIN TRANSACTION, COMMIT and ROLLBACK, with a transaction log for recovery.

Self-check

• To do:

  I can explain the difference between a declarative and an imperative language, using SQL and Python or Java.

• To do:

  I can classify any SQL statement as DDL or DML and say what it acts on.

• To do:

  I can write a SELECT query with DISTINCT, WHERE, BETWEEN, LIKE, AND, OR, NOT and ORDER BY from an English request.

• To do:

  I can use brackets to control how AND and OR combine.

• To do:

  I can join two tables with INNER JOIN … ON, qualifying field names.

• To do:

  I can explain why a missing join condition produces a Cartesian product and how many rows it has.

• To do:

  I can group rows with GROUP BY and filter groups with HAVING COUNT(*), and say when to use WHERE instead.

• To do:

  I can trace a query on sample data and write its result set in the right order.

• To do:

  I can write INSERT INTO, UPDATE … SET and DELETE FROM statements and predict which constraint stops an invalid change.

• To do:

  I can explain why an index speeds up searches but slows down updates, and why indexes are rebuilt after major changes.

• To do:

  I can use AVG, COUNT, MAX, MIN and SUM on a table and on groups, including with a join. (HL only)

• To do:

  I can explain the difference between COUNT(*) and COUNT(column). (HL only)

• To do:

  I can create a view and compare virtual and materialised views on freshness, speed and storage. (HL only)

• To do:

  I can describe each role of a view, including security and updatability. (HL only)

• To do:

  I can describe each ACID property and write a transaction with BEGIN TRANSACTION, COMMIT and ROLLBACK. (HL only)