How to Master SQL Using a Single Table: 150+ Real‑World Scenarios

✅ SECTION 1 — Filtering Data (1–10)

You learned how to filter rows using:

Basic WHERE
AND
OR
BETWEEN
IN
LIKE
IS NULL / IS NOT NULL
NOT conditions
Expressions in WHERE
Combined filters with parentheses
All examples used your columns like Country, OrderDate, Amount, Score, etc.

✅ SECTION 2 — Joins (11–20)

You learned:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
JOIN with filters
JOIN with expressions
JOIN + GROUP BY
Multi‑table joins
Anti‑join (LEFT JOIN + NULL)
Semi‑join (EXISTS)

All using your real keys: CustomerID, OrderID.

✅ SECTION 3 — Advanced Joins (21–30)

You learned:

Customers with orders in a specific month
Customers ordering in multiple months
First order per customer
Last order per customer
Customers with multiple payment methods
Customers with both Completed & Pending orders
Increasing order amounts
Decreasing order amounts
Orders updated same day
Customers ordering in 2023 but not 2024
All using window functions + joins.

✅ SECTION 4 — Set Operators (31–35)

You learned:

UNION
INTERSECT
EXCEPT
UNION ALL
EXCEPT for date ranges

✅ SECTION 5 — Functions (36–45)

You learned:

ROUND
Math expressions
CONCAT
SUBSTRING
REPLACE
UPPER / LOWER
LEN
TRIM
ABS
CEILING / FLOOR

All applied to your columns like Email, ShipAddress, Amount.

✅ SECTION 6 — Date & NULL Functions (46–55)

You learned:

YEAR / MONTH / DAY
DATEADD
DATEDIFF
GETDATE
Weekend detection
NULLIF
COALESCE
ISNULL
CAST / CONVERT
DATEFROMPARTS
Using your OrderDate, ShipDate, CreatedAt, UpdatedAt.

✅ SECTION 7 — CASE Expressions (56–65)

You learned:

Categorize Amount
Categorize Score
Categorize OrderStatus
Categorize Country
Categorize Quantity

✅ SECTION 8 — Aggregations (66–75)

You learned:

COUNT
SUM
AVG
MIN / MAX
GROUP BY Country
Orders per customer
Revenue per customer
Orders per status
Average Quantity
Monthly orders

✅ SECTION 9 — Window Functions (76–95)

You learned:

Running total
Total per customer
Avg per customer
Count per customer
Percent of total
Max per customer
Min per customer
First order amount
Last order amount
LAG
LEAD
ROW_NUMBER
RANK
DENSE_RANK
Additional window patterns

All using your real columns like Amount, OrderDate.

✅ SECTION 10 — Subqueries & CTEs (96–105)

You learned:

Subquery filters
Total spend > threshold
Customers with > N orders
First order cancelled
Only ordered in 2023
Monthly revenue CTE
Repeat customers
High‑value customers
Increasing order amounts (CTE + LAG)
Recursive date generator

✅ SECTION 11 — Views, CTAS, Temp Tables (106–115)

You learned:

What a VIEW is + example
High value orders view
Monthly revenue view
CTAS (SELECT INTO)
Temp tables
Additional CTAS/temp table patterns

✅ SECTION 12 — Stored Procedures (116–120)

You learned:

Update order status procedure
Delete cancelled orders procedure
Insert customer procedure
Refresh monthly revenue procedure
Archive old orders procedure

✅ SECTION 13 — Triggers (121–125)

You learned:

Auto‑update UpdatedAt
Log deleted orders
Prevent negative Amount
Default values trigger
Track order status changes

Plus:

✔ What a trigger is
✔ Why triggers matter
✔ Practical steps to test triggers

✅ SECTION 14 — Indexes & Execution Plans (126–135)

You learned:

Index on CustomerID
Index on Email
Composite index
Execution plan basics
Execution plan for joins
Execution plan for windows
Execution plan for subqueries
Execution plan for GROUP BY
Execution plan for ORDER BY
Execution plan comparisons

✅ SECTION 15 — Partitioning & Performance (136–150)

You learned:

Partition function
Partition scheme
Partitioned table
Querying partitions
Comparing performance
Rewrite OR → IN
Rewrite subquery → join
DISTINCT vs GROUP BY
EXISTS vs IN
Avoid SELECT *
Use LIMIT/TOP
Predicate pushdown
Covering indexes
Window functions vs correlated subqueries
Reducing scan size

✅ SECTION 16 — AI + (151–155)

You learned:

Convert English →
Explain in English
Optimize using AI
Generate test data
Convert business question →