Subqueries & Views - Activity 07

Welcome to Activity 07! This template teaches you advanced query techniques including subqueries, views, and Common Table Expressions (CTEs) using a realistic sales database system.

🎯 Learning Objectives

By completing this activity, you will:

• Master subqueries in WHERE, FROM, and SELECT clauses
• Understand correlated vs non-correlated subqueries
• Create and use database views for query simplification
• Write Common Table Expressions (CTEs) for complex queries
• Apply advanced filtering with EXISTS and NOT EXISTS
• Analyze data relationships across multiple tables

🚀 Getting Started (See Results in 30 Seconds!)

IMPORTANT: This template includes WORKING CODE! You can see results immediately:

Option One: SQLite Browser (Easiest - No Setup Required)

1. Download SQLite Browser (if you don't have it):

   • Visit: https://sqlitebrowser.org/
   • Download for your operating system
   • Install (takes 2 minutes)

2. Open the database files:

   • Launch DB Browser for SQLite
   • Click "New Database" -> Save as sales.db
   • Click "Execute SQL" tab
   • Open sqlite/schema.sql -> Click "Execute" ▶️
   • Open sample-data.sql -> Click "Execute" ▶️
   • Click "Browse Data" tab -> See 30 customers, 40 products immediately!

3. Run the queries:

   • Open sqlite/queries.sql
   • Copy Query 1 (Simple Subquery)
   • Paste in "Execute SQL" tab -> Click "Execute" ▶️
   • You just ran your first subquery! 🎉

Option 2: Docker (One Command Setup)

# Make sure you're in the activity folder, then:
docker-compose up -d

# Access MySQL:
docker exec -it w4-mysql mysql -u root -plearning123 contacts_db
# Then load schema:
source /docker-entrypoint-initdb.d/schema.sql;

# Access PostgreSQL:
docker exec -it w4-postgres psql -U postgres contacts_db
# Then load schema:
\i /docker-entrypoint-initdb.d/schema.sql

Option 3: Online (Supabase - Cloud PostgreSQL)

1. Visit https://supabase.com and create free account
2. Create new project (takes 2 minutes)
3. Open SQL Editor in dashboard
4. Copy/paste postgresql/schema.sql and sample-data.sql
5. Run queries immediately!

🎯 What's Already Working

70% of the code is implemented for you:

• ✅ Complete 4-table sales schema (customers, products, orders, order_items)
• ✅ Sample data: 30 customers, 40 products, 80 orders, 200+ order items
• ✅ Query 1: Simple subquery in WHERE clause (working)
• ✅ Query 2: Subquery with IN operator (working)
• ✅ Query 3: Subquery in SELECT clause (working)
• ✅ Query 4: Correlated subquery (working)
• ✅ Query 5: Subquery in FROM clause (inline view) (working)
• ✅ Query 6: CREATE VIEW example (working)
• ✅ Query 7: CTE (Common Table Expression) (working)
• ⚠️ Query 8: Above-average order totals (TODO for you)
• ⚠️ Query 9: Product revenue ranking (TODO for you)
• ⚠️ Query 10: Sales dashboard view (TODO for you)

📝 Your Learning Tasks

1. First, test the 7 working queries to understand subquery patterns
2. Then complete the 3 TODO queries following the examples
3. Finally, try the bonus challenges to extend your skills

📋 Tasks to Complete

TODO 1: Above-Average Order Totals (Medium)

Location: sqlite/queries.sql line 253 Your Task: Find customers with above-average order totals using a subquery

Success Criteria:

• Must show: customer_name, email, total_spent, avg_order_value
• Only include customers where total_spent > average total_spent
• Filter to completed orders only
• Order by total_spent DESC
• Should return 8-12 customers

Hints:

• Use subquery in WHERE clause to calculate AVG(total_amount)
• You'll need to sum orders per customer first
• Structure: WHERE total_spent > (SELECT AVG(...) FROM ...)
• Use GROUP BY customer_id with HAVING clause

Expected Output:

customer_name | email                 | total_spent | avg_order_value
Carol White   | carol.w@email.com     | 869.92      | 289.97
Alice Johnson | alice.j@email.com     | 729.89      | 182.47
...

Why This Matters: Identifying high-value customers helps target marketing campaigns and loyalty programs effectively.

TODO 2: Product Revenue Ranking (Hard)

Location: sqlite/queries.sql line 283 Your Task: Create product ranking by revenue using subquery or window function

Success Criteria:

• Must show: rank, product_name, category, total_revenue
• Calculate total_revenue as SUM(quantity * unit_price)
• Rank products 1-40 by revenue (highest first)
• Include products with 0 sales (revenue = 0)
• Round revenue to 2 decimal places

Hints:

• Use ROW_NUMBER() OVER (ORDER BY total_revenue DESC) for ranking
• Join products with order_items, use LEFT JOIN for products with no sales
• Use COALESCE(SUM(...), 0) to handle NULL revenues
• Group by product before applying ranking

Expected Output:

rank | product_name        | category     | total_revenue
1    | Smart Watch         | Electronics  | 2249.91
2    | Denim Jeans         | Clothing     | 1879.82
3    | Laptop Stand        | Electronics  | 1599.60
...
40   | Product X           | Books        | 0.00

Why This Matters: Revenue ranking helps with inventory management, promotional planning, and understanding which products drive business success.

TODO 3: Sales Dashboard View (Challenge)

Location: sqlite/queries.sql line 323 Your Task: Create a comprehensive sales dashboard view using CTEs or complex JOINs

Success Criteria:

• Create a view named: sales_dashboard
• Combine data from customers, orders, products, order_items
• Include these columns:
  • customer_name, customer_city, loyalty_tier
  • order_id, order_date, order_status
  • product_name, category, quantity
  • unit_price, line_total (quantity * unit_price)
• Only include completed orders
• View should return 150+ rows (all completed order items)

Hints:

• Option One: Use CTEs to break down complex query:
  sql

  WITH order_details AS (
      SELECT ... FROM orders WHERE status = 'completed'
  ),
  enriched_items AS (
      SELECT ... JOIN order_details ...
  )
  CREATE VIEW sales_dashboard AS
  SELECT ... FROM enriched_items ...
  

• Option 2: Create view directly with JOINs:
  sql

  CREATE VIEW sales_dashboard AS
  SELECT
      c.name AS customer_name,
      c.city AS customer_city,
      ...
  FROM customers c
  JOIN orders o ON c.id = o.customer_id
  JOIN order_items oi ON o.id = oi.order_id
  JOIN products p ON oi.product_id = p.id
  WHERE o.status = 'completed';
  

Expected Output (when querying view):

SELECT * FROM sales_dashboard LIMIT 10;

customer_name | customer_city | order_id | product_name       | quantity | line_total
Alice Johnson | NYC           | 1        | Wireless Headphones| 2        | 159.98
Alice Johnson | NYC           | 1        | USB-C Cable        | 1        | 12.99
Bob Smith     | NYC           | 2        | Running Shoes      | 1        | 89.99
...

Why This Matters: Dashboard views simplify complex business reporting, provide consistent data access, and enable non-technical users to query business intelligence data easily.

🛠 Database Reference

Schema Structure (4 Tables)

Customers Table (30 records)

Products Table (40 records)

Orders Table (80 records)

Order Items Table (200+ records)

Sample Data Summary

• Total Customers: 30 (across 5 cities)
• Total Products: 40 (across 5 categories)
• Total Orders: 80 (spanning 6 months: Aug 2023 - Mar 2024)
• Total Order Items: 200+ (2-5 items per order average)
• Order Status Distribution:
  • Completed: ~56 (70%)
  • Pending: ~16 (20%)
  • Cancelled: ~8 (10%)

Key SQL Concepts Used

-- Simple subquery in WHERE
WHERE customer_id IN (SELECT customer_id FROM orders WHERE ...)

-- Subquery in SELECT (scalar subquery)
SELECT name, (SELECT COUNT(*) FROM orders WHERE ...) AS order_count

-- Correlated subquery (references outer query)
WHERE order_count > (SELECT AVG(order_count) FROM ...)

-- Subquery in FROM (derived table)
FROM (SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id) AS totals

-- CREATE VIEW (reusable query)
CREATE VIEW customer_summary AS
SELECT ... FROM ... JOIN ... GROUP BY ...

-- Common Table Expression (CTE)
WITH sales AS (SELECT ...),
     summary AS (SELECT ... FROM sales)
SELECT * FROM summary;

-- Window functions (ranking)
ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rank

🧪 Testing Your Work

Manual Testing Checklist

• All 7 working queries execute without errors
• TODO 8 returns 8-12 customers with above-average spending
• TODO 9 returns 40 products ranked by revenue
• TODO 10 creates sales_dashboard view returning 150+ rows
• Bonus queries (if attempted) produce reasonable results
• No syntax errors in your SQL code
• Views can be queried like regular tables

Debugging Tips

1. "No such table" error:

   • Make sure you ran schema.sql AND sample-data.sql
   • Check you're in the correct database

2. Subquery returns multiple rows:

   • Use IN instead of = for multi-row subqueries
   • Or use ANY/ALL operators
   • Scalar subqueries must return single value

3. Correlated subquery too slow:

   • Check if indexes exist on join columns
   • Consider rewriting with JOINs instead
   • Use EXPLAIN to analyze query plan

4. View creation fails:

   • Drop existing view first: DROP VIEW IF EXISTS view_name;
   • Check all column names are valid
   • Ensure all referenced tables exist

5. Window function not supported:

   • Older SQLite versions don't support window functions
   • Use SQLite 3.25+ or switch to PostgreSQL/MySQL 8.0+

Expected Results

🎓 Extension Challenges

Ready for more? Try these bonus features:

Beginner Extensions

• Find products never ordered: Use NOT EXISTS subquery
• Calculate city-wise revenue: Group by customer city with subquery
• Find customers with only cancelled orders: Use subquery filtering

Advanced Extensions

• Running totals by customer: Use window functions with PARTITION BY
• Month-over-month growth: Compare current vs previous month revenue
• Customer cohort analysis: Group customers by registration month

Creative Extensions

• Top 10 products per category: Use ROW_NUMBER() with PARTITION BY
• Customer lifetime value prediction: Analyze order patterns
• Seasonal trends: Identify best-selling products by month

📚 Additional Resources

Subquery Learning

• SQL Subqueries Tutorial
• Correlated Subqueries Explained
• Window Functions Guide

Views & CTEs

• Database Views Explained
• CTE Best Practices
• Materialized Views

Advanced SQL Concepts

• Recursive CTEs: Build hierarchical queries
• Lateral Joins: Advanced subquery technique (PostgreSQL)
• Query Optimization: EXPLAIN and index strategies

🔍 Database Syntax Comparison

Subquery Differences

View Syntax

SQLite & MySQL:

CREATE VIEW view_name AS
SELECT ... FROM ... WHERE ...;

PostgreSQL (with options):

CREATE OR REPLACE VIEW view_name AS
SELECT ... FROM ... WHERE ...;

-- Materialized view (PostgreSQL only)
CREATE MATERIALIZED VIEW mv_name AS
SELECT ... FROM ... WHERE ...;
REFRESH MATERIALIZED VIEW mv_name;

Window Function Syntax

All three databases support similar window function syntax:

SELECT
    name,
    revenue,
    ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rank,
    SUM(revenue) OVER (PARTITION BY category ORDER BY date) AS running_total
FROM products;

🏆 Success Criteria

Your project is complete when:

• ✅ Schema created with 4 tables and sample data loaded
• ✅ All 7 working queries execute without errors
• ✅ TODO 8 completed (above-average order totals)
• ✅ TODO 9 completed (product revenue ranking)
• ✅ TODO 10 completed (sales dashboard view)
• ✅ Code is clean with proper comments
• ✅ You understand the difference between subquery types
• ✅ You can explain when to use views vs CTEs

🎉 Congratulations!

Once you complete this activity, you'll have:

• Mastered 5 different subquery patterns
• Created reusable database views
• Written complex CTEs for data analysis
• Gained confidence with advanced SQL queries

What You've Learned

• Subqueries in WHERE, FROM, and SELECT clauses
• Correlated vs non-correlated subqueries
• Creating and using database views
• Common Table Expressions (CTEs)
• Window functions for ranking and analysis
• Query optimization techniques

This skillset will serve you well in data analysis, business intelligence, and backend development roles!

Need Help?

• Review the working queries (1-7) to understand patterns
• Check the hints in each TODO section
• Use the debugging checklist above
• Test queries one step at a time
• Try breaking complex queries into smaller parts

Ready to Submit? Complete this activity and submit through the Activity Submission Form

Happy querying! 🗄️✨