Day 1
-- Create database
CREATE DATABASE finance_training;
USE finance_training;
-- Create tables
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE,
email VARCHAR(100)
);
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50),
location VARCHAR(50),
budget DECIMAL(12,2)
);
-- Insert sample data
INSERT INTO employees VALUES
(101, 'Rahul', 'Sharma', 'Finance', 75000.00, '2020-01-15', '[Link]@[Link]'),
(102, 'Priya', 'Singh', 'Finance', 82000.00, '2019-03-22', '[Link]@[Link]'),
(103, 'Amit', 'Patel', 'IT', 95000.00, '2018-06-10', '[Link]@[Link]'),
(104, 'Sneha', 'Kumar', 'IT', 88000.00, '2021-02-28', '[Link]@[Link]'),
(105, 'Ravi', 'Reddy', 'Sales', 65000.00, '2020-07-19', '[Link]@[Link]'),
(106, 'Anjali', 'Verma', 'Sales', 70000.00, '2019-11-05', '[Link]@[Link]'),
(107, 'Vikram', 'Joshi', 'HR', 60000.00, '2021-04-12', NULL),
(108, 'Pooja', 'Mehta', 'Finance', 78000.00, '2020-09-30', '[Link]@[Link]'),
(109, 'Karan', 'Gupta', 'IT', 92000.00, '2019-08-17', '[Link]@[Link]'),
(110, 'Deepa', 'Iyer', 'HR', 58000.00, '2022-01-20', '[Link]@[Link]');
INSERT INTO departments VALUES
(1, 'Finance', 'Mumbai', 5000000.00),
(2, 'IT', 'Bangalore', 8000000.00),
(3, 'Sales', 'Delhi', 3000000.00),
(4, 'HR', 'Mumbai', 2000000.00),
(5, 'Marketing', 'Pune', 2500000.00);
PROBLEM 1: SELECT & WHERE
Question
Retrieve all employees from the Finance department earning > ₹75,000. Show full name,
department, salary. Sort by salary descending.
Solution
SELECT
CONCAT(first_name, ' ', last_name) AS full_name,
department,
salary
FROM employees
WHERE department = 'Finance'
AND salary > 75000
ORDER BY salary DESC;
PROBLEM 2: DISTINCT & COUNT
Solution
-- Part 1: Counts
SELECT
COUNT(DISTINCT department) AS unique_departments,
COUNT(*) AS total_employees
FROM employees;
-- Part 2: List departments
SELECT DISTINCT department
FROM employees
ORDER BY department;
PROBLEM 3: ORDER BY Multiple Columns
Solution
SELECT
first_name,
department,
salary
FROM employees
ORDER BY
department ASC,
salary DESC;
PROBLEM 4: Complex WHERE
Question
Find employees who meet ANY criteria:
1. IT with salary > 90,000
2. Sales department
3. Hired in 2020
SELECT
employee_id,
first_name,
department,
salary,
hire_date
FROM employees
WHERE
(department = 'IT' AND salary > 90000)
OR department = 'Sales'
OR YEAR(hire_date) = 2020 -- YEAR() works in MySQL
ORDER BY employee_id;
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
employee_id INT,
product_category VARCHAR(50),
sale_amount DECIMAL(10,2),
sale_date DATE,
region VARCHAR(50)
);
INSERT INTO sales VALUES
(1, 105, 'Electronics', 15000.00, '2024-01-10', 'North'),
(2, 105, 'Electronics', 22000.00, '2024-01-15', 'North'),
(3, 106, 'Furniture', 35000.00, '2024-01-12', 'South'),
(4, 106, 'Electronics', 18000.00, '2024-01-20', 'South'),
(5, 105, 'Furniture', 28000.00, '2024-02-05', 'North'),
(6, 106, 'Clothing', 12000.00, '2024-02-08', 'South'),
(7, 105, 'Electronics', 25000.00, '2024-02-15', 'North'),
(8, 106, 'Furniture', 42000.00, '2024-02-18', 'South'),
(9, 105, 'Clothing', 8000.00, '2024-03-02', 'North'),
(10, 106, 'Electronics', 19000.00, '2024-03-10', 'South');
PROBLEM 6: Basic Aggregations (MySQL)
Solution
SELECT
SUM(sale_amount) AS total_revenue,
AVG(sale_amount) AS avg_sale,
MIN(sale_amount) AS min_sale,
MAX(sale_amount) AS max_sale,
COUNT(*) AS transaction_count
FROM sales;
PROBLEM 7: GROUP BY with HAVING (MySQL)
Solution
SELECT
product_category,
SUM(sale_amount) AS total_sales,
AVG(sale_amount) AS avg_sale_amount,
COUNT(*) AS num_transactions
FROM sales
GROUP BY product_category
HAVING SUM(sale_amount) > 50000
ORDER BY total_sales DESC;
PROBLEM 8: GROUP BY Multiple Columns (MySQL)
Solution
SELECT
region,
product_category,
SUM(sale_amount) AS total_sales,
COUNT(*) AS num_transactions
FROM sales
GROUP BY region, product_category
HAVING COUNT(*) > 1
ORDER BY region, total_sales DESC;
PROBLEM 9: WHERE + GROUP BY + HAVING (MySQL)
Solution
SELECT
region,
COUNT(*) AS num_sales,
AVG(sale_amount) AS avg_sale_amount
FROM sales
WHERE sale_date >= '2024-02-01'
GROUP BY region
HAVING AVG(sale_amount) > 20000
ORDER BY avg_sale_amount DESC;
Joins
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
city VARCHAR(50),
credit_limit DECIMAL(10,2)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10,2),
status VARCHAR(20)
);
CREATE TABLE payments (
payment_id INT PRIMARY KEY,
order_id INT,
payment_date DATE,
amount DECIMAL(10,2),
payment_method VARCHAR(20)
);
INSERT INTO customers VALUES
(1, 'Reliance Industries', 'Mumbai', 10000000.00),
(2, 'Tata Steel', 'Jamshedpur', 5000000.00),
(3, 'Infosys', 'Bangalore', 8000000.00),
(4, 'HDFC Bank', 'Mumbai', 12000000.00),
(5, 'Wipro', 'Bangalore', 6000000.00);
INSERT INTO orders VALUES
(101, 1, '2024-01-15', 250000.00, 'Completed'),
(102, 1, '2024-02-20', 180000.00, 'Completed'),
(103, 2, '2024-01-25', 320000.00, 'Completed'),
(104, 3, '2024-02-10', 150000.00, 'Pending'),
(105, 2, '2024-03-05', 280000.00, 'Completed'),
(106, 6, '2024-03-15', 95000.00, 'Completed');
INSERT INTO payments VALUES
(1, 101, '2024-01-20', 250000.00, 'Bank Transfer'),
(2, 102, '2024-02-25', 180000.00, 'Cheque'),
(3, 103, '2024-02-01', 320000.00, 'Bank Transfer'),
(4, 105, '2024-03-10', 280000.00, 'UPI'),
(5, 107, '2024-03-20', 50000.00, 'Cash');
[Link]
PROBLEM 11: INNER JOIN (MySQL)
Solution
SELECT
c.customer_name,
[Link],
o.order_id,
o.total_amount,
o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE [Link] = 'Completed'
ORDER BY o.total_amount DESC;
PROBLEM 12: LEFT JOIN (MySQL)
Solution
SELECT
c.customer_name,
[Link],
COUNT(o.order_id) AS num_orders,
IFNULL(SUM(o.total_amount), 0) AS total_order_amount -- IFNULL for MySQL
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, [Link]
ORDER BY total_order_amount DESC;
PROBLEM 13: RIGHT JOIN (MySQL)
Solution
SELECT
o.order_id,
o.customer_id AS orphan_customer_id,
o.total_amount,
[Link]
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
PROBLEM 14: FULL OUTER JOIN (MySQL Workaround)
MySQL Solution
-- MySQL doesn't have FULL OUTER JOIN, use UNION
SELECT
o.order_id,
o.total_amount,
p.payment_id,
[Link] AS paid_amount,
CASE
WHEN o.order_id IS NULL THEN 'Payment Without Order'
WHEN p.payment_id IS NULL THEN 'Unpaid Order'
WHEN o.total_amount = [Link] THEN 'Fully Paid'
ELSE 'Amount Mismatch'
END AS match_status
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
UNION
SELECT
o.order_id,
o.total_amount,
p.payment_id,
[Link] AS paid_amount,
'Payment Without Order' AS match_status
FROM orders o
RIGHT JOIN payments p ON o.order_id = p.order_id
WHERE o.order_id IS NULL
ORDER BY order_id;
Day 2
CREATE DATABASE shopeasy;
USE shopeasy;
-- Table 1: CUSTOMERS
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
city VARCHAR(50),
signup_date DATE
);
-- Table 2: ORDERS
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10,2),
status VARCHAR(20)
);
-- Table 3: PAYMENTS
CREATE TABLE payments (
payment_id INT PRIMARY KEY,
order_id INT,
payment_date DATE,
amount_paid DECIMAL(10,2),
payment_method VARCHAR(20)
);
-- Table 4: PRODUCTS
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10,2)
);
-- Table 5: ORDER_ITEMS (links orders to products)
CREATE TABLE order_items (
item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
item_total DECIMAL(10,2)
);
Step 2: Insert Sample Data
-- Insert CUSTOMERS
INSERT INTO customers VALUES
(1, 'Amit Sharma', 'Mumbai', '2023-01-15'),
(2, 'Priya Patel', 'Delhi', '2023-02-20'),
(3, 'Rahul Singh', 'Bangalore', '2023-03-10'),
(4, 'Sneha Gupta', 'Chennai', '2023-04-05'),
(5, 'Vikram Reddy', 'Hyderabad', '2023-05-12');
-- Insert ORDERS (Notice: customer_id 6 doesn't exist!)
INSERT INTO orders VALUES
(101, 1, '2024-01-10', 5000.00, 'Completed'),
(102, 1, '2024-01-25', 3000.00, 'Completed'),
(103, 2, '2024-01-15', 7500.00, 'Completed'),
(104, 3, '2024-02-01', 2000.00, 'Pending'),
(105, 2, '2024-02-10', 4500.00, 'Completed'),
(106, 6, '2024-02-15', 1500.00, 'Completed');
-- Insert PAYMENTS (Notice: order_id 107 doesn't exist!)
INSERT INTO payments VALUES
(1, 101, '2024-01-12', 5000.00, 'Credit Card'),
(2, 102, '2024-01-27', 3000.00, 'UPI'),
(3, 103, '2024-01-17', 7500.00, 'Net Banking'),
(4, 105, '2024-02-12', 4500.00, 'Credit Card'),
(5, 107, '2024-02-20', 2500.00, 'Cash');
-- Insert PRODUCTS
INSERT INTO products VALUES
(201, 'Laptop', 'Electronics', 45000.00),
(202, 'Mobile Phone', 'Electronics', 25000.00),
(203, 'Headphones', 'Electronics', 2000.00),
(204, 'Office Chair', 'Furniture', 5000.00),
(205, 'Desk', 'Furniture', 8000.00);
-- Insert ORDER_ITEMS
INSERT INTO order_items VALUES
(1, 101, 201, 1, 45000.00),
(2, 102, 203, 1, 2000.00),
(3, 103, 202, 2, 50000.00),
(4, 103, 203, 1, 2000.00),
(5, 104, 204, 1, 5000.00),
(6, 105, 205, 1, 8000.00);
SELECT * FROM customers;
SELECT
o.order_id,
c.customer_name,
[Link],
o.order_date,
o.total_amount,
[Link]
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date;
SELECT
c.customer_id,
c.customer_name,
[Link],
COUNT(o.order_id) AS total_orders,
IFNULL(SUM(o.total_amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, [Link]
ORDER BY total_spent DESC;
SELECT
o.order_id,
o.customer_id AS order_customer_id,
c.customer_name,
[Link],
o.total_amount,
[Link],
CASE
WHEN c.customer_id IS NULL THEN 'DATA ISSUE: No customer found'
ELSE 'Valid'
END AS data_quality
FROM customers AS c
RIGHT JOIN orders AS o ON c.customer_id = o.customer_id
ORDER BY o.order_id;
SELECT
o.order_id,
o.total_amount AS order_amount,
p.payment_id,
p.amount_paid,
p.payment_method,
CASE
WHEN p.payment_id IS NULL THEN ' Unpaid Order'
WHEN o.total_amount = p.amount_paid THEN ' Fully Paid'
WHEN o.total_amount > p.amount_paid THEN ' Partially Paid'
ELSE ' Overpaid'
END AS payment_status
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
UNION
SELECT
o.order_id,
o.total_amount AS order_amount,
p.payment_id,
p.amount_paid,
p.payment_method,
'Payment Without Order' AS payment_status
FROM orders o
RIGHT JOIN payments p ON o.order_id = p.order_id
WHERE o.order_id IS NULL
ORDER BY order_id;
CapStone
Capstone Activity: SQL Assessment for Business Analyst Trainees
Level: Intermediate
Business Scenario
You have recently joined Retail Insight Pvt. Ltd. as a junior analyst. Your manager has
provided
partial data extracts from the company’s sales database. You are required to analyse customer
purchasing behaviour and generate insights for the upcoming strategy meeting.
The following three tables are provided:
1. customers
Customer_id Customer_name City Signup_date
101 Maya Singh Mumbai 2021-04-12
102 Rohan Verma Delhi 2021-06-21
103 Fatima Khan Bangalore 2021-03-08
104 Arjun Patel Chennai 2021-11-17
2. products
Product_id Product_name Category Price
P01 Laptop Electronics 55000
P02 Headphones Accessories 2500
P03 Chair Furniture 4500
P04 Phone Electronics 30000
3. sales
Sale_id Customer_id Product_id Quantity Sale_date
1 101 P01 1 2023-05-13
2 102 P02 2 2023-05-21
3 101 P03 1 2023-06-01
4 104 P04 1 2023-06-10
5 103 P02 3 2023-06-11
Capstone Tasks
Task 1: Customer Purchase Summary
Write a query to display each customer’s name, city, and the total amount they have spent so
far. (Hint: Use JOINs and aggregations)
Task 2: Category-wise Revenue
Find the total revenue generated from each product category. (Hint: JOIN products with
sales and
group by category)
Task 3: Identify High-Value Customers
Retrieve details of customers who have spent more than ₹40,000 in total. (Hint: Use a
subquery or
having clause)
Task 4: Monthly Sales Trend
Show month-wise total sales (revenue) for 2023. (Hint: Use DATE functions + GROUP BY)
Task 5: Most Popular Product
Find the product that has been purchased the most (highest total quantity).
Return product name, category, and total quantity sold.
Task 6: Customers Without Purchases
Identify customers who have never made a purchase. (Hint: Use LEFT JOIN and check for
NULL)
Task 7: First Purchase Date per Customer
List each customer with the date of their first purchase.
Sort the result by earliest first purchase
Solution
CREATE DATABASE IF NOT EXISTS retail_insight;
USE retail_insight;
CREATE TABLE customers (
Customer_id INT PRIMARY KEY,
Customer_name VARCHAR(100) NOT NULL,
City VARCHAR(50),
Signup_date DATE
);
CREATE TABLE products (
Product_id VARCHAR(10) PRIMARY KEY,
Product_name VARCHAR(100) NOT NULL,
Category VARCHAR(50),
Price DECIMAL(10, 2)
);
CREATE TABLE sales (
Sale_id INT PRIMARY KEY,
Customer_id INT,
Product_id VARCHAR(10),
Quantity INT,
Sale_date DATE,
FOREIGN KEY (Customer_id) REFERENCES customers(Customer_id),
FOREIGN KEY (Product_id) REFERENCES products(Product_id)
);
INSERT INTO customers VALUES
(101, 'Maya Singh', 'Mumbai', '2021-04-12'),
(102, 'Rohan Verma', 'Delhi', '2021-06-21'),
(103, 'Fatima Khan', 'Bangalore', '2021-03-08'),
(104, 'Arjun Patel', 'Chennai', '2021-11-17');
INSERT INTO products VALUES
('P01', 'Laptop', 'Electronics', 55000),
('P02', 'Headphones', 'Accessories', 2500),
('P03', 'Chair', 'Furniture', 4500),
('P04', 'Phone', 'Electronics', 30000);
INSERT INTO sales VALUES
(1, 101, 'P01', 1, '2023-05-13'),
(2, 102, 'P02', 2, '2023-05-21'),
(3, 101, 'P03', 1, '2023-06-01'),
(4, 104, 'P04', 1, '2023-06-10'),
(5, 103, 'P02', 3, '2023-06-11');
-- TASK 1: CUSTOMER PURCHASE SUMMARY
SELECT
c.Customer_name,
[Link],
SUM([Link] * [Link]) AS Total_Spent
FROM customers c
INNER JOIN sales s ON c.Customer_id = s.Customer_id
INNER JOIN products p ON s.Product_id = p.Product_id
GROUP BY c.Customer_id, c.Customer_name, [Link]
ORDER BY Total_Spent DESC;
-- TASK 2: CATEGORY-WISE REVENUE
SELECT
[Link],
SUM([Link] * [Link]) AS Total_Revenue
FROM sales s
INNER JOIN products p ON s.Product_id = p.Product_id
GROUP BY [Link]
ORDER BY Total_Revenue DESC;
-- TASK 3: IDENTIFY HIGH-VALUE CUSTOMERS
SELECT
c.Customer_id,
c.Customer_name,
[Link],
SUM([Link] * [Link]) AS Total_Spent
FROM customers c
INNER JOIN sales s ON c.Customer_id = s.Customer_id
INNER JOIN products p ON s.Product_id = p.Product_id
GROUP BY c.Customer_id, c.Customer_name, [Link]
HAVING SUM([Link] * [Link]) > 40000
ORDER BY Total_Spent DESC;
-- TASK 4: MONTHLY SALES TREND
SELECT
YEAR(s.Sale_date) AS Sale_Year,
MONTH(s.Sale_date) AS Sale_Month,
MONTHNAME(s.Sale_date) AS Month_Name,
SUM([Link] * [Link]) AS Monthly_Revenue
FROM sales s
INNER JOIN products p ON s.Product_id = p.Product_id
WHERE YEAR(s.Sale_date) = 2023
GROUP BY YEAR(s.Sale_date), MONTH(s.Sale_date), MONTHNAME(s.Sale_date)
ORDER BY Sale_Year, Sale_Month;
-- TASK 5: MOST POPULAR PRODUCT
SELECT
p.Product_name,
[Link],
SUM([Link]) AS Total_Quantity_Sold
FROM sales s
INNER JOIN products p ON s.Product_id = p.Product_id
GROUP BY p.Product_id, p.Product_name, [Link]
ORDER BY Total_Quantity_Sold DESC
LIMIT 1;
-- TASK 6: CUSTOMERS WITHOUT PURCHASES
SELECT
c.Customer_id,
c.Customer_name,
[Link],
c.Signup_date
FROM customers c
LEFT JOIN sales s ON c.Customer_id = s.Customer_id
WHERE s.Sale_id IS NULL;
-- TASK 7: FIRST PURCHASE DATE PER CUSTOMER
SELECT
c.Customer_id,
c.Customer_name,
[Link],
MIN(s.Sale_date) AS First_Purchase_Date
FROM customers c
INNER JOIN sales s ON c.Customer_id = s.Customer_id
GROUP BY c.Customer_id, c.Customer_name, [Link]
ORDER BY First_Purchase_Date ASC;