Beyond this course

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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 everything

Banks, 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!

Back to the course