Data & Analytics
SQL & Database Fundamentals
Tables, queries, filters, and joins for data that already exists.
SQL & Database Fundamentals teaches you to ask a database a precise question. You will create a small set of tables, filter rows, and join related data. The lectures use standard SQL so the ideas survive a change of tool.
Course syllabus
01. Tables
- Rows, columns, and keys
What a table is for, and how a row is identified.
- A table is one kind of thing
Customers in one table, orders in another.
- A primary key
The column that makes a row unique.
- A foreign key
A column that points at a row in another table.
- Create a small customers table
An id, a name, and a city. Nothing extra yet.
- Create an orders table
An id, the customer id, a date, and an amount.
- Insert rows you can remember
Three customers and five orders you could retype.
- Look at the table before you query it
The rows, so a later result can surprise you honestly.
02. Select
- SELECT and WHERE
Ask for the columns and rows you mean.
- Choose the columns
Name them. A star is for looking, not for a query you will keep.
- Filter with WHERE
The condition that throws the other rows away.
- Compare a number
Greater than, less than, and equal, on the amount.
- Compare text
A city, and the quotes that make it text.
- AND and OR
Both conditions, or either. Parentheses when you mix them.
- NULL is not a value you compare with equals
IS NULL, when the cell was never filled.
- A filter you can say in a sentence
Orders over 500 from this year. Then write only that.
03. Sort and totals
- Sort, limit, and aggregate
Order, counts, and totals.
- ORDER BY
The column, and whether large values come first.
- LIMIT the preview
The first ten rows when you are still exploring.
- COUNT the rows
How many orders match the filter.
- SUM and AVG
The total and the average, and the column they apply to.
- GROUP BY a category
One result row per city, or per customer.
- WHERE before the group, HAVING after
Filter rows first. Filter groups second.
- A total you can check by hand
Five orders. Add them yourself. Then trust the query.
04. Joins
- Joins
Combine two tables without duplicating the story.
- Why you join
The customer name lives in another table. The order only has the id.
- INNER JOIN
Rows that have a match on both sides.
- The ON clause is the key
orders.customer_id = customers.id. Not a guess.
- A join that multiplies rows
If the key is wrong, five orders become twenty.
- LEFT JOIN
Every order, even when the customer row is missing.
- Select columns from both tables
Prefix the names so city is not ambiguous.
- Read the join before the filter
Who is kept, then which of those you still want.
05. Three tables
- A products table
An id and a name. The order line will point at it.
- An order line
The order, the product, and the quantity. A third table with a job.
- Join line to product
The name of the thing that was sold.
- Join line to order to customer
Who bought it, on which order.
- A sum across the line items
Quantity times price, grouped by order.
- Do not join a table you do not need
Extra tables are how totals quietly double.
- Alias a table
A short name so the query can be read.
- Draw the three boxes
Lines, orders, customers. Arrows on the keys.
06. Change data carefully
- INSERT a row with named columns
The columns and the values, in the same order.
- UPDATE with a WHERE
Change one customer. Never practise an update without a filter.
- See the rows before you update them
SELECT with the same WHERE first.
- DELETE is not undo
A WHERE you have already SELECTed.
- A transaction in plain words
Several changes that should all succeed, or none of them.
- Why this course stays on SELECT
Reading is the skill. Writing comes after you can see the rows.
- A backup thought
On a real database, someone else owns the delete key.
- Your practice database is disposable
You can drop it. You cannot drop a client's.
07. Read a query
- Reading a query you did not write
Find the filters and the join before you trust the result.
- Start at FROM
The tables. Everything else hangs off them.
- Then the joins
Which keys, and whether an inner join dropped rows.
- Then WHERE
The rows that remain.
- Then GROUP BY and the select list
What one result row represents.
- A subquery you can name
If the inner query has a job, say the job.
- Format it so the eye can land
Each clause on its own line.
- The question the query claims to answer
If you cannot say it, do not ship the number.
08. A small report
- The question
Which customers spent the most this month.
- The tables you need
Customers and orders. Not products, unless the question asks.
- The filter on the date
This month, written so next month you know what to change.
- The group and the sort
One row per customer, largest total first.
- Limit to ten
A list someone will read.
- Check two customers by hand
Their orders, added, match the query.
- Save the query with a name
A file called top-customers, not query3.
- What you would add if they asked why
The orders under the total, as a second query, not a guess.
Lecture videos stay with the course. Playback for enrolled students will open once payments are available. Video links are not published on this page.