SQL and Databases: Ask Your Data Anything

By the end

What you'll build

• Explain why one table per kind of thing beats one wide sheet, and name the three anomalies it prevents
• Write SELECT queries with explicit column lists, computed columns and aliases
• Filter rows precisely with comparison operators, bracketed AND/OR/NOT, IN, BETWEEN and LIKE
• Find and handle missing data with IS NULL and COALESCE, and explain why = NULL silently returns nothing
• Sort and page results with ORDER BY, LIMIT and a unique tie-breaker so answers are repeatable
• Summarise data with COUNT, SUM, AVG, MIN and MAX, and choose correctly between COUNT(*) and COUNT(column)
• Group rows with GROUP BY, apply the rule about what may sit beside it, and filter groups with HAVING
• Join tables with INNER and LEFT JOIN, write an anti-join, join a table to itself, and spot an accidental cross join
• Restructure a nested query into a readable CTE and label rows with CASE WHEN
• Change data with INSERT, UPDATE and DELETE inside a transaction, writing the WHERE clause first
• Judge a table design against third normal form and decide when to denormalise on purpose
• Choose useful indexes and read a query plan well enough to tell a full scan from an indexed search

Curriculum

What's inside

7 modules · 48 lessons

1. 01

   Tables, Rows and Keys

   6 lessons
   • Start Here: What This Course Is, and What It Is Not
   • One Big Sheet, and Where It Breaks
   • Rows, Columns and Types
   • Primary Keys: One Row, One Name
   • Your First Query: SELECTsql lab
   • + 1 more lesson
2. 02

   Asking for the Right Rows

   7 lessons
   • WHERE: The Filter
   • Filter with WHEREsql lab
   • AND, OR, NOT and the Bracket Trap
   • Combine Conditionssql lab
   • Shortcuts: IN, BETWEEN and LIKE
   • + 2 more lessons
3. 03

   Missing Values, Order and Unique Values

   6 lessons
   • NULL Is Not Zero, and Not Even Equal to Itself
   • Handle Missing Valuessql lab
   • ORDER BY: Putting Rows in Order
   • Sort and Take the Top Fewsql lab
   • List the Unique Valuessql lab
   • + 1 more lesson
4. 04

   Aggregates and Grouping

   6 lessons
   • Five Functions That Collapse Many Rows Into One
   • Count, Sum and Averagesql lab
   • GROUP BY, Slowly
   • Group and Countsql lab
   • The SELECT Rule Beside a GROUP BY
   • + 1 more lesson
5. 05

   Filtering Groups, Dates and Your First Join

   7 lessons
   • HAVING vs WHERE
   • Filter Groups with HAVINGsql lab
   • Dates in SQLite
   • Sales by Monthsql lab
   • Foreign Keys, and What They Prevent
   • + 2 more lessons
6. 06

   Joins in Depth, Subqueries and Readable SQL

   8 lessons
   • LEFT JOIN: Keeping the Rows With No Match
   • Find the Customers Who Never Orderedsql lab
   • Joining Three Tablessql lab
   • A Table Joined to Itself
   • Who Reports to Whomsql lab
   • + 3 more lessons
7. 07

   Changing Data, Design, Speed and the Capstone

   8 lessons
   • CASE WHEN: If-Then Inside SQLsql lab
   • Changing Data: Write the WHERE First
   • Change Rows Safelysql lab
   • Transactions: All or Nothing
   • Third Normal Form, Without the Jargon
   • + 3 more lessons

The shape of it

How this course works