Before solving challenges, you need data. Real, messy, realistic data. Not 3 rows that make every query trivial. This lesson gives you a complete database with 100+ rows — enough to make queries interesting.
Copy-paste these scripts into MySQL Workbench, pgAdmin, or any SQL client. Run them in order. You will have a working database in under 2 minutes.
CREATE DATABASE IF NOT EXISTS ecommerce_practice;
USE ecommerce_practice;-- Customers table
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
city VARCHAR(50),
signup_date DATE NOT NULL
);
-- Products table
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price > 0),
stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0)
);
-- Orders table
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status ENUM('pending', 'completed', 'cancelled', 'returned') DEFAULT 'pending',
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- Order Items table
CREATE TABLE order_items (
item_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- Payments table
CREATE TABLE payments (
payment_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
payment_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_method ENUM('credit_card', 'debit_card', 'upi', 'net_banking', 'cod') NOT NULL,
payment_status ENUM('success', 'failed', 'pending', 'refunded') DEFAULT 'pending',
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);
-- Reviews table
CREATE TABLE reviews (
review_id INT PRIMARY KEY AUTO_INCREMENT,
product_id INT NOT NULL,
customer_id INT NOT NULL,
rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
review_text TEXT,
review_date DATE NOT NULL,
FOREIGN KEY (product_id) REFERENCES products(product_id),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);INSERT INTO customers (first_name, last_name, email, city, signup_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@email.com', 'Mumbai', '2024-01-15'),
('Priya', 'Patel', 'priya.patel@email.com', 'Delhi', '2024-01-20'),
('Amit', 'Kumar', 'amit.kumar@email.com', 'Bangalore', '2024-02-01'),
('Sneha', 'Gupta', 'sneha.gupta@email.com', 'Pune', '2024-02-10'),
('Vikram', 'Singh', 'vikram.singh@email.com', 'Chennai', '2024-02-15'),
('Anjali', 'Desai', 'anjali.desai@email.com', 'Mumbai', '2024-03-01'),
('Rohan', 'Mehta', 'rohan.mehta@email.com', 'Delhi', '2024-03-10'),
('Kavita', 'Rao', 'kavita.rao@email.com', 'Hyderabad', '2024-03-15'),
('Deepak', 'Joshi', 'deepak.joshi@email.com', 'Pune', '2024-04-01'),
('Nisha', 'Agarwal', 'nisha.agarwal@email.com', 'Bangalore', '2024-04-10'),
('Suresh', 'Reddy', 'suresh.reddy@email.com', 'Hyderabad', '2024-04-20'),
('Meera', 'Nair', 'meera.nair@email.com', 'Chennai', '2024-05-01'),
('Arjun', 'Verma', 'arjun.verma@email.com', 'Mumbai', '2024-05-15'),
('Pooja', 'Iyer', 'pooja.iyer@email.com', 'Bangalore', '2024-06-01'),
('Karan', 'Malhotra', 'karan.malhotra@email.com', 'Delhi', '2024-06-10');INSERT INTO products (product_name, category, price, stock_quantity) VALUES
('iPhone 15 Pro', 'Electronics', 134900.00, 50),
('Samsung Galaxy S24', 'Electronics', 79999.00, 75),
('MacBook Air M3', 'Electronics', 114900.00, 30),
('Sony WH-1000XM5', 'Electronics', 29990.00, 100),
('Cotton Kurta Set', 'Clothing', 1499.00, 200),
('Levi''s 501 Jeans', 'Clothing', 3999.00, 150),
('Nike Air Max', 'Footwear', 12995.00, 80),
('Adidas Ultraboost', 'Footwear', 16999.00, 60),
('The Alchemist', 'Books', 350.00, 500),
('Atomic Habits', 'Books', 550.00, 400),
('Prestige Mixer Grinder', 'Home & Kitchen', 3499.00, 120),
('Instant Pot', 'Home & Kitchen', 8999.00, 45),
('Yoga Mat Premium', 'Sports', 1299.00, 200),
('Resistance Bands Set', 'Sports', 799.00, 300),
('Boat Earbuds 441', 'Electronics', 1499.00, 250),
('Campus Running Shoes', 'Footwear', 1899.00, 180),
('Python Crash Course', 'Books', 699.00, 150),
('Steel Water Bottle', 'Home & Kitchen', 599.00, 400),
('Badminton Racket Pro', 'Sports', 2499.00, 90),
('Wireless Mouse', 'Electronics', 899.00, 300);INSERT INTO orders (customer_id, order_date, total_amount, status) VALUES
(1, '2024-02-01', 134900.00, 'completed'),
(1, '2024-03-15', 4498.00, 'completed'),
(2, '2024-02-10', 79999.00, 'completed'),
(2, '2024-04-20', 1499.00, 'cancelled'),
(3, '2024-02-15', 114900.00, 'completed'),
(3, '2024-05-01', 29990.00, 'completed'),
(4, '2024-03-01', 3999.00, 'completed'),
(4, '2024-05-10', 12995.00, 'returned'),
(5, '2024-03-05', 350.00, 'completed'),
(5, '2024-06-01', 1299.00, 'completed'),
(6, '2024-03-20', 29990.00, 'completed'),
(6, '2024-04-15', 8999.00, 'completed'),
(7, '2024-04-01', 16999.00, 'completed'),
(7, '2024-05-20', 550.00, 'completed'),
(8, '2024-04-10', 3499.00, 'pending'),
(8, '2024-06-05', 1499.00, 'completed'),
(9, '2024-04-25', 79999.00, 'completed'),
(9, '2024-06-10', 899.00, 'completed'),
(10, '2024-05-05', 134900.00, 'completed'),
(10, '2024-06-15', 2499.00, 'completed'),
(11, '2024-05-10', 1899.00, 'completed'),
(11, '2024-06-20', 599.00, 'completed'),
(12, '2024-05-15', 699.00, 'completed'),
(13, '2024-05-25', 12995.00, 'cancelled'),
(13, '2024-06-25', 29990.00, 'completed'),
(14, '2024-06-01', 1499.00, 'completed'),
(14, '2024-06-28', 3999.00, 'pending'),
(15, '2024-06-10', 550.00, 'completed'),
(1, '2024-06-15', 8999.00, 'completed'),
(3, '2024-06-20', 799.00, 'completed');INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 134900.00),
(2, 5, 2, 1499.00),
(2, 9, 1, 350.00),
(3, 2, 1, 79999.00),
(4, 5, 1, 1499.00),
(5, 3, 1, 114900.00),
(6, 4, 1, 29990.00),
(7, 6, 1, 3999.00),
(8, 7, 1, 12995.00),
(9, 9, 1, 350.00),
(10, 13, 1, 1299.00),
(11, 4, 1, 29990.00),
(12, 12, 1, 8999.00),
(13, 8, 1, 16999.00),
(14, 10, 1, 550.00),
(15, 11, 1, 3499.00),
(16, 15, 1, 1499.00),
(17, 2, 1, 79999.00),
(18, 20, 1, 899.00),
(19, 1, 1, 134900.00),
(20, 19, 1, 2499.00),
(21, 16, 1, 1899.00),
(22, 18, 1, 599.00),
(23, 17, 1, 699.00),
(24, 7, 1, 12995.00),
(25, 4, 1, 29990.00),
(26, 15, 1, 1499.00),
(27, 6, 1, 3999.00),
(28, 10, 1, 550.00),
(29, 12, 1, 8999.00),
(30, 14, 1, 799.00);INSERT INTO payments (order_id, payment_date, amount, payment_method, payment_status) VALUES
(1, '2024-02-01', 134900.00, 'credit_card', 'success'),
(2, '2024-03-15', 4498.00, 'upi', 'success'),
(3, '2024-02-10', 79999.00, 'debit_card', 'success'),
(4, '2024-04-20', 1499.00, 'upi', 'refunded'),
(5, '2024-02-15', 114900.00, 'credit_card', 'success'),
(6, '2024-05-01', 29990.00, 'net_banking', 'success'),
(7, '2024-03-01', 3999.00, 'upi', 'success'),
(8, '2024-05-10', 12995.00, 'cod', 'refunded'),
(9, '2024-03-05', 350.00, 'upi', 'success'),
(10, '2024-06-01', 1299.00, 'upi', 'success'),
(11, '2024-03-20', 29990.00, 'credit_card', 'success'),
(12, '2024-04-15', 8999.00, 'debit_card', 'success'),
(13, '2024-04-01', 16999.00, 'credit_card', 'success'),
(14, '2024-05-20', 550.00, 'upi', 'success'),
(15, '2024-04-10', 3499.00, 'net_banking', 'pending'),
(16, '2024-06-05', 1499.00, 'upi', 'success'),
(17, '2024-04-25', 79999.00, 'credit_card', 'success'),
(18, '2024-06-10', 899.00, 'upi', 'success'),
(19, '2024-05-05', 134900.00, 'net_banking', 'success'),
(20, '2024-06-15', 2499.00, 'debit_card', 'success'),
(21, '2024-05-10', 1899.00, 'upi', 'success'),
(22, '2024-06-20', 599.00, 'upi', 'success'),
(23, '2024-05-15', 699.00, 'cod', 'success'),
(24, '2024-05-25', 12995.00, 'credit_card', 'failed'),
(25, '2024-06-25', 29990.00, 'credit_card', 'success'),
(26, '2024-06-01', 1499.00, 'upi', 'success'),
(28, '2024-06-10', 550.00, 'upi', 'success'),
(29, '2024-06-15', 8999.00, 'debit_card', 'success'),
(30, '2024-06-20', 799.00, 'upi', 'success');INSERT INTO reviews (product_id, customer_id, rating, review_text, review_date) VALUES
(1, 1, 5, 'Best phone I have ever used. Camera is incredible.', '2024-02-15'),
(2, 3, 4, 'Great phone but battery could be better.', '2024-03-01'),
(3, 5, 5, 'MacBook Air is a beast. Silent and fast.', '2024-03-10'),
(4, 6, 5, 'Noise cancellation is top-notch.', '2024-04-01'),
(4, 11, 4, 'Good headphones but a bit tight on the ears.', '2024-04-15'),
(9, 5, 3, 'Decent read but overhyped.', '2024-03-20'),
(10, 7, 5, 'Changed my life. Must read for everyone.', '2024-06-01'),
(12, 6, 4, 'Makes cooking so easy. Worth the price.', '2024-05-01'),
(7, 8, 4, 'Comfortable and stylish. Good for daily use.', '2024-06-10'),
(15, 8, 3, 'Average sound quality for the price.', '2024-06-15'),
(1, 10, 5, 'Premium build quality. Worth every rupee.', '2024-05-20'),
(2, 9, 4, 'Smooth performance. Samsung nailed it.', '2024-05-10'),
(6, 7, 4, 'Classic jeans. Fit is perfect.', '2024-04-10'),
(6, 14, 3, 'Good quality but expensive for jeans.', '2024-06-30'),
(13, 10, 4, 'Thick and grippy. Good for home workouts.', '2024-06-20'),
(16, 11, 4, 'Decent shoes for the price range.', '2024-05-25'),
(18, 11, 5, 'Keeps water cold for 24 hours. Love it.', '2024-06-25'),
(8, 13, 5, 'Best running shoes I have owned.', '2024-06-28'),
(5, 4, 4, 'Beautiful fabric. Got compliments at the party.', '2024-03-15'),
(11, 8, 3, 'Does the job but noisy at high speed.', '2024-04-20');Notice that order_id 27 (Pooja, pending) has NO payment record. And order_id 15 has payment_status = pending. These are intentional data gaps — you will use them in the data validation challenges later.
Save all these scripts in a single .sql file. Every time you mess up the data while practicing, just drop the database and re-run. Takes 10 seconds.
SELECT COUNT(*) FROM customers; -- Should return 15
SELECT COUNT(*) FROM products; -- Should return 20
SELECT COUNT(*) FROM orders; -- Should return 30
SELECT COUNT(*) FROM order_items; -- Should return 30
SELECT COUNT(*) FROM payments; -- Should return 29 (one missing intentionally)
SELECT COUNT(*) FROM reviews; -- Should return 20
Key Point: A complete e-commerce database with 6 tables and 100+ rows of realistic Indian data — ready for all challenges in this chapter.