Dataset Overview:
Assume weโre working with two tables:
1. Customers
| customer_id | name | country |
|---|---|---|
| 1 | Alice Smith | USA |
| 2 | John Lee | Canada |
| 3 | Sara Khan | UK |
2. Orders
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 101 | 1 | 2024-12-01 | 120.00 |
| 102 | 2 | 2024-12-05 | 150.00 |
| 103 | 1 | 2024-12-09 | 200.00 |
๐งช PART 1: Basic SELECT Queries
Q1. Write a query to get all customers from the table.
SELECT ___________ FROM customers;Q2. Write a query to get the names of customers from the USA.
SELECT name FROM customers WHERE ___________ = 'USA';๐งฎ PART 2: Aggregation
Q3. Write a query to find the total order amount.
SELECT ________(amount) FROM orders;Q4. Get the average order amount.
SELECT _____________(amount) FROM orders;๐ PART 3: JOINING Tables
Q5. Write a query to show all orders along with customer names.
SELECT o.order_id, c.name, o.amount
FROM orders o
JOIN customers c ON o.____________ = c.____________;
๐ PART 4: GROUP BY and Filtering
Q6. Find total amount spent per customer.
SELECT c.name, SUM(o.amount) AS total_spent
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY ________;
Q7. Show customers who have spent more than $200 in total.
-- Build on Q6 by adding:
HAVING SUM(o.amount) > ________;
๐ Challenge Question
Q8. List each country and the average amount spent by its customers.
SELECT c.country, AVG(o.amount) AS avg_spent
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY ___________;
