SQL and Relational Databases | Nesqual TechAll coursesSQL and Relational Databases
Design schemas, write joins, window functions and transactions, tune slow queries and defend against SQL injection, using SQLite and Python.
- Access
- Starter
- Level
- Intermediate
- Duration
- 45 h
- Sections
- 91
Start freeIncluded in Starter, Pro and Elite.
Course outline
The relational model and your first queries
- Why a database, and where SQLite fitsLesson25 min
- The relational model in plain termsLesson30 min
- Meeting shop.db and the sql.py helperLesson30 min
- SELECT and FROM: asking for columnsLesson30 min
- Data types in SQLiteLesson30 min
- How SQL is read and runLesson30 min
- Chapter 1 quiz: the relational model and first queriesQuiz15 min
Expressions, filtering and sorting
Filtering rows with WHERE
Lesson25 min
IN, BETWEEN, LIKE and date rangesLesson30 minNULL and three-valued logicLesson30 minScalar functions and CASELesson35 minDISTINCT and ORDER BYLesson30 minLIMIT, OFFSET and top-N queriesLesson30 minExpressions, filtering and sorting quizQuiz15 minAggregation and grouping
- Aggregate functions: counting, adding and summarisingLesson30 min
- GROUP BY: one summary row per groupLesson30 min
- HAVING: filtering groups, and how it differs from WHERELesson25 min
- The order in which a query runsLesson25 min
- Conditional aggregation: pivots, percentages and listsLesson30 min
- Aggregating money correctlyLesson30 min
- Aggregation and grouping quizQuiz15 min
- Lab: answer business questions with aggregatesLab40 min
Joins of every kind
- Inner joins: pairing rows across tablesLesson30 min
- Outer joins: keeping the rows that have no partnerLesson30 min
- Cross joins and self joinsLesson30 min
- Joining many tables, and the fan-out trapLesson35 min
- Anti-joins, semi-joins and set operatorsLesson35 min
- Join pitfalls and how to catch themLesson30 min
- Quiz: joins of every kindQuiz15 min
- Lab: answer questions that need joinsLab40 min
Subqueries and common table expressions
- Scalar subqueries and derived tablesLesson30 min
- IN and NOT IN subqueries, and the NULL trapLesson30 min
- Correlated subqueries, EXISTS and NOT EXISTSLesson30 min
- WITH: common table expressionsLesson30 min
- Recursive CTEs: trees and seriesLesson35 min
- Refactoring nested queries into CTEsLesson30 min
- Subqueries and CTEs quizQuiz15 min
- Lab: answering questions with subqueries and CTEsLab40 min
Window functions
- What a window is: OVER, PARTITION BY and ORDER BYLesson30 min
- Ranking rows: row_number, rank, dense_rank and ntileLesson30 min
- Top N per group and removing duplicatesLesson30 min
- Running totals, moving averages and framesLesson35 min
- Looking at neighbouring rows: lag, lead and streaksLesson35 min
- Named windows and readable report queriesLesson30 min
- Window functions quizQuiz15 min
- Lab: reports with window functionsLab40 min
Changing data and transactions
- INSERT: adding rows, and your first Python database scriptLesson30 min
- UPDATE and DELETE without regretsLesson30 min
- Upserts: when the row might already existLesson30 min
- Transactions and ACID: all or nothingLesson35 min
- Transactions in Python's sqlite3 moduleLesson30 min
- Concurrency, locks and isolation levelsLesson35 min
- Quiz: changing data and transactionsQuiz15 min
- Lab: change the shop's data safelyLab40 min
Schema design and normalisation
- From requirements to entities and relationshipsLesson30 min
- Keys: candidate, primary, natural and surrogateLesson30 min
- One-to-one, one-to-many and many-to-manyLesson30 min
- Normalisation by example: 1NF, 2NF and 3NFLesson35 min
- Denormalisation and everyday modelling choicesLesson30 min
- From design to DDL: CREATE, ALTER and DROPLesson35 min
- Schema design and normalisation quizQuiz15 min
Constraints and indexes
- PRIMARY KEY, NOT NULL and UNIQUE in depthLesson30 min
- CHECK, DEFAULT, STRICT tables and generated columnsLesson30 min
- Foreign keys and referential integrityLesson35 min
- Changing a schema safely: ALTER TABLE, rebuilds and migrationsLesson35 min
- How an index worksLesson30 min
- The index family: composite, covering, partial and expression indexesLesson35 min
- Quiz: constraints and indexesQuiz15 min
Query plans and performance
- How the planner runs your queryLesson30 min
- Measuring honestly: a timing harness and a big tableLesson35 min
- Index-friendly conditions (sargability)Lesson35 min
- Choosing indexes for a workloadLesson35 min
- Statistics, join order and N+1 queriesLesson35 min
- Pagination, counting, batching and a tuning checklistLesson35 min
- Quiz: query plans and performanceQuiz15 min
- Lab: tune three slow queriesLab40 min
Views, triggers and security
- Views: saved queries and safe interfacesLesson30 min
- Triggers: code that runs when data changesLesson30 min
- SQL injection: how string-built queries breakLesson35 min
- The defence: parameterised queriesLesson30 min
- Least privilege: logins, roles and read-only accessLesson30 min
- Protecting data: passwords, masking, encryption and backupsLesson35 min
- Views, triggers and security quizQuiz15 min
SQLite, PostgreSQL and MySQL, and the capstone
- A dialect map of SQLite, PostgreSQL and MySQLLesson35 min
- Translating queries between dialectsLesson35 min
- The three systems compared as systemsLesson30 min
- Working with databases from codeLesson30 min
- Capstone brief: analyse Lantern's shop and web dataLesson30 min
- Capstone walkthrough, and where to go nextLesson30 min
- Quiz: dialects, systems and the capstoneQuiz15 min
- Capstone lab: the Lantern analysis reportLab40 min