06. SQL and working with SQLite - revision mindmap
All mindmaps · Full chapter · Practice answers
Open full-resolution image · Printable collection - page 6
The map is a completed revision summary. Cover a branch and reconstruct it, then try the linked chapter practice. Read the image at full size when labels are small.
Text version
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
Worked example & exam traps
- Customers A and B; only A has 2 orders. Keep both with LEFT JOIN.
- GROUP BY customer key; COUNT(OrderID) yields A:2, B:0.
- COUNT(*) would count B’s unmatched joined row as 1.
- Avoid: COUNT(*) for zero child count; WHERE for aggregate totals
Sources and reading
This map condenses Chapter 06 and its source trail. Use the self-learning reading guide for the corresponding VJC pages and A notes. It does not add topics to the stated promo scope.