SQL

6 subjects · 13 chapters · 52 topics

1. 01

   Relational Foundations

   1.1 Databases

   • Tables, rows and columns
   • Keys and relationships
   • Schemas and catalogs
   • SQL dialects and standards

   1.2 Data types and design

   • Common data types
   • NULL semantics
   • Normalisation
   • Designing a small schema
2. 02

   Reading Data

   2.1 Basic queries

   • SELECT and column expressions
   • WHERE and comparison operators
   • ORDER BY, LIMIT and OFFSET
   • DISTINCT and aliases

   2.2 Filtering well

   • IN, BETWEEN and LIKE
   • Handling NULLs correctly
   • CASE expressions
   • Boolean logic pitfalls
3. 03

   Combining and Summarising

   3.1 Joins

   • INNER and OUTER joins
   • Self joins and cross joins
   • Multi-table joins
   • Join versus subquery

   3.2 Aggregation

   • COUNT, SUM, AVG, MIN, MAX
   • GROUP BY and HAVING
   • Grouping sets and rollup
   • Avoiding wrong aggregates

   3.3 Advanced queries

   • Subqueries and correlated subqueries
   • Common table expressions
   • Recursive CTEs
   • Set operations
4. 04

   Analytical SQL

   4.1 Window functions

   • OVER, PARTITION BY and ORDER BY
   • ROW_NUMBER, RANK and DENSE_RANK
   • LAG, LEAD and running totals
   • Frames

   4.2 Dates, text and numbers

   • Date arithmetic and truncation
   • String functions
   • Numeric formatting and rounding
   • Type casting
5. 05

   Writing and Managing Data

   5.1 Modifying data

   • INSERT, UPDATE and DELETE
   • UPSERT patterns
   • Transactions and rollback
   • Constraints in action

   5.2 Objects

   • Views and materialised views
   • Indexes and when they help
   • Stored procedures at a glance
   • Permissions
6. 06

   Practice

   6.1 Query craft

   • Reading an execution plan
   • Rewriting a slow query
   • Formatting for readability
   • Testing a query's correctness

   6.2 Project

   • Answering a set of real business questions
   • Building the queries
   • Documenting assumptions
   • Presenting the results