Database Tutorial Series

Free Practice Files

Every episode of the Database Tutorial Series on YouTube comes with a free practice file — the exact data used in the video, so you can follow along and try the challenge yourself.

Watch the Series on YouTube

Episode 10

Subqueries

Same barkada food-trip bill from Episode 9, plus a new friends table. Covers subqueries — a SELECT nested inside another query — so you can answer questions like “which item costs the most?” without hardcoding the answer.

Download ep10_subqueries.sql

15 rows · 2 tables Subqueries Nested SELECT MAX()

How it works

A subquery is just a SELECT that runs first and hands its single answer to the outer query, as if you'd typed the result in by hand. Here, (SELECT MAX(price) FROM orders) runs once and resolves to a single number — the highest price in the whole table. The outer query then asks: “which row's price equals that number?”

This is different from sorting and grabbing the top row with ORDER BY price DESC LIMIT 1, which also finds the one biggest item — but that trick breaks the moment you need the biggest item per group instead (e.g. the most expensive item in each category). That's where correlated subqueries take over, in a later episode.

The friends table isn't needed for this challenge — it's here so Episode 11 can introduce joins using the same dataset.

Show the solution

SELECT item, price
FROM orders
WHERE price = (SELECT MAX(price) FROM orders);

Result: Fried Chicken at ₱120 — the single most expensive item Ana ordered.

Episode 9

GROUP BY & HAVING

One barkada food-trip bill, split with a single query. Covers GROUP BY, the 5 aggregate functions (COUNT, SUM, AVG, MIN, MAX), HAVING, and the WHERE-vs-HAVING question that trips up almost every beginner.

Download ep9_groupby.sql

10 rows · 1 table GROUP BY HAVING Aggregate functions

How it works

Think of GROUP BY as drawing an invisible line around every row that shares the same value in a column — here, every order with the same category gets folded into one bucket. Once rows are grouped, you can't ask for an individual row's price anymore; you can only ask something about the whole bucket, which is exactly what aggregate functions (SUM, COUNT, AVG, MIN, MAX) are for.

The part that trips up almost everyone: WHERE filters rows before grouping happens, so it can't see a group's total — SUM(price) doesn't exist yet at that point in the query. HAVING filters after grouping, once the totals exist. That's why “categories with a total over ₱150” has to go in a HAVING clause, never a WHERE clause.

Show the solution

SELECT category, SUM(price) AS total
FROM orders
GROUP BY category
HAVING SUM(price) > 150;

Result: Food (₱470) and Dessert (₱180) both clear ₱150. Drinks totals only ₱85, so it's filtered out.

Want the full course?

These episodes are the free preview. SQL Foundations takes you from zero to querying real databases with confidence — live, with projects.

Enroll in SQL Foundations