Computer Science › Section J · Database systems › Topic J.3
Querying with SQL
SQL is the language almost every relational database understands. A few short, readable statements can filter, combine and summarise tables of any size.
What SQL is
SQL, short for Structured Query Language, is the standard language for relational databases. It grew out of IBM research in the 1970s and was standardised by ANSI in 1986 and by ISO soon after; nearly every relational DBMS speaks it, each with small differences of dialect. SQL is declarative, as "Declarative programming" explained: a query describes the rows wanted, and the DBMS's query optimiser decides how to find them. Keywords are not case-sensitive, and a statement ends with a semicolon. Every query here was run in SQLite, the DBMS behind Python's sqlite3 module.
A small library database
The examples use two tables. Book(BookId, Title, Genre, Year) holds five books: 1 Tide Tables (poetry, 2019), 2 The Glass Orchard (fiction, 2021), 3 Counting Stars (science, 2018), 4 Salt and Iron (fiction, 2015) and 5 Small Engines (science, 2022). Loan(LoanId, BookId, Borrower, Days) records six loans: loan 1, book 2 to Ana for 14 days; 2, book 2 to Kofi for 7; 3, book 3 to Ana for 21; 4, book 4 to Mei for 14; 5, book 2 to Mei for 10; and 6, book 3 to Kofi for 7. BookId is the primary key of Book and a foreign key in Loan.
SELECT, FROM and WHERE
The core query has three clauses. FROM names the tables, WHERE gives a condition each row must meet, and SELECT lists the columns to return. SELECT Title, Year FROM Book WHERE Genre = 'fiction'; returns The Glass Orchard, 2021 and Salt and Iron, 2015. In the terms of "The relational model", WHERE performs a SELECT operation and the SELECT clause performs a PROJECT, a confusing swap of names. Conditions combine with AND, OR and NOT, and compare with =, <> (not equal), <, <=, > and >=. Text values go in single quotes, and SELECT * returns every column.
Sorting, duplicates and limits
ORDER BY sorts the result: ORDER BY Year is ascending, and ORDER BY Year DESC puts the newest first. Without ORDER BY the row order is not guaranteed. Unlike projection, SQL keeps duplicate rows: SELECT Borrower FROM Loan; returns six names, Ana, Kofi and Mei twice each, while SELECT DISTINCT Borrower FROM Loan; returns each name once. To keep only the first few rows, SQLite, MySQL and PostgreSQL accept LIMIT: SELECT Title, Year FROM Book ORDER BY Year DESC LIMIT 2; gives Small Engines, 2022 and The Glass Orchard, 2021. The SQL standard spells this FETCH FIRST 2 ROWS ONLY, which SQLite does not accept.
Joining tables
A join combines rows from two tables. The standard form names both tables and the matching condition: FROM Loan JOIN Book ON Loan.BookId = Book.BookId. Each loan is paired with the book it refers to, and pairs that do not match are discarded; this is an inner join, and plain JOIN means INNER JOIN. Columns carry their table name, as in Book.Title, whenever a name could be ambiguous. An older style lists both tables after FROM and puts the matching condition in WHERE: FROM Loan, Book WHERE Loan.BookId = Book.BookId. Both return the same rows, but JOIN ... ON keeps joining and filtering apart.
Worked example: the long loans
List every loan longer than 10 days, longest first. Query: SELECT Book.Title, Loan.Borrower, Loan.Days FROM Loan JOIN Book ON Loan.BookId = Book.BookId WHERE Loan.Days > 10 ORDER BY Loan.Days DESC; Steps: (1) The join pairs each of the six loans with its book. (2) WHERE keeps loans 1, 3 and 4, of 14, 21 and 14 days; loan 5, of exactly 10 days, fails > 10. (3) The SELECT clause keeps three columns. (4) ORDER BY puts Counting Stars, Ana, 21 first, then The Glass Orchard, Ana, 14 and Salt and Iron, Mei, 14. (5) The two 14-day loans tie, so their order is not guaranteed; ORDER BY Loan.Days DESC, Book.Title would fix it.
Aggregates and grouping
Aggregate functions summarise many rows in one value: COUNT, SUM, AVG, MIN and MAX. SELECT COUNT(*), MAX(Days) FROM Loan; returns 6 and 21. AVG(Days) gives about 12.17 in SQLite, though some systems truncate the average of whole numbers to 12. GROUP BY splits the rows into groups and applies the aggregates to each: SELECT Borrower, SUM(Days) FROM Loan GROUP BY Borrower; gives Ana 35, Kofi 14 and Mei 24, in no guaranteed order. HAVING then filters whole groups, as WHERE filters single rows. Standard SQL requires every selected column to be grouped or inside an aggregate; SQLite is more lenient, which hides mistakes.
Worked example: loans per genre
Which genres have at least three loans? Query: SELECT Book.Genre, COUNT(*) AS Loans FROM Loan JOIN Book ON Loan.BookId = Book.BookId GROUP BY Book.Genre HAVING COUNT(*) >= 3; Steps: (1) The join gives six loan rows, each tagged with its book's genre. (2) GROUP BY forms two groups: fiction (loans 1, 2, 4 and 5) and science (loans 3 and 6). Poetry has no loans, so it forms no group. (3) COUNT(*) gives fiction 4 and science 2; AS Loans names that column. (4) HAVING keeps groups of at least 3, so the answer is fiction, 4. (5) Contrast WHERE: adding WHERE Loan.Days >= 10 before GROUP BY drops loans 2 and 6 first, leaving fiction 3 and science 1.
Outer joins and NULL
An inner join drops rows with no match, so books 1 and 5, never borrowed, vanish from any join with Loan. A LEFT JOIN keeps every row of the left table, filling the right table's columns with NULL where nothing matches. NULL marks a missing or unknown value; it is neither zero nor empty text. A comparison with NULL is never true, so WHERE Genre = NULL matches nothing; the tests are IS NULL and IS NOT NULL. Together they find unborrowed books: SELECT Book.Title FROM Book LEFT JOIN Loan ON Book.BookId = Loan.BookId WHERE Loan.LoanId IS NULL; returns Tide Tables and Small Engines.
Changing data and defining tables
INSERT INTO Book (BookId, Title, Genre, Year) VALUES (6, 'Paper Moons', 'poetry', 2024); adds a row. UPDATE Loan SET Days = 28 WHERE LoanId = 3; changes one row, and DELETE FROM Loan WHERE Borrower = 'Kofi'; removes two. CREATE TABLE defines columns, types and constraints: CREATE TABLE Loan (LoanId INTEGER PRIMARY KEY, BookId INTEGER NOT NULL REFERENCES Book(BookId), Borrower VARCHAR(30) NOT NULL, Days INTEGER CHECK (Days > 0)); The DBMS then rejects a 0-day loan or a missing borrower. SQLite enforces REFERENCES only after PRAGMA foreign_keys = ON; is run. Programs should pass values as parameters, never paste them into SQL text, as "Cybersecurity: attacks and defences" explains.
Common mistakes to avoid
Forgetting WHERE on an UPDATE or DELETE. Writing = NULL instead of IS NULL. Filtering an aggregate with WHERE, as in WHERE COUNT(*) >= 3; aggregate conditions belong in HAVING. Relying on row order without ORDER BY. Expecting SQL to drop duplicates as projection does; ask for DISTINCT. Counting matches after a LEFT JOIN with COUNT(*): an unmatched book still has one row, so it counts 1, while COUNT(Loan.LoanId) skips NULLs and correctly gives 0. Putting text values in double quotes; standard SQL uses single quotes for text and double quotes for names.
Key takeaways
FROM names tables, WHERE filters rows and SELECT picks columns; ORDER BY sorts, DISTINCT removes duplicates and LIMIT (or the standard FETCH FIRST) trims results. JOIN ... ON pairs matching rows; LEFT JOIN keeps unmatched rows of the left table with NULLs, tested by IS NULL. COUNT, SUM, AVG, MIN and MAX summarise; GROUP BY forms groups and HAVING filters them. INSERT, UPDATE and DELETE change data, CREATE TABLE sets constraints, and a missing WHERE changes every row.
Flashcards
Flashcard
Structured Query Language, the standard language of relational databases
Test your understanding
Question 1 of 5
Using the library tables, what does SELECT Title FROM Book WHERE Year >= 2019 AND Genre <> 'poetry'; return?
Question 2 of 5
Which clause keeps only the genres with at least three loans after grouping?
Question 3 of 5
Which query idea correctly finds the books that have never been borrowed?
Question 4 of 5
SELECT Borrower FROM Loan; returns six rows. How many rows does SELECT DISTINCT Borrower FROM Loan; return?
Question 5 of 5
After GROUP BY Borrower, what does SUM(Days) give for Ana?
