Beginner · Free course
SQL and Databases
Real SQL on a real MySQL server: queries, joins, design and performance.
90 lessons1 videos so far11 min of videoNotes for every lesson
What you’ll learn
- Tables and SELECT
- Joins and aggregation
- Schema design and normalisation
- Indexes and transactions
0 of 90 complete
Curriculum
- 1What a database is: tables, rows, columns and SQLsoon
- 2Running SQL: sqlite3 from Python and the SQLite shellsoon
- 3Your first SELECT: columns, expressions and aliases11 min
- 4Filtering rows with WHERE and comparison operatorssoon
- 5Combining conditions: AND, OR, NOT, IN and BETWEENsoon
- 6Pattern matching with LIKEsoon
- 7NULL and three-valued logicsoon
- 8Sorting and limiting: ORDER BY, LIMIT, OFFSET, DISTINCTsoon
- 9Data types across SQLite, MySQL and PostgreSQLsoon
- 10Creating and changing tables: CREATE, ALTER, DROPsoon
- 11Primary keys: natural, surrogate and compositesoon
- 12Adding data with INSERTsoon
- 13Changing data safely: UPDATE and DELETEsoon
- 14Upserts and conflict handlingsoon
- 15Numeric expressions, CAST and type conversionsoon
- 16String functions and collationsoon
- 17Dates and times 1: SQLite date functionssoon
- 18Dates and times 2: time zones, MySQL and PostgreSQLsoon
- 19Conditional logic with CASEsoon
- 20Aggregate functions: COUNT, SUM, AVG, MIN, MAXsoon
- 21Grouping rows with GROUP BYsoon
- 22HAVING and the logical order of a querysoon
- 23Relationships and foreign keyssoon
- 24Constraints and referential actionssoon
- 25Joins 1: INNER JOINsoon
- 26Joins 2: LEFT, RIGHT and FULL OUTER joinssoon
- 27Joins 3: CROSS, self, non-equi and multi-table joinssoon
- 28Subqueries 1: scalar, IN and derived tablessoon
- 29Subqueries 2: correlated, EXISTS, ANY, ALL and LATERALsoon
- 30Set operations: UNION, INTERSECT, EXCEPTsoon
- 31Common table expressions and temporary tablessoon
- 32Views and materialized viewssoon
- 33Normalization 1: anomalies and first normal formsoon
- 34Normalization 2: 2NF, 3NF, BCNF and denormalizationsoon
- 35Entity-relationship modellingsoon
- 36Designing a schema: an online storesoon
- 37Window functions 1: OVER, PARTITION BY and rankingsoon
- 38Window functions 2: running totals and share of totalsoon
- 39Window functions 3: frames with ROWS, RANGE and GROUPSsoon
- 40Window functions 4: LAG, LEAD and value functionssoon
- 41Window functions 5: NTILE, percentiles and QUALIFYsoon
- 42Recursive CTEs 1: generating series and date spinessoon
- 43Recursive CTEs 2: trees, hierarchies and graphssoon
- 44Pivoting and unpivotingsoon
- 45Subtotals with GROUPING SETS, ROLLUP and CUBEsoon
- 46JSON in SQL 1: storing and extracting JSONsoon
- 47JSON in SQL 2: arrays, unnesting and JSON indexessoon
- 48Full-text searchsoon
- 49Advanced DDL: generated columns, sequences and schemassoon
- 50Triggerssoon
- 51Stored procedures and user-defined functionssoon
- 52SQL from application code: parameters, injection and ORMssoon
- 53Transactions and ACIDsoon
- 54Concurrency anomalies: dirty reads to write skewsoon
- 55Isolation levels in practicesoon
- 56MVCC: how databases keep many versions of a rowsoon
- 57Locking 1: shared, exclusive and row lockssoon
- 58Locking 2: deadlocks, optimistic and pessimistic lockingsoon
- 59How databases store data: pages, WAL and the buffer poolsoon
- 60Indexes 1: what an index is and when it helpssoon
- 61Indexes 2: B-tree and B+tree internalssoon
- 62Indexes 3: clustered, secondary and special index typessoon
- 63Indexes 4: composite, covering, partial and expression indexessoon
- 64The query planner: reading EXPLAINsoon
- 65Join algorithms and physical operatorssoon
- 66Query optimisation 1: rewriting slow queriessoon
- 67Query optimisation 2: measuring, hints and bulk loadingsoon
- 68Partitioning large tablessoon
- 69Replication basicssoon
- 70Scaling out: sharding and distributed SQLsoon
- 71Database security: users, roles and privilegessoon
- 72Operations: backups, recovery and schema migrationssoon
- 73OLTP vs OLAP and data warehousessoon
- 74Dimensional modelling: star and snowflake schemassoon
- 75Fact table types and slowly changing dimensionssoon
- 76Analytics SQL patterns: sessions, gaps and islands, cohortssoon
- 77SQL vs NoSQL and choosing a databasesoon
- 78SQL interview 1: rapid-fire concept questionssoon
- 79SQL interview 2: Nth highest salary and ranking puzzlessoon
- 80SQL interview 3: finding and deleting duplicatessoon
- 81SQL interview 4: top-N per group and latest recordsoon
- 82SQL interview 5: consecutive days, streaks and gapssoon
- 83SQL interview 6: running totals, moving averages and growthsoon
- 84SQL interview 7: anti-joins, pivots and reporting puzzlessoon
- 85SQL interview 8: product metrics questionssoon
- 86SQL interview 9: hierarchy, graph and self-join puzzlessoon
- 87SQL interview 10: design, performance and transaction scenariossoon
- 88Project 1: analytics for a food-delivery appsoon
- 89Project 2: a concurrent ticket-booking systemsoon
- 90Capstone: build, tune and model a complete e-commerce databasesoon