Advanced Queries - Activity 03

Welcome to advanced SQL querying! This activity teaches you to work with multiple tables using JOINs, master filtering, and build complex e-commerce search queries.

🎯 Learning Objectives

By completing this activity, you will:

• Master INNER JOIN to combine related tables
• Write multi-table queries with proper conditions
• Use aggregate functions with GROUP BY
• Filter grouped data with HAVING clause
• Build real-world e-commerce search functionality

🚀 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/
   • Install (takes 2 minutes)

2. Open the database files:

   • Launch DB Browser for SQLite
   • Click "New Database" -> Save as ecommerce.db
   • Click "Execute SQL" tab
   • Open sqlite/schema.sql -> Click "Execute" ▶️
   • Open sample-data.sql -> Click "Execute" ▶️
   • Click "Browse Data" tab -> See customers and orders!

3. Run the queries:

   • Open sqlite/queries.sql
   • Copy Query 1 (View Customers with Orders)
   • Paste in "Execute SQL" tab -> Click "Execute" ▶️
   • You just ran your first JOIN query! 🎉

Option 2: Docker (One Command Setup)

docker-compose up -d
docker exec -it w4-mysql mysql -u root -plearning123 ecommerce_db

🎯 What's Already Working

70% of the code is implemented for you:

• ✅ Complete database schema: customers + orders tables (fully working)
• ✅ Sample data: 20 customers, 50 orders (loaded)
• ✅ Query 1: Simple SELECT from customers (working)
• ✅ Query 2: Simple SELECT from orders (working)
• ✅ Query 3: INNER JOIN customers + orders (working)
• ✅ Query 4: JOIN with WHERE filtering (working)
• ✅ Query 5: JOIN with aggregate COUNT (working)
• ✅ Query 6: GROUP BY with ORDER BY (working)
• ✅ Query 7: Multi-condition filtering (working)
• ⚠️ Query 8: Complex multi-condition filter (TODO for you)
• ⚠️ Query 9: JOIN with aggregation (COUNT orders per customer) (TODO for you)
• ⚠️ Query 10: Complex JOIN with sorting and LIMIT (TODO for you)

📋 Tasks to Complete

TODO 1: Multi-Condition Advanced Filter (Medium)

Location: sqlite/queries.sql line 160 Your Task: Find orders meeting multiple complex criteria

Success Criteria:

• Find orders where amount > $100 AND status = 'completed'
• Show: customer name, order date, amount, status
• Order by amount DESC
• Should return 15-20 orders

Hints:

• Use INNER JOIN to connect customers and orders
• Combine conditions with AND in WHERE clause
• Use ORDER BY for sorting

Why This Matters: Real e-commerce systems need complex filtering for reporting and analytics.

TODO 2: Customer Order Count with Aggregation (Hard)

Location: sqlite/queries.sql line 185 Your Task: Count how many orders each customer has placed

Success Criteria:

• Show: customer name and count of their orders
• Use GROUP BY customer_id
• Order by order count DESC (most orders first)
• Should show all 20 customers

Hints:

• Use COUNT(*) or COUNT(orders.id)
• GROUP BY customers.id
• Use LEFT JOIN to include customers with 0 orders

Expected Output: Customers with order counts ranging from 0-5 orders

Why This Matters: Customer analytics help identify your best customers for loyalty programs.

TODO 3: Top 5 Customers by Total Spending (Challenge)

Location: sqlite/queries.sql line 215 Your Task: Find customers who have spent the most money

Success Criteria:

• Calculate SUM(amount) per customer
• Show: customer name, email, total spent
• Filter to only completed orders
• Order by total DESC
• LIMIT to top 5 customers

Hints:

• Use SUM(amount) aggregate function
• GROUP BY customers.id
• WHERE status = 'completed'
• Use ORDER BY ... DESC LIMIT 5

Expected Output: Top 5 customers with total spending between $500-$1500

Why This Matters: Identifying VIP customers helps target marketing and retention efforts.

🛠 Database Reference

Schema Structure

customers table (20 customers):

• id, first_name, last_name, email, phone, city, state, created_at

orders table (50 orders):

• id, customer_id (FK), order_date, amount, status, created_at

Key SQL Commands Used

-- Basic JOIN
SELECT c.first_name, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- JOIN with filtering
SELECT c.first_name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

-- Aggregate with conditions
SELECT c.first_name, SUM(o.amount) as total
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.id
HAVING SUM(o.amount) > 500;

🧪 Testing Your Work

Manual Testing Checklist

• All 7 working queries execute without errors
• TODO 8 returns 15-20 filtered orders
• TODO 9 shows all customers with order counts
• TODO 10 returns top 5 customers by spending
• Results make logical sense

Expected Results

🎓 Extension Challenges

Beginner Extensions

• Find customers with no orders (LEFT JOIN with NULL check)
• Calculate average order value per customer
• Find orders placed this month

Advanced Extensions

• Create monthly sales report (GROUP BY month)
• Find customers who haven't ordered in 90 days
• Build customer lifetime value (CLV) calculation

🏆 Success Criteria

Your project is complete when:

• ✅ All 7 working queries execute correctly
• ✅ TODO 8 completed (complex filtering)
• ✅ TODO 9 completed (order count aggregation)
• ✅ TODO 10 completed (top customers by spending)
• ✅ You understand JOIN concepts

🎉 Congratulations!

You've mastered:

• INNER JOIN and LEFT JOIN operations
• Aggregate functions (COUNT, SUM)
• GROUP BY and HAVING clauses
• Complex multi-table queries

Next: Multi-table relationships with junction tables!

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

Happy querying! 🗄️✨