Month 2 – SQL & Database Querying (Advanced)
Advanced track: Month 2 – SQL & Database Querying (Advanced)
4 lessonsData Analytics (Advanced Track)Free to enrol
Lessons
01
Week 1: Intro to Databases & SQL
Video: enrol to watchA database stores data in tables, and SQL is how you ask it questions.
Why not just use spreadsheets
Databases handle far more rows, several people can read and write at once without overwriting each other, and rules can be enforced so an order cannot reference a customer who does not exist. That last point - referential integrity - is why business data lives in databases.
Tables, rows, columns, keys
A table holds one kind of thing: customers, or orders. Each row is one record. Each column is one attribute.
A primary key uniquely identifies a row - usually an id. A foreign key is a column pointing at another table's primary key. The orders table holding a customer_id is what lets you connect an order back to the customer who placed it.
Relational, normalised design
Rather than repeating the customer's full address on every order, you store it once in customers and reference it. Less storage, and crucially one place to update when it changes.
Your first query
SELECT *
FROM customers;
Then narrow it:
SELECT customer_name, email
FROM customers
WHERE city = 'Dubai';
Flavours of SQL
PostgreSQL, MySQL, SQL Server and SQLite all speak SQL with small dialect differences. The core - SELECT, FROM, WHERE, JOIN, GROUP BY - is the same everywhere, so what you learn transfers.
Practice
Sketch two tables for a school: students and enrolments. Decide the primary key of each, and which column links them.
02
Week 2: SELECT, WHERE, ORDER BY
Video: enrol to watchThese three clauses handle the majority of everyday querying.
SELECT: choosing columns
SELECT customer_name, city FROM customers;
Use SELECT * when exploring, named columns in real work.
Rename output with AS:
SELECT customer_name AS name, total AS order_value FROM orders;
Get distinct values with DISTINCT:
SELECT DISTINCT city FROM customers;
That is the quickest way to see every category in a column - invaluable for spotting "Dubai" and "dubai" stored separately.
WHERE: choosing rows
SELECT * FROM orders WHERE total > 500;
SELECT * FROM orders WHERE status = 'shipped' AND total > 500;
SELECT * FROM orders WHERE city IN ('Dubai','Sharjah');
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';
SELECT * FROM customers WHERE email IS NULL;
Remember that NULL means unknown, so = NULL never matches. Use IS NULL.
ORDER BY: sorting
SELECT customer_name, total
FROM orders
ORDER BY total DESC;
Sort by several columns, each with its own direction:
ORDER BY city ASC, total DESC
That groups by city alphabetically, then puts the biggest order first within each city.
Combining the three
SELECT customer_name, total
FROM orders
WHERE status = 'completed' AND total > 100
ORDER BY total DESC
LIMIT 20;
The database applies them in that order: filter, then sort, then limit. Knowing the order explains why you cannot refer to a sorted position inside WHERE.
Practice
Write one query returning the 10 largest completed orders from 2026, showing customer name and total, largest first.
03
Week 3: Joins & Aggregations
Video: enrol to watchJoins combine tables; aggregations summarise them. Together they answer most real business questions.
INNER JOIN
Returns only rows with a match on both sides:
SELECT o.id, c.customer_name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;
An order with no matching customer disappears from the result. That is usually what you want, but be aware it is happening.
LEFT JOIN
Keeps every row from the left table, filling in NULL where the right table has no match:
SELECT c.customer_name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
This is how you find customers who have never ordered - add WHERE o.id IS NULL.
Choosing between them
If a row count drops unexpectedly after adding a join, an INNER JOIN is silently discarding unmatched rows. Switch to LEFT JOIN to see what you are losing.
Aggregating joined data
SELECT c.customer_name,
COUNT(o.id) AS order_count,
SUM(o.total) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.customer_name
ORDER BY lifetime_value DESC;
Every non-aggregated column in SELECT must appear in GROUP BY.
Note COUNT(o.id) rather than COUNT(*) here. With a LEFT JOIN, COUNT(*) counts the NULL-filled row and reports 1 for a customer with no orders; COUNT(o.id) correctly reports 0.
Practice
Write a query listing every customer with their order count and total spend, including customers who have never ordered, highest spend first.
04
Week 4: Advanced Queries & Indexes
Video: enrol to watchOnce the basics are comfortable, these techniques handle the harder questions and keep queries fast.
Subqueries
A query inside a query:
SELECT customer_name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);
Readable for one level of nesting; beyond that, reach for a CTE.
Common table expressions
WITH names a result you can then use, which makes long queries readable:
WITH high_value AS (
SELECT customer_id, SUM(total) AS spend
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 5000
)
SELECT c.customer_name, h.spend
FROM high_value h
JOIN customers c ON c.id = h.customer_id;
CASE for conditional logic
SELECT customer_name,
CASE WHEN total > 1000 THEN 'high'
WHEN total > 250 THEN 'medium'
ELSE 'low' END AS band
FROM orders;
Window functions
These calculate across related rows without collapsing them:
SELECT customer_name, total,
RANK() OVER (PARTITION BY city ORDER BY total DESC) AS rank_in_city
FROM orders;
Every row stays, and each gains its rank within its city. ROW_NUMBER, LAG and running totals with SUM() OVER work the same way.
Indexes
An index is a lookup structure that lets the database find rows without scanning the whole table. Index columns you filter or join on frequently. The trade-off: indexes speed up reads and slow down writes, so do not index everything.
Use EXPLAIN before your query to see whether the database is using an index or scanning the entire table.
Practice
Write a query ranking the top three orders within each region using a window function.