SQL Mastery

Every query starts with SELECT — you name the columns you want and the table they live in. Run this to see all customers:

WHERE filters rows. Only keep customers from India:

ORDER BY sorts the result. Show the most recent signups first with DESC:

Combine conditions with AND / OR. Customers from India who signed up in 2025:

IN matches a list; BETWEEN matches a range (inclusive):

LIKE does pattern matching — % is any run of characters, _ is a single character. Names starting with 'A':

Data is split across tables to avoid duplication. A JOIN stitches them back together on a shared key. Match each order to the customer who placed it:

An INNER JOIN (the default) keeps only rows that match on both sides. A LEFT JOIN keeps every row from the left table even when there is no match — perfect for finding gaps. Which customers have never ordered?

Aggregation functions (COUNT, SUM, AVG, MIN, MAX) collapse many rows into one summary. Count how many orders each status has:

GROUP BY defines the buckets; the aggregate runs per bucket. Total revenue per product category (joining three tables):

Filter groups (not rows) with HAVING — it runs after aggregation, so it can reference the aggregate. Only categories with revenue over 100:

A subquery is a query nested inside another. A scalar subquery returns a single value you can compare against — products priced above the average:

A subquery in IN returns a list. Customers who have placed at least one order:

A correlated subquery references the outer row and re-runs per row — here, an order count per customer:

A Common Table Expression (CTE) names a subquery up front with WITH, so your main query reads cleanly. Compute each order's total, then keep only the big ones:

You can chain multiple CTEs, and even reference one from the next. CTEs can also be recursive — generate the numbers 1 to 5 with no table at all:

A window function computes across a set of rows without collapsing them — you keep every row and add a calculated column. ROW_NUMBER() numbers rows inside each PARTITION:

RANK() ranks by a value across the whole set. Rank customers by total spend (CTE + window together):

Add ORDER BY inside the window to get a running total — cumulative spend per customer over time:

CASE is SQL's if/else. Bucket products into price tiers:

CASE inside an aggregate is how you pivot rows into columns — count orders by status in a single row:

Set operators stack the results of two queries. UNION merges and de-duplicates; UNION ALL keeps duplicates and is faster:

EXCEPT returns rows in the first query that are not in the second — a clean way to find products that were never ordered:

SQL can transform text inline. UPPER, LOWER, LENGTH, and SUBSTR are the everyday ones:

The || operator concatenates strings — great for building labels:

Dates are stored as ISO text (YYYY-MM-DD), which sorts and compares correctly. STRFTIME pulls out parts of a date:

julianday() converts a date to a number of days, so subtracting two of them gives an age in days. How long ago (from 9 Jul 2025) was each order placed?

INSERT adds rows. Add a new customer, then read it back:

UPDATE ... SET ... WHERE changes existing rows. Mark every shipped order as delivered:

DELETE ... WHERE removes rows. Always keep the WHERE — without it you delete the whole table: