Exercises
Beyond the review problems in each lesson, these are practical exercises that combine multiple concepts. A total of 640 problems are provided by difficulty level.
All problems can be verified with the TechShop sample database (
ecommerce-ko.db). Each problem includes collapsible hints and answers — try solving them yourself first before checking.
Beginner (240 Problems)
Solvable with concepts from Lessons 00-07. Uses single tables only, without JOINs or subqueries.
| # | Topic | Problems | Key Concepts |
|---|---|---|---|
| 1 | Product Search | 30 | SELECT, WHERE, LIKE, BETWEEN, IN |
| 2 | Sorting and Paging | 30 | ORDER BY, LIMIT, OFFSET, DISTINCT |
| 3 | Aggregate Functions | 30 | COUNT, SUM, AVG, MIN, MAX, ROUND |
| 4 | Grouping and Filtering | 30 | GROUP BY, HAVING |
| 5 | NULL Handling | 30 | IS NULL, COALESCE, NULL and aggregation |
| 6 | CASE Expressions | 30 | CASE WHEN, classification, conditional aggregation |
| 7 | Comprehensive | 30 | Combination of all beginner concepts |
| 8 | SQL Error Detection | 30 | Syntax/logic/result error diagnosis |
Intermediate (220 Problems)
Uses concepts from Lessons 08-17. Includes JOINs, subqueries, functions, and DML/DDL.
| # | Topic | Problems | Key Concepts |
|---|---|---|---|
| 1 | JOIN Master | 25 | INNER/LEFT JOIN, anti-join, multi-table |
| 2 | Date/Time Analysis | 20 | SUBSTR, julianday, strftime, calendar |
| 3 | String/Numeric Functions | 20 | LENGTH, REPLACE, GROUP_CONCAT, ROUND, CAST |
| 4 | Subqueries | 20 | WHERE/FROM/SELECT/correlated subqueries |
| 5 | Set Operations | 15 | UNION, INTERSECT, EXCEPT |
| 6 | DML Practice | 20 | INSERT, UPDATE, DELETE, UPSERT |
| 7 | DDL/Constraints | 15 | CREATE/ALTER/DROP TABLE, PK, FK, CHECK |
| 8 | Transactions | 15 | BEGIN, COMMIT, ROLLBACK, SAVEPOINT |
| 9 | Comprehensive | 25 | Combination of all intermediate concepts |
| 10 | SQL Debugging | 25 | JOIN/subquery/date error diagnosis |
| 11 | Data Quality | 20 | NULL, duplicates, FK integrity, outliers |
Advanced (180 Problems)
Uses concepts from Lessons 18-26. Includes window functions, CTEs, DB objects, and business analysis.
| # | Topic | Problems | Key Concepts |
|---|---|---|---|
| 1 | Window Functions in Practice | 15 | ROW_NUMBER, RANK, LAG, NTILE, SUM OVER |
| 2 | CTE Applications | 15 | WITH, recursive CTE, multi-CTE chaining |
| 3 | EXISTS and Anti-Patterns | 15 | EXISTS, NOT EXISTS, universal negation |
| 4 | DB Object Design | 20 | Views, triggers, stored procedures |
| 5 | JSON Applications | 10 | json_extract, json_set, JSON aggregation |
| 6 | Indexes and Optimization | 15 | EXPLAIN, covering/partial indexes, query rewrite |
| 7 | Sales Analysis | 20 | YoY, ABC, promotions, basket analysis |
| 8 | Customer/Operations Analysis | 20 | RFM, cohort, inventory, CS performance |
| 9 | Practical SQL Patterns | 15 | Top-N, sessions, funnel, pivot, Pareto |
| 10 | Interview Prep | 20 | FAANG/Korean IT frequently asked patterns |
| 11 | Challenge Problems | 15 | Mini projects, comprehensive design |