Beyond this course
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — You Did It!
Take a moment to appreciate everything you've learned:
SELECT, WHERE, ORDER BY, LIMIT Aggregates, GROUP BY and HAVING CASE, COALESCE and NULL Inner joins, outer joins, multi-table queries String, number and date functions UNION, INTERSECT, EXCEPT INSERT, UPDATE, DELETE CREATE TABLE, constraints, ALTER Subqueries, CTEs, window functions And so much more!
You're not just a beginner anymore. You have a solid foundation in SQL — and SQL is everywhere: the same core works in PostgreSQL, MySQL, SQL Server, Oracle and the SQLite you used here.
Here are a few directions you could explore next.
Exercise 2 — Other databases
You practised on SQLite, a tiny engine that lives in one file. Bigger projects use a database server: PostgreSQL, MySQL, SQL Server, Oracle… The SQL is the same, only small details change — the dialect:
-- SQLite, PostgreSQL, MySQL
SELECT name FROM codemon ORDER BY level DESC LIMIT 3;
-- SQL Server
SELECT TOP 3 name FROM codemon ORDER BY level DESC;Pick one, keep its documentation handy, and everything you learned here carries over.
Exercise 3 — Indexes
With millions of rows, a WHERE trainer_id = 3 means reading every row — unless there is an index, a sorted structure that lets the database jump straight to the matches:
CREATE INDEX idx_codemon_trainer ON codemon(trainer_id);Index the columns you filter and join on. Faster reads, slightly slower writes.
Exercise 4 — Transactions
Trading a Codémon takes two UPDATEs — if the second one fails, the first must not stay. A transaction makes them succeed or fail together:
BEGIN;
UPDATE codemon SET trainer_id = 2 WHERE name = 'Aquachu';
UPDATE codemon SET trainer_id = 1 WHERE name = 'Pyrochu';
COMMIT; -- or ROLLBACK; to undo everythingBanks, shops, games: every serious app relies on them. Look up the ACID properties.
Exercise 5 — Database design
So far the tables were given to you. Designing them is a skill of its own: store each fact once and link tables with keys — that is normalization. A many-to-many relation needs a third table, the junction table:
CREATE TABLE tournament_entries (
tournament_id INTEGER REFERENCES tournaments(tournament_id),
trainer_id INTEGER REFERENCES trainers(trainer_id),
PRIMARY KEY (tournament_id, trainer_id)
);Sketch an ER diagram before writing any CREATE TABLE.
Exercise 6 — SQL from code
In real projects, a program sends the queries. Here, Python's built-in sqlite3:
import sqlite3
con = sqlite3.connect("league.db")
rows = con.execute(
"SELECT name, level FROM codemon WHERE trainer_id = ?", (3,)
).fetchall()
print(rows) # [('Florachu', 12), ('Rockachu', 13), ('Firochu', 9)]The ? is a parameter — never glue user input into a query by hand, that is how SQL injection works. ORMs (Prisma, SQLAlchemy…) can write the SQL for you; knowing SQL lets you fix it when it is slow.
Exercise 7 — Project ideas
The best way to make SQL stick is to query data you care about:
- Your own Codédex — trainers, Codémons, gyms, battles: design the tables, insert your data, answer ten questions of your own.
- A personal tracker — books, films or expenses; a monthly report with GROUP BY, a running total with a window.
- A real dataset — import a CSV (sports, movies, open data) into SQLite or PostgreSQL and explore it.
- A small app — a Python or JavaScript script that reads and writes your database.
Start small, finish it, then make it bigger.
Exercise 8 — Where to start?
Start with something personal: a little website listing the last books you read, with your comments and a grade for each. The list lives in a database, the page shows it, a small form adds the next book. Frontend, backend and database — all yours!
Exercise 9 — A Message from Prof Pi
Less than 1% of the people who downloaded this app will put in the work to finish this course.
I'm so proud of you for making it this far. Remember: every expert was once a beginner. Keep querying, keep learning, and most importantly — have fun!