-- Create the sales table with realistic e-commerce data
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10, 2),
quantity INT,
sale_date DATE,
region VARCHAR(50),
customer_id INT
);
-- Insert sample e-commerce data
INSERT INTO sales VALUES
(1, 'Laptop Pro 15"', 'Electronics', 1299.99, 2, '2024-01-15', 'North', 101),
(2, 'Wireless Mouse', 'Electronics', 29.99, 5, '2024-01-15', 'North', 102),
(3, 'Office Chair', 'Furniture', 199.99, 1, '2024-01-16', 'South', 103),
(4, 'USB-C Hub', 'Electronics', 49.99, 3, '2024-01-16', 'East', 104),
(5, 'Standing Desk', 'Furniture', 399.99, 1, '2024-01-17', 'West', 105),
(6, 'Laptop Pro 15"', 'Electronics', 1299.99, 1, '2024-01-17', 'South', 106),
(7, 'Desk Lamp', 'Furniture', 39.99, 4, '2024-01-18', 'North', 107),
(8, 'Webcam HD', 'Electronics', 79.99, 2, '2024-01-18', 'East', 108),
(9, 'Ergonomic Keyboard', 'Electronics', 89.99, 3, '2024-01-19', 'West', 109),
(10, 'Monitor Stand', 'Furniture', 34.99, 6, '2024-01-19', 'South', 110);
-- Calculate total revenue
SELECT
SUM(price * quantity) AS total_revenue
FROM sales;
-- Your turn! Calculate revenue by region
SELECT
region,
SUM(price * quantity) AS regional_revenue
FROM sales
GROUP BY region
ORDER BY regional_revenue DESC;
-- Count total number of sales
SELECT COUNT(*) AS total_sales FROM sales;
-- Count unique customers
SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM sales;
-- Count sales by category
SELECT
category,
COUNT(*) AS sales_count
FROM sales
GROUP BY category;
-- Calculate average sale value
SELECT
AVG(price * quantity) AS average_sale_value
FROM sales;
-- Average price by category
SELECT
category,
AVG(price) AS average_price,
MIN(price) AS lowest_price,
MAX(price) AS highest_price
FROM sales
GROUP BY category;
-- Sales summary by category
SELECT
category,
COUNT(*) AS total_sales,
SUM(quantity) AS units_sold,
SUM(price * quantity) AS revenue,
AVG(price * quantity) AS avg_sale_value
FROM sales
GROUP BY category
ORDER BY revenue DESC;
-- Find categories with revenue over $1000
SELECT
category,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY category
HAVING SUM(price * quantity) > 1000;
-- Find regions with more than 3 sales
SELECT
region,
COUNT(*) AS sale_count,
SUM(price * quantity) AS revenue
FROM sales
GROUP BY region
HAVING COUNT(*) > 2
ORDER BY revenue DESC;
-- Comprehensive sales analytics query
SELECT
category,
region,
COUNT(*) AS transactions,
COUNT(DISTINCT customer_id) AS unique_customers,
SUM(quantity) AS units_sold,
SUM(price * quantity) AS total_revenue,
AVG(price * quantity) AS avg_transaction_value,
MIN(price) AS min_price,
MAX(price) AS max_price
FROM sales
GROUP BY category, region
HAVING SUM(price * quantity) > 100
ORDER BY total_revenue DESC;
-- ❌ Wrong:
SELECT category, product_name, SUM(quantity)
FROM sales GROUP BY category;
-- ✅ Correct:
SELECT category, SUM(quantity)
FROM sales GROUP BY category;
-- ❌ Wrong:
SELECT * FROM sales HAVING price > 100;
-- ✅ Correct:
SELECT * FROM sales WHERE price > 100;
-- Handle NULL prices:
SELECT SUM(COALESCE(price, 0) * quantity) AS total_revenue
FROM sales;
SUM(column) -- Total of all values
COUNT(*) -- Count all rows
COUNT(column) -- Count non-NULL values
COUNT(DISTINCT col) -- Count unique values
AVG(column) -- Average value
MIN(column) -- Smallest value
MAX(column) -- Largest value
GROUP BY column1, column2 -- Group results
HAVING condition -- Filter groups
ORDER BY aggregate_function -- Sort by calculated values
SELECT
dimension_column,
COUNT(*) as count,
SUM(value_column) as total,
AVG(value_column) as average
FROM table
GROUP BY dimension_column
ORDER BY total DESC;
SELECT
category,
SUM(revenue) as total_revenue
FROM sales
GROUP BY category
HAVING SUM(revenue) > threshold
ORDER BY total_revenue DESC
LIMIT 10;
With aggregations alone, you can answer powerful questions:
SELECT AVG(salary) FROM employeesSELECT department, SUM(sales) GROUP BY departmentHAVING SUM(amount) > 1000But you CAN'T easily answer questions like:
These questions require comparing individual rows to aggregate values - and that's where aggregations alone fall short.
Right now, to find employees earning above average, you'd need TWO separate queries:
-- Query 1: Calculate the average salary
SELECT AVG(salary) as avg_salary FROM employees;
-- Result: 74,375
-- Query 2: Manually plug in the result
SELECT name, department, salary
FROM employees
WHERE salary > 74375; -- Hardcoded value!
The problems with this approach:
In the next lesson, you'll learn to nest queries inside each other:
-- One query that always works, always current!
SELECT name, department, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
How it works:
SELECT AVG(salary) FROM employees -> returns 74,375WHERE salary > 74375┌──────────────────────────────────────────────────┐
│ OUTER QUERY: "Give me employees │
│ WHERE salary is greater than..." │
│ │
│ ┌────────────────────────────────────────┐ │
│ │ INNER QUERY: "...the average salary │ │
│ │ which is currently 74,375" │ │
│ └────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────┘
Key insight: The inner query returns a value that the outer query uses. This is the foundation of nested thinking in SQL.
Before moving to Lesson 6, make sure you can confidently:
Try this exercise to prepare for subqueries:
Part 1: Write a query to find the average price of all products
SELECT AVG(price) as avg_price FROM products;
-- Note down the result: _______
Part 2: Write a separate query to find products above that average
SELECT name, price FROM products WHERE price > _______;
-- Use the number from Part 1
Coming in Lesson 6: You'll combine these into ONE powerful query that always stays current:
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
This is your gateway to advanced SQL - nested queries that think in layers!