Lesson 21 � Advanced

LAST LESSON � SQL Complete!

PRACTICAL
PROJECTS.

Ab tak sab seekh liya � SELECT, JOINs, Transactions, Triggers. Ab time hai real-world projects banane ka. E-commerce database se shuru karo, analytics queries likho, aur performance optimize karo. Yeh wahi cheezein hain jo interviews mein poochte hain aur real jobs mein kaam aati hain.

? 30 min✓ Advanced✓ Prerequisite: Transactions

WHY: Projects kyun zaroori hain?

Sab seekh liya textbook se � lekin real SQL projects mein kya hota hai✓ Companies mein database design karna padta hai, complex queries likhni padti hain, aur performance optimize karna padta hai. Projects se tumhein pata chalega ki sab concepts kaise ek saath kaam karte hain. Interview mein bhi projects dikhao � interviewer impress hoga!

E-COMMERCE

Complete e-commerce database design karo � customers, products, orders, payments. Real-world schema design seekho jo Flipkart, Amazon jaisi companies use karti hain.

ANALYTICS

Data analysis queries likho � revenue reports, customer insights, sales trends. Business decisions lene ke liye data chahiye, aur tum woh data extract karoge.

OPTIMIZATION

Performance tuning seekho � indexes, query optimization, execution plans. Bade datasets pe fast queries likho jo production mein kaam karein.

Project 1: E-Commerce Database

Pehle poora e-commerce database design karo. Char tables � customers, products, orders, order_items. Har table mein proper constraints aur relationships:

-- Project 1: E-Commerce Database
-- Complete e-commerce schema design

CREATE TABLE customers (
 id INT PRIMARY KEY,
 name VARCHAR(100),
 email VARCHAR(100) UNIQUE,
 city VARCHAR(50)
);

CREATE TABLE products (
 id INT PRIMARY KEY,
 name VARCHAR(100),
 category VARCHAR(50),
 price DECIMAL(10,2)
);

CREATE TABLE orders (
 id INT PRIMARY KEY,
 customer_id INT,
 order_date DATE,
 total DECIMAL(10,2),
 FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
 order_id INT,
 product_id INT,
 quantity INT,
 price DECIMAL(10,2),
 PRIMARY KEY (order_id, product_id),
 FOREIGN KEY (order_id) REFERENCES orders(id),
 FOREIGN KEY (product_id) REFERENCES products(id)
);

ℹ

Schema design: Customers mein basic info, products mein catalog, orders mein purchase records, aur order_items mein har order ke items. Foreign keys ensure karti hain ki data consistent rahe � koi orphan order nahi hoga bina customer ke.

Data insert karo

Ab realistic data daalo � 5 customers, 6 products, 10 orders:

-- Customers data
INSERT INTO customers VALUES
(1, 'Aman Kumar', 'aman@email.com', 'Delhi'),
(2, 'Priya Singh', 'priya@email.com', 'Mumbai'),
(3, 'Rahul Verma', 'rahul@email.com', 'Bangalore'),
(4, 'Sneha Patel', 'sneha@email.com', 'Delhi'),
(5, 'Vikram Reddy', 'vikram@email.com', 'Hyderabad');

-- Products data
INSERT INTO products VALUES
(1, 'iPhone 15', 'Electronics', 79999.00),
(2, 'Samsung S24', 'Electronics', 69999.00),
(3, 'Nike Air Max', 'Footwear', 12999.00),
(4, 'Levi\'s Jeans', 'Clothing', 3999.00),
(5, 'MacBook Pro', 'Electronics', 199999.00),
(6, 'Adidas Ultraboost', 'Footwear', 14999.00);

-- Orders data
INSERT INTO orders VALUES
(1, 1, '2024-01-15', 79999.00),
(2, 2, '2024-01-16', 82998.00),
(3, 1, '2024-02-01', 16998.00),
(4, 3, '2024-02-05', 199999.00),
(5, 4, '2024-02-10', 16998.00),
(6, 2, '2024-03-01', 79999.00),
(7, 5, '2024-03-05', 14999.00),
(8, 1, '2024-03-10', 3999.00),
(9, 3, '2024-03-15', 12999.00),
(10, 4, '2024-03-20', 69999.00);

-- Order items
INSERT INTO order_items VALUES
(1, 1, 1, 79999.00),
(2, 2, 1, 69999.00),
(2, 3, 1, 12999.00),
(3, 3, 1, 12999.00),
(3, 4, 1, 3999.00),
(4, 5, 1, 199999.00),
(5, 2, 1, 69999.00),
(5, 3, 1, 12999.00),
(6, 1, 1, 79999.00),
(7, 6, 1, 14999.00),
(8, 4, 1, 3999.00),
(9, 3, 1, 12999.00),
(10, 2, 1, 69999.00);

Project 2: Analytics Queries

Ab data analysis karo � business ke liye important insights nikalo:

1. Top 5 customers by spending

-- Top 5 customers by total spending
SELECT c.name, SUM(o.total) as total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC
LIMIT 5;

ℹ

Insight: Yeh query batata hai ki sabse zyada kaun kharch kar raha hai. Marketing team isko use karke VIP customers ko special offers de sakti hai.

2. Category-wise revenue

-- Category-wise total revenue
SELECT p.category, SUM(oi.quantity * oi.price) as revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;

ℹ

Insight: Kaunsi category sabse zyada revenue de rahi hai✓ Electronics top pe hai toh inventory zyada rakho, Footwear slow hai toh discounts do.

3. Monthly sales trend

-- Monthly sales trend
SELECT DATE_FORMAT(order_date, '%Y-%m') as month,
 COUNT(*) as orders,
 SUM(total) as revenue
FROM orders
GROUP BY month
ORDER BY month;

ℹ

Insight: Kaunse month mein sales zyada hui✓ Seasonal trends samjho � festival season mein revenue badhta hai ya nahi. Business planning ke liye zaroori hai.

4. Customer city-wise orders

-- City-wise order count and revenue
SELECT c.city,
 COUNT(DISTINCT o.id) as total_orders,
 SUM(o.total) as total_revenue
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.city
ORDER BY total_revenue DESC;

5. Products never ordered

-- Products with zero orders
SELECT p.name, p.category, p.price
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.product_id IS NULL;

ℹ

Insight: Kaunse products bilkul nahi bik rahe✓ Marketing team inko promote kar sakti hai ya phir discount de sakti hai clearance sale mein.

6. Average order value per customer

-- Average order value per customer
SELECT c.name,
 COUNT(o.id) as total_orders,
 ROUND(AVG(o.total), 2) as avg_order_value
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
HAVING total_orders > 1
ORDER BY avg_order_value DESC;

Project 3: Advanced Analytics with Window Functions

Window functions se complex analytics karo � ranking, running totals, comparisons:

1. Customer ranking by spending

-- Customer ranking by total spending
SELECT c.name,
 SUM(o.total) as total_spent,
 RANK() OVER (ORDER BY SUM(o.total) DESC) as spending_rank
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

2. Running total of revenue

-- Running total of revenue by date
SELECT order_date,
 total,
 SUM(total) OVER (ORDER BY order_date) as running_total
FROM orders
ORDER BY order_date;

ℹ

Running total: Har order ke baad total revenue kitna ho gaya. Business ko pata chalta hai ki cumulative growth kaisi hai over time.

3. Month-over-month growth

-- Month-over-month revenue growth
WITH monthly_revenue AS (
 SELECT DATE_FORMAT(order_date, '%Y-%m') as month,
 SUM(total) as revenue
 FROM orders
 GROUP BY month
)
SELECT month,
 revenue,
 LAG(revenue) OVER (ORDER BY month) as prev_month,
 ROUND(((revenue - LAG(revenue) OVER (ORDER BY month)) /
 LAG(revenue) OVER (ORDER BY month)) * 100, 2) as growth_pct
FROM monthly_revenue;

4. Top product per category

-- Top selling product in each category
WITH category_sales AS (
 SELECT p.category, p.name,
 SUM(oi.quantity) as total_sold,
 ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity) DESC) as rn
 FROM products p
 JOIN order_items oi ON p.id = oi.product_id
 GROUP BY p.category, p.name
)
SELECT category, name, total_sold
FROM category_sales
WHERE rn = 1;

Project 4: Performance Optimization

Bade datasets pe fast queries likho � indexes, query optimization, execution plans:

1. Indexes lagao

-- Performance optimization: Indexes

-- Foreign key columns pe index
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

-- Frequently filtered columns pe index
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_products_category ON products(category);

-- Composite index for common queries
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Covering index for analytics
CREATE INDEX idx_order_items_cover ON order_items(product_id, quantity, price);

ℹ

Index strategy: Foreign keys pe index lagao � JOINs fast honge. Filtered columns pe index lagao � WHERE clause fast hoga. Covering index se table scan ki zaroorat nahi padegi.

2. Query optimization

-- Query optimization examples

-- BAD: SELECT * � unnecessary columns
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;

-- GOOD: Sirf needed columns select karo
SELECT c.name, o.total, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- BAD: Subquery instead of JOIN
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- GOOD: JOIN use karo
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- BAD: LIKE with leading wildcard
SELECT * FROM customers WHERE name LIKE '%aman%';

-- GOOD: Full text search ya prefix match
SELECT * FROM customers WHERE name LIKE 'aman%';

3. EXPLAIN se samjho

-- Execution plan dekho
EXPLAIN SELECT c.name, SUM(o.total) as total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC;

-- Detailed analysis
EXPLAIN ANALYZE SELECT p.category, SUM(oi.quantity * oi.price) as revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.category;

ℹ

EXPLAIN: Query ka execution plan dikhata hai � kaunsa index use ho raha hai, kitne rows scan ho rahe hain, kya full table scan ho raha hai. Agar "Using filesort" ya "Using temporary" dikhe toh optimization ki zaroorat hai.

4. Stored procedure for common analytics

-- Reusable analytics stored procedure
CREATE PROCEDURE GetCustomerAnalytics(IN customer_id INT)
BEGIN
 -- Customer basic info
 SELECT name, email, city FROM customers WHERE id = customer_id;

 -- Order history
 SELECT order_date, total FROM orders
 WHERE customer_id = customer_id
 ORDER BY order_date DESC;

 -- Spending summary
 SELECT COUNT(*) as total_orders,
 SUM(total) as total_spent,
 AVG(total) as avg_order_value
 FROM orders WHERE customer_id = customer_id;

 -- Top categories purchased
 SELECT p.category, SUM(oi.quantity) as items_bought
 FROM order_items oi
 JOIN products p ON oi.product_id = p.id
 JOIN orders o ON oi.order_id = o.id
 WHERE o.customer_id = customer_id
 GROUP BY p.category
 ORDER BY items_bought DESC;
END;

-- Use karo
CALL GetCustomerAnalytics(1);

Try it: E-Commerce Queries Practice

Neeche ke editor mein complete e-commerce database banao aur queries practice karo. Template already loaded hai � code run karo aur results dekho!

SQL playgroundComplete e-commerce database � design, data, analytics

query.sql

-- Complete E-Commerce Database Project

-- Step 1: Tables banao
CREATE TABLE customers (
 id INT PRIMARY KEY,
 name VARCHAR(100),
 email VARCHAR(100) UNIQUE,
 city VARCHAR(50)
);

CREATE TABLE products (
 id INT PRIMARY KEY,
 name VARCHAR(100),
 category VARCHAR(50),
 price DECIMAL(10,2)
);

CREATE TABLE orders (
 id INT PRIMARY KEY,
 customer_id INT,
 order_date DATE,
 total DECIMAL(10,2),
 FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
 order_id INT,
 product_id INT,
 quantity INT,
 price DECIMAL(10,2),
 PRIMARY KEY (order_id, product_id),
 FOREIGN KEY (order_id) REFERENCES orders(id),
 FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Step 2: Data daalo
INSERT INTO customers VALUES
(1, 'Aman Kumar', 'aman@email.com', 'Delhi'),
(2, 'Priya Singh', 'priya@email.com', 'Mumbai'),
(3, 'Rahul Verma', 'rahul@email.com', 'Bangalore'),
(4, 'Sneha Patel', 'sneha@email.com', 'Delhi'),
(5, 'Vikram Reddy', 'vikram@email.com', 'Hyderabad');

INSERT INTO products VALUES
(1, 'iPhone 15', 'Electronics', 79999.00),
(2, 'Samsung S24', 'Electronics', 69999.00),
(3, 'Nike Air Max', 'Footwear', 12999.00),
(4, 'Levi''s Jeans', 'Clothing', 3999.00),
(5, 'MacBook Pro', 'Electronics', 199999.00),
(6, 'Adidas Ultraboost', 'Footwear', 14999.00);

INSERT INTO orders VALUES
(1, 1, '2024-01-15', 79999.00),
(2, 2, '2024-01-16', 82998.00),
(3, 1, '2024-02-01', 16998.00),
(4, 3, '2024-02-05', 199999.00),
(5, 4, '2024-02-10', 16998.00),
(6, 2, '2024-03-01', 79999.00),
(7, 5, '2024-03-05', 14999.00),
(8, 1, '2024-03-10', 3999.00),
(9, 3, '2024-03-15', 12999.00),
(10, 4, '2024-03-20', 69999.00);

INSERT INTO order_items VALUES
(1, 1, 1, 79999.00),
(2, 2, 1, 69999.00),
(2, 3, 1, 12999.00),
(3, 3, 1, 12999.00),
(3, 4, 1, 3999.00),
(4, 5, 1, 199999.00),
(5, 2, 1, 69999.00),
(5, 3, 1, 12999.00),
(6, 1, 1, 79999.00),
(7, 6, 1, 14999.00),
(8, 4, 1, 3999.00),
(9, 3, 1, 12999.00),
(10, 2, 1, 69999.00);

-- Step 3: Analytics queries
-- Top customers
SELECT c.name, SUM(o.total) as total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC
LIMIT 5;

-- Category revenue
SELECT p.category, SUM(oi.quantity * oi.price) as revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;

-- Monthly trend
SELECT DATE_FORMAT(order_date, '%Y-%m') as month,
 COUNT(*) as orders,
 SUM(total) as revenue
FROM orders
GROUP BY month
ORDER BY month;

Run Query dabayein

Quick check

Ek analytics query likho jo top 3 cities by revenue dikhaaye. Har city ka total revenue aur order count hona chahiye.

customers aur orders ko JOIN karo customer_id se. GROUP BY c.city karo aur SUM(o.total) aur COUNT(o.id) calculate karo. ORDER BY total_revenue DESC aur LIMIT 3 lagao.

Common project mistakes

Normalization na karna: Sab data ek table mein daal dete ho � redundancy badhti hai, update anomalies aate hain. Proper normal form follow karo � 3NF tak jaao.
Foreign keys na lagana: Relationships define nahi karte � orphan records aa jaate hain hain. Hamesha FOREIGN KEY constraints lagao.
Indexes na lagana: Bade datasets pe queries slow hoti hain. WHERE, JOIN, ORDER BY columns pe indexes lagao � performance 10x improve hogi.
SELECT * use karna: Har column select karte ho jab sirf 2-3 chahiye. Sirf needed columns select karo � network traffic kam, memory kam, speed zyada.
Hardcoded values: Query mein values hardcode karte ho. Stored procedures ya parameters use karo � code reusable hoga.

SQL Complete hai!

Ab tum ready ho real-world SQL projects banane ke liye. E-commerce, analytics, optimization � sab aa gaya. Portfolio mein projects dikhao aur interviews mein confidence se baat karo!

PRACTICALPROJECTS.