Commands, clauses and popular functions at a glance.
Covers all four sections of the SQL course.
JUMP TO A TOPIC
SQL examples use SQLite unless labelled otherwise. Each row is an independent
snippet: read it on its own rather than pasting every row together.
Use the bookmark links to find what you need. Query examples use
Books(BookID, Title, Price, CategoryID) and
Categories(CategoryID, CategoryName). The setup below creates sample data.
01 | RELATIONAL FOUNDATIONS
Relational foundations reference
Item
Use and example
Table, record and field
A table stores one kind of entity. A record is a row; a field is a
column.
-- Books is a table. Each row is one book.
SELECT BookID, Title, Price
FROM Books;
Primary key
Uniquely identifies each record. Explicit NOT NULL is useful in SQLite
table definitions.
CREATE TABLE Categories (
CategoryID INTEGER PRIMARY KEY,
CategoryName TEXT NOT NULL UNIQUE
);
Foreign key
Stores a reference to a parent key. Multiple books can reference one
category.
For exact money in SQLite, integer minor units avoid floating-point
rounding. Dates can use ISO text.
CREATE TABLE Payments (
PaymentID INTEGER PRIMARY KEY,
AmountPence INTEGER NOT NULL,
PaidOn TEXT NOT NULL, -- YYYY-MM-DD
Confirmed INTEGER NOT NULL
CHECK (Confirmed IN (0, 1))
);
Examples use SQLite syntax. In MySQL/MariaDB, DECIMAL is available for exact
money; DATE and BOOLEAN declarations and generated-key syntax differ. SQLite
VARCHAR(n) does not enforce a length limit by itself.
Return matching rows from both tables. Join on the related key fields.
SELECT b.Title, c.CategoryName
FROM Books AS b
INNER JOIN Categories AS c
ON b.CategoryID = c.CategoryID;
LEFT JOIN
Keep every left-hand row, including books without a category.
Unmatched right-hand fields are NULL.
SELECT b.Title, c.CategoryName
FROM Books AS b
LEFT JOIN Categories AS c
ON b.CategoryID = c.CategoryID;
Find missing matches
Test a non-NULL key on the right to find unmatched rows.
SELECT b.Title
FROM Books AS b
LEFT JOIN Categories AS c
ON b.CategoryID = c.CategoryID
WHERE c.CategoryID IS NULL;
Filtering a LEFT JOIN
Place a right-table condition in ON when left rows must remain even
without a qualifying match.
SELECT c.CategoryName, b.Title
FROM Categories AS c
LEFT JOIN Books AS b
ON b.CategoryID = c.CategoryID
AND b.Price < 10;
A join can return several rows for one entity when it has multiple matches.
A WHERE condition on the right-hand table can remove unmatched LEFT JOIN
rows.
Produce one summary row per group. Group the nonaggregated selected
columns.
SELECT CategoryID, COUNT(*) AS BookCount
FROM Books
GROUP BY CategoryID;
HAVING
Filter groups after aggregation. WHERE filters individual input rows.
SELECT CategoryID, COUNT(*) AS BookCount
FROM Books
WHERE Price >= 5
GROUP BY CategoryID
HAVING COUNT(*) >= 2
ORDER BY BookCount DESC, CategoryID;
Count with LEFT JOIN
Count the child key, not COUNT(*), so empty categories show 0.
SELECT c.CategoryID, c.CategoryName,
COUNT(b.BookID) AS BookCount
FROM Categories AS c
LEFT JOIN Books AS b
ON b.CategoryID = c.CategoryID
GROUP BY c.CategoryID, c.CategoryName
ORDER BY c.CategoryID;
Group changes into one transaction and commit them together.
BEGIN TRANSACTION;
UPDATE Books SET Price = 10 WHERE BookID = 1;
UPDATE Books SET Price = 12 WHERE BookID = 2;
COMMIT;
ROLLBACK
Undo changes in the current uncommitted transaction. It cannot undo a
completed COMMIT.
BEGIN TRANSACTION;
UPDATE Books SET Price = 0;
ROLLBACK;
Error handling
In application code, catch errors, roll back and report failure. A
statement error does not always end the transaction.
-- SQL transaction outline:
BEGIN TRANSACTION;
-- Execute related changes here.
-- Application chooses COMMIT on success
-- or ROLLBACK on failure.
ROLLBACK;
Exact transaction behaviour depends on the database and driver. Check
affected rows and business rules before committing; a successful query
affecting zero rows may still mean the requested operation failed.
? is a Python sqlite3 placeholder. Bind values separately; do not
substitute text into SQL. This snippet is Python.
title = "Python Basics"
rows = connection.execute(
"SELECT BookID, Title FROM Books WHERE Title = ?",
(title,)
).fetchall()
LIKE parameter
Include the wildcard in the bound value. This snippet is Python using
sqlite3.
prefix = "Python"
rows = connection.execute(
"SELECT Title FROM Books WHERE Title LIKE ?",
(prefix + "%",)
).fetchall()
Identifiers
Parameters bind values, not table names, column names or SQL keywords.
Use an allowlist for chosen identifiers. This snippet is Python.
allowed = {"title": "Title", "price": "Price"}
choice = "price"
column = allowed[choice]
rows = connection.execute(
f"SELECT Title, Price FROM Books ORDER BY {column}"
).fetchall()
Least privilege
SQLite has no GRANT/REVOKE user system. Restrict the database file and
application access. The SQL below is MySQL/MariaDB only.
GRANT SELECT ON cafe.Books
TO 'reader'@'localhost';
REVOKE SELECT ON cafe.Books
FROM 'reader'@'localhost';
Never concatenate untrusted values into SQL. Placeholder styles differ by
driver. Parameter binding prevents values becoming SQL instructions;
authentication, authorisation and validation are still needed.
Separate members, books and loans. This links each loan to one member
and one book.
CREATE TABLE Loans (
LoanID INTEGER PRIMARY KEY,
MemberID INTEGER NOT NULL,
BookID INTEGER NOT NULL,
BorrowedOn TEXT NOT NULL,
ReturnedOn TEXT,
FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
FOREIGN KEY (BookID) REFERENCES Books(BookID)
);
Current loans
A missing return date represents an active loan in this design.
SELECT l.LoanID, m.Email, b.Title
FROM Loans AS l
INNER JOIN Members AS m
ON l.MemberID = m.MemberID
INNER JOIN Books AS b
ON l.BookID = b.BookID
WHERE l.ReturnedOn IS NULL;
Record a return
Update the existing loan rather than deleting borrowing history.
UPDATE Loans
SET ReturnedOn = '2026-11-02'
WHERE LoanID = 1
AND ReturnedOn IS NULL;
Books without active loans
Treat each Books row as one lendable copy for this small project.
SELECT b.BookID, b.Title
FROM Books AS b
WHERE NOT EXISTS (
SELECT 1 FROM Loans AS l
WHERE l.BookID = b.BookID
AND l.ReturnedOn IS NULL
);
For a larger library, separate book titles from physical copies. Enforce
borrowing rules in transactions and database constraints where possible,
rather than relying on a page display to prevent duplicate loans.