LEARN TO CODE | QUICK REFERENCE

SQL CHEAT SHEET

Commands, clauses and popular functions at a glance. Covers all four sections of the SQL course.

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.

CREATE TABLE ReadingLists (
  ListID INTEGER PRIMARY KEY,
  CategoryID INTEGER NOT NULL,
  FOREIGN KEY (CategoryID)
    REFERENCES Categories(CategoryID)
);
Composite key A key formed from multiple fields. A linking table models a many-to-many relationship.

CREATE TABLE BookTags (
  BookID INTEGER NOT NULL,
  Tag TEXT NOT NULL,
  PRIMARY KEY (BookID, Tag),
  FOREIGN KEY (BookID) REFERENCES Books(BookID)
);
Comments and literals Use single quotes for text, with two single quotes for an apostrophe. End statements with a semicolon.

-- A single-line comment
/* A longer comment */
SELECT 'O''Brien' AS Surname;

Back to topic links

02 | TYPES AND CONSTRAINTS

Types and constraints reference
Item Use and example
Common types INTEGER for whole numbers, REAL for approximate decimals, TEXT for text. SQLite uses type affinity.

CREATE TABLE Measurements (
  MeasurementID INTEGER PRIMARY KEY,
  Label TEXT NOT NULL,
  Reading REAL
);
NOT NULL Require a value. This does not reject an empty string.

CREATE TABLE Clubs (
  ClubID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL
);
UNIQUE Prevent duplicate non-NULL values. NULL behaviour varies by database.

CREATE TABLE Members (
  MemberID INTEGER PRIMARY KEY,
  Email TEXT NOT NULL UNIQUE
);
CHECK and DEFAULT CHECK tests a rule. DEFAULT supplies an omitted value, not an explicitly supplied NULL.

CREATE TABLE Stock (
  StockID INTEGER PRIMARY KEY,
  Quantity INTEGER NOT NULL DEFAULT 0
    CHECK (Quantity >= 0)
);
SQLite foreign keys Enable enforcement on every connection, before starting a transaction.

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys; -- expect 1
Money, dates and booleans 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.

Back to topic links

03 | SELECT AND WHERE

Select and where reference
Item Use and example
SELECT / FROM Choose named columns from a table.

SELECT Title, Price
FROM Books;
SELECT * Return all columns. Named columns are clearer in application queries.

SELECT *
FROM Books;
WHERE Keep only rows whose condition is true.

SELECT Title, Price
FROM Books
WHERE Price < 10;
Comparisons SQL uses = for equality and <> for not equal.

SELECT Title
FROM Books
WHERE CategoryID <> 2;
-- Also: <, >, <=, >=
AND / OR / NOT Combine conditions. Parentheses make the intended grouping clear.

SELECT Title
FROM Books
WHERE (CategoryID = 1 OR CategoryID = 2)
  AND NOT Price > 15;
AS aliases Give a result column a readable label. This does not rename the stored column.

SELECT Title AS BookTitle,
       Price AS PriceInPounds
FROM Books;

Back to topic links

04 | ORDER AND LIMIT

Order and limit reference
Item Use and example
ORDER BY Sort results explicitly. Without ORDER BY, row order is not guaranteed.

SELECT Title, Price
FROM Books
ORDER BY Price ASC, BookID ASC;
DESC Sort descending. A second unique key resolves ties predictably.

SELECT Title, Price
FROM Books
ORDER BY Price DESC, BookID ASC;
LIMIT Return at most this many rows. Supported by SQLite and MySQL/MariaDB.

SELECT Title, Price
FROM Books
ORDER BY Price DESC, BookID ASC
LIMIT 3;
OFFSET Skip rows after sorting. Useful for small paginated results.

SELECT BookID, Title
FROM Books
ORDER BY BookID
LIMIT 3 OFFSET 3;

Back to topic links

05 | PATTERNS, SETS AND NULL

Patterns, sets and null reference
Item Use and example
LIKE % matches zero or more characters; _ matches one character. Case behaviour depends on the database.

SELECT Title
FROM Books
WHERE Title LIKE 'Python%';
One-character wildcard This matches a four-character title beginning with C.

SELECT Title
FROM Books
WHERE Title LIKE 'C___';
IN / NOT IN Test membership in a list. Avoid NULL in NOT IN lists or subquery results.

SELECT Title
FROM Books
WHERE CategoryID IN (1, 2);
BETWEEN Both endpoints are included.

SELECT Title, Price
FROM Books
WHERE Price BETWEEN 5 AND 15;
IS NULL / IS NOT NULL NULL means missing or unknown. Do not compare it with = NULL.

SELECT Title
FROM Books
WHERE CategoryID IS NULL;
COALESCE Return the first non-NULL value. It does not replace an empty string.

SELECT Title,
       COALESCE(CategoryID, 0) AS CategoryOrZero
FROM Books;

Back to topic links

06 | DISTINCT AND CALCULATIONS

Distinct and calculations reference
Item Use and example
DISTINCT Remove duplicate result rows across the selected columns.

SELECT DISTINCT CategoryID
FROM Books;
Calculated columns Calculate a result without changing stored values.

SELECT Title,
       Price * 1.10 AS IncreasedPrice
FROM Books;
ROUND Round a calculated number. Display formatting may still need application code.

SELECT Title,
       ROUND(Price * 0.9, 2) AS SalePrice
FROM Books;
Decimal division In SQLite, use a real operand to avoid integer division.

SELECT 5 / 2 AS IntegerResult,
       5.0 / 2 AS DecimalResult;
-- 2 and 2.5
CASE Return a label based on conditions. This is an expression, not a procedural IF statement.

SELECT Title,
       CASE
         WHEN Price < 10 THEN 'Budget'
         ELSE 'Standard'
       END AS PriceBand
FROM Books;

Back to topic links

07 | INNER JOIN AND LEFT JOIN

Inner join and left join reference
Item Use and example
INNER JOIN 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.

Back to topic links

08 | AGGREGATE FUNCTIONS

Aggregate functions reference
Item Use and example
COUNT(*) / COUNT(field) COUNT(*) counts rows. COUNT(field) counts non-NULL values.

SELECT COUNT(*) AS TotalBooks,
       COUNT(CategoryID) AS CategorisedBooks
FROM Books;
COUNT(DISTINCT field) Count different non-NULL values.

SELECT COUNT(DISTINCT CategoryID) AS CategoriesUsed
FROM Books;
SUM and AVG Total or average non-NULL numeric values. AVG ignores missing values.

SELECT SUM(Price) AS TotalPrice,
       AVG(Price) AS AveragePrice
FROM Books;
MIN and MAX Return the smallest and largest non-NULL values.

SELECT MIN(Price) AS Cheapest,
       MAX(Price) AS MostExpensive
FROM Books;
Empty results COUNT returns 0 when no rows match. SUM and AVG return NULL.

SELECT COALESCE(SUM(Price), 0) AS Total
FROM Books
WHERE Price > 1000;

Back to topic links

09 | GROUP BY AND HAVING

Group by and having reference
Item Use and example
GROUP BY 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;

Back to topic links

10 | SIMPLE SUBQUERIES

Simple subqueries reference
Item Use and example
Scalar subquery Compare against one calculated value.

SELECT Title, Price
FROM Books
WHERE Price > (SELECT AVG(Price) FROM Books);
IN subquery Compare against a one-column result set.

SELECT Title
FROM Books
WHERE CategoryID IN (
  SELECT CategoryID
  FROM Categories
  WHERE CategoryName = 'Computing'
);
EXISTS Test whether a related row exists.

SELECT c.CategoryName
FROM Categories AS c
WHERE EXISTS (
  SELECT 1
  FROM Books AS b
  WHERE b.CategoryID = c.CategoryID
);
NOT EXISTS Find parent records without children, avoiding the NULL trap of NOT IN.

SELECT c.CategoryName
FROM Categories AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM Books AS b
  WHERE b.CategoryID = c.CategoryID
);

Back to topic links

11 | INSERT, UPDATE AND DELETE

Insert, update and delete reference
Item Use and example
INSERT Name the columns receiving values.

INSERT INTO Books (BookID, Title, Price, CategoryID)
VALUES (10, 'New Book', 9.50, 1);
Multiple records Each row must match the named column list.

INSERT INTO Books (BookID, Title, Price, CategoryID)
VALUES (11, 'Book A', 8.00, 1),
       (12, 'Book B', 12.00, 2);
Preview a change Run a SELECT with the intended WHERE condition before editing.

SELECT BookID, Title, Price
FROM Books
WHERE BookID = 1;
UPDATE Change matching records. Without WHERE, every row is updated.

UPDATE Books
SET Price = 11.50
WHERE BookID = 1;
DELETE Remove matching records. Without WHERE, all rows are targeted.

DELETE FROM Books
WHERE BookID = 1;

Back to topic links

12 | TRANSACTIONS

Transactions reference
Item Use and example
BEGIN / COMMIT 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.

Back to topic links

13 | PARAMETERS AND PERMISSIONS

Parameters and permissions reference
Item Use and example
Bound values ? 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.

Back to topic links

14 | LIBRARY PROJECT PATTERNS

Library project patterns reference
Item Use and example
Related tables 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.

Back to topic links

TRY IT | SAMPLE DATABASE

Run this once in a new, empty SQLite practice database. Then try individual query examples. Reset the database after changing its data.

Show the sample database setup

PRAGMA foreign_keys = ON;
CREATE TABLE Categories (
  CategoryID INTEGER PRIMARY KEY,
  CategoryName TEXT NOT NULL UNIQUE
);
CREATE TABLE Books (
  BookID INTEGER PRIMARY KEY,
  Title TEXT NOT NULL,
  Price REAL NOT NULL CHECK (Price >= 0),
  CategoryID INTEGER,
  FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);
INSERT INTO Categories (CategoryID, CategoryName)
VALUES (1, 'Computing'), (2, 'Science'), (3, 'Art');
INSERT INTO Books (BookID, Title, Price, CategoryID)
VALUES (1, 'Python Basics', 8.50, 1),
       (2, 'Web Design', 12.00, 1),
       (3, 'Space', 15.00, 2),
       (4, 'Code', 6.00, 1),
       (5, 'Unsorted Stories', 5.00, NULL);

COMMON MIX-UPS

  • WHERE filters rows; HAVING filters groups.
  • IS NULL tests for missing values; = NULL does not.
  • BETWEEN includes both endpoints.
  • COUNT(*) counts rows; COUNT(column) ignores NULL.
  • ORDER BY is needed for a guaranteed result order.
  • A join may return multiple matches for one record.
  • Use WHERE carefully with UPDATE and DELETE.
  • ROLLBACK cannot undo a transaction already committed.
  • Bind user values as parameters rather than building SQL text.
  • SQL dialects differ. Check your database and driver.

BACK TO SQL

Official references: