Intermediate Projects
Prerequisites: Level 2 — Intermediate
Build these using joins, subqueries, CTEs, CASE, constraints, and database design from Level 2.
1. Sales Analysis
Using the sample database, write queries for: revenue per customer (including customers who never ordered, showing zero), revenue per product category per month, the best-selling product by quantity, and customers whose last order is more than 60 days old. Use CTEs to keep each query readable.
2. Design a School Database
Design tables for students, teachers, courses, classes (a course taught by a teacher in a term), and enrollments with grades. Choose primary and foreign keys, add sensible constraints, normalize to third normal form, and draw an ER diagram. Then write queries for class rosters, each student's average grade, and teachers' total student counts.
3. Import and Clean a Public Dataset
Import a public CSV dataset into SQLite or PostgreSQL, create properly typed tables, and write a cleaning script: standardize text, handle missing values, remove duplicates, and create a view with the cleaned data. Document every step as SQL so it can be rerun.
4. A Small App's Data Layer
Pick a language you know — Python, PHP, or another — and write a small program (a to-do list or bookmark manager) that stores its data in SQLite, using parameterized queries for every statement.