Chapter 6 · Project and Next Steps
Summary, Cheat Sheet and What's Next
- Page 22 of 22
- 11 min read
You started this tutorial without ever having used a database. You can now read a schema, ask it almost any question about customers, products, orders and payments, change data safely, and turn the answers into a checked report from Python. This page collects all of it in one place: a cheat sheet to keep open while you work, the order in which SQL really runs a query, the dialect differences that bite, a glossary, and what comes next.
What you will learn
- The syntax of the whole beginner tutorial on one page, grouped by task.
- The logical order of a query, and why it explains most beginner errors.
- The SQLite, MySQL and PostgreSQL differences you are most likely to meet.
- What the advanced tutorial covers, and how to keep practising until then.
What you can do now
- Explain tables, rows, columns, primary and foreign keys, and how one-to-many and many-to-many relationships are stored.
- Read data with
SELECT, filter it withWHERE(includingNULL,IN,LIKE,BETWEEN), sort and page it. - Create tables with the right types and constraints, and insert, update, delete and upsert rows safely.
- Summarise with aggregates,
GROUP BY,HAVINGandCASE WHEN. - Combine tables with inner, outer, cross and self joins, subqueries,
EXISTSand set operations, without double counting. - Read an error message, shrink a broken query and find the bug.
Cheat sheet
| Task | Syntax |
|---|---|
| Choose columns, compute, rename | SELECT name, price * 2 AS double_price FROM products; |
| Unique values | SELECT DISTINCT city FROM customers; |
| Filter rows | WHERE price >= 1000 AND (stock > 0 OR stock IS NULL) |
| Ranges, lists, patterns | BETWEEN 1000 AND 3000, IN ('Dhaka', 'Sylhet'), LIKE 'Py%' |
| Missing values | IS NULL, IS NOT NULL, COALESCE(city, 'Unknown') |
| Sort and page | ORDER BY price DESC, product_id LIMIT 5 OFFSET 10 |
| Text, numbers, dates | LOWER, LENGTH, SUBSTR, REPLACE, ROUND(x, 2), strftime('%Y-%m', d), CAST(x AS INTEGER) |
| Safe division | a * 1.0 / NULLIF(b, 0) |
| Summaries | COUNT(*), COUNT(col), COUNT(DISTINCT col), SUM, AVG, MIN, MAX |
| Groups | GROUP BY category_id HAVING COUNT(*) > 1 |
| Conditions in a value | CASE WHEN price < 2000 THEN 'cheap' ELSE 'premium' END |
| Conditional count | SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) |
| Matching rows only | FROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_id |
| Keep unmatched rows | LEFT JOIN … WHERE right_table.key IS NULL finds the missing ones |
| Every combination | CROSS JOIN; a table with itself: employees AS e JOIN employees AS m ON m.employee_id = e.manager_id |
| A query inside a query | WHERE price > (SELECT AVG(price) FROM products), FROM (SELECT …) AS t |
| Does a match exist? | WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id) |
| Stack results | UNION (removes duplicates), UNION ALL, INTERSECT, EXCEPT |
| Make a table | CREATE TABLE t (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, qty INTEGER CHECK (qty > 0), cat_id INTEGER, FOREIGN KEY (cat_id) REFERENCES categories (category_id)); |
| Change a table | ALTER TABLE t ADD COLUMN note TEXT;, DROP TABLE IF EXISTS t; |
| Add, change, remove rows | INSERT INTO t (a, b) VALUES (1, 'x');, UPDATE t SET b = 'y' WHERE id = 1;, DELETE FROM t WHERE id = 1; |
| Insert or update | INSERT … ON CONFLICT (id) DO UPDATE SET b = excluded.b (SQLite, PostgreSQL) |
| From Python | con.execute("SELECT … WHERE id = ?", (5,)), pd.read_sql_query(sql, con) |
The order SQL runs a query
You write SELECT first, but the database reads the query in a different order. Most "why does this not work?" moments are explained by this one diagram:
written order logical order (what happens first)
------------- ----------------------------------
SELECT 5 1 FROM / JOIN which rows exist
FROM 1 2 WHERE keep some rows
WHERE 2 3 GROUP BY make groups
GROUP BY 3 4 HAVING keep some groups
HAVING 4 5 SELECT compute columns, aliases
ORDER BY 6 6 ORDER BY sort (aliases are known here)
LIMIT 7 7 LIMIT cut the resultSo an alias made in SELECT does not exist yet in WHERE, a filter on a SUM belongs in HAVING, and ORDER BY can use the aliases. Here is one query that uses every step; the comments give the order:
SELECT c.name AS category, -- 5
COUNT(DISTINCT o.order_id) AS orders,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items AS oi -- 1
JOIN orders AS o ON o.order_id = oi.order_id
JOIN products AS p ON p.product_id = oi.product_id
JOIN categories AS c ON c.category_id = p.category_id
WHERE o.status IN ('delivered', 'shipped') -- 2
GROUP BY c.category_id, c.name -- 3
HAVING COUNT(DISTINCT o.order_id) >= 4 -- 4
ORDER BY revenue DESC, c.category_id -- 6
LIMIT 2; -- 7+-------------+--------+---------+
| category | orders | revenue |
+-------------+--------+---------+
| Electronics | 6 | 31550 |
| Accessories | 4 | 8900 |
+-------------+--------+---------+Each step leaves a trace: WHERE drops the cancelled and pending orders before any grouping, HAVING drops Courses (only 3 orders), Furniture never appears because the inner joins start from sold items, and LIMIT cuts Books (4 orders, 8,600 taka) after the sort.
Dialect differences to remember
| Topic | SQLite | MySQL 8 | PostgreSQL |
|---|---|---|---|
| Join text | a || b | CONCAT(a, b) (|| means OR) | a || b or CONCAT |
| Month of a date | strftime('%Y-%m', d) | DATE_FORMAT(d, '%Y-%m') | to_char(d, 'YYYY-MM') |
| Case-insensitive match | LIKE ignores case (ASCII) | depends on collation (usually ignores case) | LIKE is case-sensitive; use ILIKE |
| Upsert | ON CONFLICT … DO UPDATE | ON DUPLICATE KEY UPDATE | ON CONFLICT … DO UPDATE |
| Auto-numbered key | INTEGER PRIMARY KEY | AUTO_INCREMENT | GENERATED ALWAYS AS IDENTITY |
| Booleans | 0 / 1 | TINYINT(1) | BOOLEAN |
| Integer division | 7 / 2 = 3 | 7 / 2 = 3.5000 | 7 / 2 = 3 |
NULLs in ORDER BY … ASC | first | first | last |
| First n rows | LIMIT n | LIMIT n | LIMIT n or FETCH FIRST n ROWS ONLY |
RIGHT / FULL OUTER JOIN | both from 3.39 | RIGHT yes; FULL no (emulate with UNION ALL) | both |
INTERSECT / EXCEPT | yes | from 8.0.31 | yes |
| Types | flexible (affinity), STRICT tables | enforced | enforced, strictest |
SQL Server, which this tutorial only mentions, writes SELECT TOP n instead of LIMIT and sorts NULLs first like SQLite and MySQL. The first row of the table is the one that surprises people most. The same expression gives different answers:
SELECT 'Nadia' || ' Rahman' AS joined;+--------------+
| joined |
+--------------+
| Nadia Rahman |
+--------------+-- MySQL
SELECT 'Nadia' || ' Rahman' AS joined, CONCAT('Nadia', ' Rahman') AS concatenated;+--------+--------------+
| joined | concatenated |
+--------+--------------+
| 0 | Nadia Rahman |
+--------+--------------+-- PostgreSQL
SELECT 'Nadia' || ' Rahman' AS joined, 7 / 2 AS int_division, ROUND(7 / 2.0, 2) AS decimal_division;+--------------+--------------+------------------+
| joined | int_division | decimal_division |
+--------------+--------------+------------------+
| Nadia Rahman | 3 | 3.50 |
+--------------+--------------+------------------+MySQL reads || as logical OR, turns both strings into numbers (0) and returns 0: no error, only a warning that most clients never show. When you move a query between databases, test it on the target database; "it ran" is not the same as "it is right".
Glossary
| Term | Meaning |
|---|---|
| Database / DBMS | An organised store of data / the software that manages it (SQLite, MySQL, PostgreSQL) |
| Table, row, column | A set of records of one kind; one record; one attribute of every record |
| Primary key | The column(s) that identify each row uniquely |
| Foreign key | A column that points to a primary key in another table |
| Schema | The tables, columns, types and relationships of a database |
| Constraint | A rule the database enforces: NOT NULL, UNIQUE, CHECK, keys |
| NULL | "Unknown or missing", not zero and not empty text; compare with IS NULL |
| Aggregate | A function that turns many rows into one value: COUNT, SUM, AVG |
| Join | Combining rows of two tables that match on a condition |
| Fan-out | Rows multiplied by a join to a one-to-many table, which inflates sums |
| Subquery / correlated subquery | A query inside a query / one that refers to the outer row and runs per row |
| Upsert | Insert a row, or update it if the key already exists |
| Normalization | Storing each fact once and linking with keys, instead of copying it |
| Transaction | A group of changes that succeed or fail together (BEGIN … COMMIT or ROLLBACK) |
| Dialect | One database's version of SQL |
Common mistakes
= NULLinstead ofIS NULL.WHERE city = NULLmatches nothing, silently. Fix:WHERE city IS NULL.NOT INwith a NULL in the list. One NULL makes the whole test unknown and the result empty. Fix:NOT EXISTS, or filter the NULLs out of the subquery.- Summing after a one-to-many join. Order 6 has two payments, so its items count twice. Fix: aggregate each side first, then join.
- An
UPDATEorDELETEwithoutWHERE. Every row changes. Fix: run the sameWHEREas aSELECTfirst, and work inside a transaction. - No tie-breaker in
ORDER BY. Rows with equal values can come back in any order, so pages and "top 3" lists change between runs. Fix: end with a unique column, e.g.ORDER BY price DESC, product_id.
Try it yourself
A mixed review. Try each one before reading the answer.
- Easy: List the products that cost between 1,000 and 3,000 taka and have stock, cheapest first.
- Medium: For every city (customers without a city as
Unknown), show the number of customers and the number of orders they placed, including cities with no orders. - Hard: Which products were bought by more than one different customer in sold orders? Show the product name and the number of customers.
Answers
-- 1. A filter, a range and a sort
SELECT name, price, stock
FROM products
WHERE price BETWEEN 1000 AND 3000
AND stock > 0
ORDER BY price, product_id;
-- 2. LEFT JOIN so customers without orders still count; COUNT(o.order_id) skips NULLs
SELECT COALESCE(c.city, 'Unknown') AS city,
COUNT(DISTINCT c.customer_id) AS customers,
COUNT(o.order_id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY COALESCE(c.city, 'Unknown')
ORDER BY orders DESC, city;
-- 3. Join, filter, group, then filter groups
SELECT p.name, COUNT(DISTINCT o.customer_id) AS customers
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY p.product_id, p.name
HAVING COUNT(DISTINCT o.customer_id) > 1
ORDER BY customers DESC, p.product_id;What's next: the advanced tutorial
SQL and Data Handling for AI: Advanced assumes everything on this page and builds on the same shop database:
| Chapter | What you will add |
|---|---|
| Advanced Queries | CTEs and recursive queries, window functions (ranking, running totals, moving averages), growth, cohorts, retention, gaps and islands |
| Design and Integrity | ER diagrams and normalization, designing a real schema and migrating it safely, transactions, isolation and locking |
| Performance and Production | Indexes, EXPLAIN and query optimisation, views, procedures and triggers, security, SQL injection and backups |
| Python and Data Handling | DB-API in depth, pandas and SQLAlchemy, data cleaning, import and export (CSV, JSON, Excel, Parquet) |
| Pipelines and AI | ETL pipelines, ML-ready datasets without leakage, SQL in AI apps (LLM logs, JSON, full-text search, pgvector and RAG), warehouses and scale |
| Practice and Capstone | Interview-style problems and capstone projects |
How to keep practising
- Ask the shop new questions. Which city buys the most courses? Which customer has not ordered for the longest time? Write the question in words first, then the query, then check the result by hand on a few rows.
- Use your own data. Load a CSV you care about (your expenses, a Kaggle dataset, your app's logs) into SQLite with pandas
to_sqland explore it with SQL. - Practise on a real server. Run a few of your queries on MySQL or PostgreSQL (Docker makes this a one-liner) and note what changes.
- Solve a little every day. Sites such as SQLBolt, LeetCode's SQL problems, HackerRank and DataLemur have graded exercises; 15 minutes a day beats a long session once a month.
Summary
- A query is written
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMITbut runsFROMfirst andSELECTalmost last. NULLneedsIS NULLandCOALESCE; aggregates skip it;NOT INtrips over it.- Joins match rows;
LEFT JOINkeeps the unmatched ones; aggregate one-to-many sides before joining them. - Change data with a
WHEREyou have tested as aSELECT, and let constraints guard the data. - The core is the same everywhere, but text joining, dates, upserts and division differ by dialect: test on the target database.
Next: SQL and Data Handling for AI: Advanced. It opens with CTEs and Recursive Queries, which lets you name each step of a long query, like the sales report from the project, and generate rows such as calendars and org charts.