MA-SQL1

SQL — from queries to database design

SELECT, joins, aggregation, window functions, and schema design — guided sessions on one bookshop database

Twelve guided sessions build a complete mental model of relational databases and SQL, all on one small bookshop schema (authors, books, sales) you will come to know row by row. Start with tables, keys, and the relational model, then write real queries: SELECT with filtering, sorting, and limits; computed columns and aliases; aggregation with `GROUP BY` and `HAVING`; the full join family (INNER, LEFT, RIGHT, FULL, self-joins, multi-key joins); subqueries (scalar, `IN`, `EXISTS`, correlated) and common table expressions; set operations (`UNION`, `INTERSECT`, `EXCEPT`); window functions (ranking, `LAG`/`LEAD`, running totals); and `INSERT`/`UPDATE`/`DELETE` with transactions. The capstone session teaches schema design: constraints, normalisation, indexes, and views — so you can both ask questions of a database and design one. Every session is trace-by-hand: you compute each result set, bucket, join pair, and window value on paper before checking reveals, with auto-graded quizzes.

1. 1Databases, tables, and the relational model~55 min
2. 2SELECT basics: columns, WHERE, ORDER BY, LIMIT~65 min
3. 3Computed columns, aliases, and text functions~60 min
4. 4Aggregation: COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING~70 min
5. 5Joins I: INNER and LEFT~75 min
6. 6Joins II: RIGHT, FULL, self-joins, multi-key joins~65 min
7. 7Subqueries: scalar, IN, EXISTS, correlated~75 min
8. 8Common table expressions (WITH)~65 min
9. 9Set operations: UNION, INTERSECT, EXCEPT~55 min
10. 10Window functions~75 min
11. 11INSERT, UPDATE, DELETE, and transactions~65 min
12. 12Schema design: constraints, normalisation, indexes~75 min