06 SQL and working with SQLite

SQL and working with SQLite

← Index · Solutions

SQL syllabus quick-reference priority/high

Write a query in this clause order: SELECT → FROM/JOIN → WHERE → GROUP BY → HAVING → ORDER BY. The table covers the examinable SQL/SQLite forms in the syllabus reference guide and the VJC notes. Rows marked † are taught or used in these notes even though they are not printed in the syllabus’s short SQL reference table.

Area	Operation / syntax	What it does / exam note	Example
Query structure	SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...;	Produces the requested result table. Include only clauses that are needed and only the requested output columns.	SELECT CustomerID, SUM(Quantity) AS Total FROM Orders WHERE CollectionDate >= '2026-09-01' GROUP BY CustomerID HAVING SUM(Quantity) > 10 ORDER BY Total DESC;
SQLite types and values	INTEGER, REAL, TEXT, NULL	Use INTEGER for whole numbers, REAL for approximate decimals and TEXT for strings/ISO dates. NULL means missing or unknown, not zero or an empty string.	Quantity INTEGER, Price REAL, CollectionDate TEXT
Constraints	NOT NULL, UNIQUE, PRIMARY KEY, AUTOINCREMENT, FOREIGN KEY, CHECK†	Enforce required, unique, identifying, generated, related or valid values. A composite key names multiple columns.	PRIMARY KEY (OrderID, BentoID), FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID), CHECK (Quantity > 0)
DDL: create table	CREATE TABLE	Defines a new table, its columns and constraints.	CREATE TABLE Customer (CustomerID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL UNIQUE);
DDL: create safely†	CREATE TABLE IF NOT EXISTS	Avoids an error if a table with that name already exists.	CREATE TABLE IF NOT EXISTS Customer (CustomerID INTEGER PRIMARY KEY, Name TEXT NOT NULL);
DML: insert	INSERT INTO ... VALUES ...	Adds a new record. Listing the target columns avoids relying on table-column order.	INSERT INTO Customer (CustomerID, Name) VALUES (1, 'Ali');
DML: update	UPDATE ... SET ... WHERE ...	Changes every record satisfying the row condition. Without WHERE, every row is updated.	UPDATE Customer SET Name = 'Alicia' WHERE CustomerID = 1;
DML: delete rows	DELETE FROM ... WHERE ...	Deletes every record satisfying the row condition. Without WHERE, every row is deleted but the table remains.	DELETE FROM Customer WHERE CustomerID = 1;
DDL: drop table	DROP TABLE	Removes the table definition and all its records.	DROP TABLE Customer;
Read selected/all fields	SELECT column... / SELECT *	Retrieves chosen columns or every column. Prefer chosen columns when the question specifies the output.	SELECT Name, Phone FROM Customer; / SELECT * FROM Customer;
Remove duplicate result rows†	DISTINCT	Removes duplicate rows from the result; it does not aggregate or change stored data.	SELECT DISTINCT CustomerID FROM Orders;
Aliases	AS	Gives a temporary name to a table, column or computed result; qualify ambiguous fields.	SELECT c.Name, COUNT(o.OrderID) AS OrderCount FROM Customer AS c INNER JOIN Orders AS o ON o.CustomerID = c.CustomerID GROUP BY c.CustomerID, c.Name;
Filter rows	WHERE with =, !=/<>, <, <=, >, >=	Keeps individual rows satisfying the condition. Do this before grouping.	SELECT * FROM Orders WHERE Quantity >= 3;
Combine/negate conditions	AND, OR, NOT	AND requires both; OR requires either; NOT negates. Parenthesise mixed logic.	WHERE Quantity >= 3 AND NOT (CustomerID = 4 OR CustomerID = 7)
Test missing values	IS NULL, IS NOT NULL	Tests for missing/non-missing values. Never use = NULL or != NULL.	SELECT * FROM Customer WHERE Phone IS NULL;
Inclusive range†	BETWEEN ... AND ...	Keeps values between two inclusive endpoints.	WHERE CollectionDate BETWEEN '2026-09-01' AND '2026-09-30'
Membership†	IN (...)	Tests whether a value equals one item in a list.	WHERE CustomerID IN (1, 3, 5)
Pattern match†	LIKE with % and _	% matches any sequence; _ matches exactly one character.	WHERE Name LIKE 'A%' / WHERE Name LIKE '_li'
Sort	ORDER BY ... ASC/DESC	Sorts ascending (ASC, the default) or descending (DESC). Add a tie-breaker when deterministic order matters.	ORDER BY CollectionDate DESC, OrderID ASC;
Arithmetic operators	+, -, *, /, %	Calculates numeric expressions; % gives the remainder.	SELECT Quantity * 2 AS DoubleQty FROM Orders WHERE OrderID % 2 = 0;
Text concatenation	||	Joins text in SQLite.	SELECT FirstName || ’ ’ || Surname AS FullName FROM Borrower;
Count rows	COUNT(*)	Counts result rows, including a retained all-NULL optional side of a left join.	SELECT COUNT(*) AS NumberOfOrders FROM Orders;
Count non-NULL values	COUNT(column)	Counts non-NULL values only. Count an optional-side key to obtain zero for an unmatched left-side row.	COUNT(o.OrderID) AS OrderCount
Other aggregates	SUM, MAX, MIN, AVG†	Calculates one value per result or per group; these ignore NULL values. Count orders, but sum Quantity when portions are requested.	SELECT SUM(Quantity), MAX(Quantity), MIN(Quantity), AVG(Quantity) FROM Orders;
Form groups†	GROUP BY	Collapses rows with the same grouping value so aggregates are calculated per group. Group by the entity’s key and any non-aggregated displayed fields.	SELECT CustomerID, SUM(Quantity) AS Total FROM Orders GROUP BY CustomerID;
Filter groups†	HAVING	Keeps groups satisfying an aggregate condition; use WHERE for row conditions.	SELECT CustomerID, SUM(Quantity) AS Total FROM Orders GROUP BY CustomerID HAVING SUM(Quantity) > 10;
Cartesian/cross join	FROM table1, table2	Produces every possible row pair. Usually unwanted unless the question explicitly asks for a Cartesian product.	SELECT * FROM Customer, Orders;
Comma-style matched join	FROM table1, table2 WHERE join_condition	Syllabus-listed inner-join form. Do not omit the matching-key condition.	SELECT c.Name, o.OrderID FROM Customer AS c, Orders AS o WHERE c.CustomerID = o.CustomerID;
Inner join	INNER JOIN ... ON ...	Keeps only row pairs satisfying the join condition.	SELECT c.Name, o.OrderID FROM Customer AS c INNER JOIN Orders AS o ON o.CustomerID = c.CustomerID;
Left outer join	LEFT OUTER JOIN ... ON ...	Keeps every left-table row and fills unmatched right-table fields with NULL. LEFT JOIN is the same.	SELECT c.CustomerID, o.OrderID FROM Customer AS c LEFT OUTER JOIN Orders AS o ON o.CustomerID = c.CustomerID;
Preserve zero matches	Filter the optional table in ON; group and count its non-NULL key	A right-table condition in WHERE would reject the NULL row produced by the left join.	SELECT c.CustomerID, COUNT(o.OrderID) AS OrderCount FROM Customer AS c LEFT JOIN Orders AS o ON o.CustomerID = c.CustomerID AND o.CollectionDate BETWEEN '2026-09-01' AND '2026-09-30' GROUP BY c.CustomerID;
Replace an aggregate NULL†	COALESCE(expression, fallback)	Returns the first non-NULL value; useful when SUM over no matches should display zero.	COALESCE(SUM(o.Quantity), 0) AS TotalQuantity
SQLite foreign-key enforcement	PRAGMA foreign_keys = ON;	Enables enforcement for the current SQLite connection; declaring a foreign key alone may not enable it.	connection.execute("PRAGMA foreign_keys = ON")
Python: connect and execute	sqlite3.connect(...); connection.execute(...)	Opens/creates the database and runs SQL. A SELECT returns a cursor.	connection = sqlite3.connect("orders.db"); cursor = connection.execute("SELECT * FROM Orders")
Python: bind values	? placeholders plus a tuple	Safely substitutes values, preventing input from being parsed as SQL. One value needs a trailing comma.	connection.execute("DELETE FROM Customer WHERE CustomerID = ?", (customer_id,))
Python: retrieve rows	Cursor iteration, fetchone(), fetchall()	fetchone() returns the next row or None; fetchall() returns a list of remaining rows.	rows = cursor.fetchall()
Python: finish a transaction	commit(), rollback(), close()	Commit intended changes, roll back an incomplete transaction, and always close the connection. A SELECT does not need a commit.	connection.commit() / connection.rollback() / connection.close()

Source anchors: 9569 SQL reference guide, pp. 17–18; VJC Chapter 16, Parts 2–3. Supplementary checks: W3Schools SQL quick reference, operators, aggregate functions and NULL handling.

Priority key · Priorities guide emphasis; they do not remove taught scope.

Exam recall

Output columns → tables/joins → row filter → groups → group filter → order. Count orders and sum quantities only when those are the requested quantities.

Understand the result: one row represents what? priority/high

SQL describes the result you want from related tables. Before selecting clauses, finish this sentence: “Each output row represents …” It might be one order, one customer or one bento type. That decision determines whether you need individual rows or grouped summaries.

For example, a customer with three orders should appear three times in an order-by-order report, but once in a total-per-customer report. Adding DISTINCT to hide repetition would not calculate the customer’s total.

Before writing SQL, identify the required columns, source tables, row conditions, grouping and order. SQL returns a table-shaped result. SELECT * can fail a question asking for only two attributes, even if those attributes are included.

SELECT c.CustomerID, c.Name, COUNT(*) AS NumberOfOrders
FROM Customer AS c
JOIN Orders AS o ON o.CustomerID = c.CustomerID
WHERE o.CollectionDate >= '2026-09-01'
GROUP BY c.CustomerID, c.Name
HAVING COUNT(*) >= 2
ORDER BY NumberOfOrders DESC, c.CustomerID ASC;

Think: FROM/JOIN supplies rows → WHERE filters individual rows → GROUP BY forms groups → HAVING filters groups → SELECT produces columns → ORDER BY sorts. Write clauses in the syntax order shown. A tie-breaker makes tied results deterministic when needed.

Core operations priority/high

Purpose	SQL pattern
Define a table	CREATE TABLE Customer (CustomerID INTEGER PRIMARY KEY, Name TEXT NOT NULL);
Add a record	INSERT INTO Customer (CustomerID, Name) VALUES (1, 'Ali');
Change selected records	UPDATE Customer SET Name = 'Alicia' WHERE CustomerID = 1;
Delete selected records	DELETE FROM Customer WHERE CustomerID = 1;
Remove a table	DROP TABLE Customer;

CREATE/ALTER/DROP are schema-definition operations; INSERT/UPDATE/DELETE manipulate data; SELECT retrieves it (classification labels can vary by course). Missing WHERE in UPDATE/DELETE affects every row. DROP is not the same as deleting one row.

Conditions use =, <>, <, <=, AND, OR, NOT, BETWEEN (inclusive), IN, and LIKE. In LIKE, % matches any sequence and _ matches one character. Use parentheses where combined conditions could be misread. NULL represents missing/unknown data; use IS NULL or IS NOT NULL, not = NULL.

COUNT(*) counts rows; COUNT(column) ignores NULL in that column. SUM, AVG, MIN, MAX aggregate values. DISTINCT removes duplicate result rows. To show groups with zero matches, use a suitable LEFT JOIN and count a non-null key on the optional side; an inner join omits them.

Joins and keys priority/high

A join combines related rows through matching keys. Omitting a required join condition can create unrelated combinations. Qualify repeated column names with aliases. A foreign key constraint enforces a relationship; merely writing a JOIN does not establish that constraint.

Customer:                    Orders:
ID  Name                     OrderID  CustomerID  Quantity
1   Ali                      10       1           3
2   Bea                      11       1           4
 
Join on matching CustomerID → (Ali,10,3), (Ali,11,4)
Group by customer           → Ali: 2 orders, 7 portions

There is no order for Bea. INNER JOIN omits Bea; LEFT JOIN can retain Bea with NULL order fields. Counting the optional order key gives zero; COUNT(*) would count the retained row. Count the thing named in the requirement: orders and portions are different quantities.

SQLite example of a composite key: PRIMARY KEY (OrderID, BentoID). A foreign key can be declared FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID). SQLite connections should enable PRAGMA foreign_keys = ON when relying on foreign-key enforcement.

Worked example — adapted from HCI 2025 Q5 priority/high

To count orders for each bento type, use Orders(OrderID, BentoID, Quantity) and Bento(BentoID, Name):

SELECT b.BentoID, b.Name, COUNT(o.OrderID) AS OrderCount
FROM Bento AS b
LEFT JOIN Orders AS o ON o.BentoID = b.BentoID
GROUP BY b.BentoID, b.Name
ORDER BY OrderCount DESC, b.BentoID;

This includes bento types with no orders. If the question asks for portions, use SUM(Quantity) rather than counting orders; use COALESCE(SUM(o.Quantity), 0) for zero-order types. The noun in the requirement changes the calculation.

Exam approach: translate each clause of the question priority/high

Requirement	Decision to make
“Show the name and number of orders for each bento”	Output name plus COUNT; group by the bento identity.
“Including bento types with no orders”	Start with all Bento rows and LEFT JOIN orders.
“Only orders collected in September”	Filter order rows before aggregation, taking care not to remove required unmatched parents.
“Only totals above ten”	HAVING filters the computed groups.
“Most popular first”	ORDER BY the count descending.

HCI 2023 Q6(c) and 2024 Q5(e) require joined reports with ordering; HCI 2025 Q5(d) requires an aggregate popularity report. Practise the original 2024 Q5(e), PDF p.3 after 06A, and the original 2025 Q5(d), PDF p.4 after the worked example. Use the tables supplied or designed in those questions, not this chapter’s substituted schema.

Common mistakes

Grouping by a non-unique name instead of identity; filtering with WHERE when a grouped total is required; using = NULL; omitting the join condition; and returning extra columns or removing meaningful duplicate rows.

Python with SQLite: complete workflow priority/medium

Syllabus §3.3.8 includes using a programming language with SQL. Learn the complete workflow alongside handwritten query construction. Historical promo frequency does not exclude taught Lab content.

import sqlite3
 
def orders_for_customer(connection, customer_id):
    cursor = connection.execute(
        "SELECT OrderID, CollectionDate FROM Orders "
        "WHERE CustomerID = ? ORDER BY CollectionDate DESC, OrderID",
        (customer_id,)  # Bind one value; the comma creates a one-item tuple.
    )
    return cursor.fetchall()  # Return all matching rows, not printed output.

The ? binds a value, and (customer_id,) is a one-element tuple. Do not interpolate user input into SQL text. fetchone() returns one row or None; fetchall() returns a list of remaining rows. Commit successful changes when needed and close the connection when finished. Transactions group operations so that failure can roll back an incomplete change.

SQLite commonly uses INTEGER for whole numbers, REAL for approximate decimal quantities, TEXT for strings/ISO dates and BLOB for binary data. Use NOT NULL, UNIQUE, CHECK and key constraints when the scenario requires them. A sensible type alone does not enforce a range such as nonnegative quantity.

For DB Browser lab work: inspect the table structure/keys, inspect sample records, execute the query, and compare both column headings and rows with the requested result. Save/commit intended edits through the application. Test joins with a parent having no children and with repeated names; otherwise a wrong join or grouping key can appear correct on a small dataset.

Connect → execute → fetch → close

import sqlite3
 
def customer_orders(database_path, customer_id):
    connection = sqlite3.connect(database_path)
    try:
        cursor = connection.execute(
            "SELECT OrderID, CollectionDate FROM Orders "
            "WHERE CustomerID = ? ORDER BY OrderID",
            (customer_id,)
        )
        return cursor.fetchall()
    finally:
        connection.close()

Contract: the database and Orders table already exist. The result is a list of rows; no matches gives []. A SELECT does not need a commit. For a required persistent update, execute the parameterised change and commit it; close the connection afterward.

Keep customers with no matching orders

To retain all customers, put the optional order-date restriction in ON. A WHERE condition on the unmatched order’s date rejects the NULL row. Count the optional key, not COUNT(*).

Practice

For all questions, use Customer(CustomerID PK, Name, Phone) and Orders(OrderID PK, CustomerID FK, CollectionDate, Quantity). Dates use ISO YYYY-MM-DD text.

06A — original. Retrieve only the customer name and collection date for orders whose Quantity is at least 3, latest collection dates first. Each order should produce a row.

SELECT Customer.Name,  COUNT(OrderID) AS Quantity;
 
 
 

06B — original. Show CustomerID and total Quantity for customers whose total Quantity exceeds 10. Sort by total descending. Include only customers meeting that condition.

06C — original, extra practice. Write a Python function change_phone(connection, customer_id, phone) using parameters to update a customer’s phone and commit the change. Explain why treating phone as an integer and interpolating it into SQL are poor choices.

Hints

06A: WHERE acts on orders. 06B: HAVING acts on grouped totals. 06C: bind both values, including the identifier.

06D — original, LEFT JOIN. Show every CustomerID and its number of orders collected in September 2026, including zero, ordered by CustomerID. Use the chapter’s schema and ISO date text. Explain where to put the date restriction and which expression to count.

06E — original, table definition. Create Orders with integer OrderID primary key; required CustomerID foreign key; required CollectionDate text; and required positive integer Quantity. Customer already exists. State how to enable SQLite foreign-key enforcement on the connection.

Revision checklist

• 06.1 Create tables with suitable SQLite types, primary/foreign keys and relevant constraints.
• 06.2 Write SELECT queries with selected fields, conditions, Boolean operators and ordering.
• 06.3 Join tables through the correct key pairs and avoid unintended Cartesian products.
• 06.4 Distinguish INNER JOIN from LEFT OUTER JOIN and predict unmatched-row results.
• 06.5 Use aggregate functions and distinguish counting rows from counting non-NULL values.
• 06.6 Use GROUP BY and HAVING where taught; distinguish row filtering from group filtering.
• 06.7 Write INSERT, UPDATE, DELETE and DROP statements and explain their different effects.
• 06.8 Handle NULL with the appropriate SQL condition.
• 06.9 Translate a report specification into output fields, tables, join conditions, filters and order.
• 06.10 Use DB Browser to inspect schema, execute queries and check results.
• 06.11 Use Python sqlite3 to connect, parameterise a query, fetch results, commit changes when needed and close the connection.
• 06.12 Test a query against duplicate names, repeated orders, missing matches and zero matching rows.

Visual revision mindmap

Open this mindmap and its text version · All 21 mindmaps

Your mindmap framework

Centre: SQL and working with SQLite. Build the six branches below. For each subbranch, add a short definition, a labelled sample and one exam trap from memory; then check the chapter.

flowchart LR
    C["06 • Revision map"]
    C --> B0["Decode the requested result"]
    C --> B1["Read and filter"]
    C --> B2["Join and aggregate"]
    C --> B3["Define and change data"]
    C --> B4["Python and SQLite"]
    C --> B5["Visual checks and mistakes"]

• Decode the requested result

  • One row represents what?.
  • Required columns and source tables.
  • Conditions, grouping and order.
  • Order count versus total quantity; preserve meaningful duplicates.

• Read and filter

  • SELECT, FROM, aliases.
  • WHERE: comparisons, Boolean conditions, BETWEEN, IN, LIKE.
  • NULL: IS NULL and IS NOT NULL.
  • ORDER BY direction and tie-breaker where required.

• Join and aggregate

  • Match the intended key pairs.
  • INNER JOIN versus LEFT JOIN.
  • COUNT(*), COUNT(column), SUM, AVG, MIN, MAX.
  • GROUP BY forms groups; HAVING filters groups.

• Define and change data

  • CREATE: types, primary/foreign keys, constraints.
  • INSERT: columns and values.
  • UPDATE and DELETE: identify affected rows with WHERE.
  • DROP removes a table; foreign-key enforcement on the connection.

• Python and SQLite

  • Connect → parameterised execute.
  • Fetch one/all rows; no match behaviour.
  • Commit changes where needed; close connection.
  • Placeholders bind values; one-item tuple needs a comma.

• Visual checks and mistakes

  • Draw tiny input tables → joined rows → grouped result.
  • Include a parent with no children and duplicate names.
  • Put optional date restriction in ON to retain zero matches.
  • Avoid: COUNT(*) for zero child count; WHERE for aggregate totals.

Close the notes and test the map: explain one branch aloud, sketch its sample, then answer a linked practice question. Mark any missing link to revisit.

Source trail

9569 §3.3.8; reference guide pp.17–18; school SQL and SQL2; all three MOE DB notes.

HCI 2022 Q5(e); 2023 Q6(c); 2024 Q5(e); 2025 Q5(d). GROUP BY is retained because HCI Q5(d) and SQL2 teach grouped counts.

Source guide records provenance and original-paper locations.