Amazon SQL Server Database Project - 10 Tables, Data, Relationships &
Advanced JOIN Queries
This document is designed for beginners who want to learn SQL Server using a real-world
Amazon-style e-commerce database.
Contents:
1. 10 SQL Server Tables
2. Relationships between tables
3. CREATE TABLE scripts
4. INSERT statements with 10 rows each
5. 10 Advanced JOIN queries
6. Simple explanation for each query
1. SQL Server CREATE TABLE Scripts
Customers
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
email VARCHAR(100),
city VARCHAR(50),
join_date DATE
);
Categories
CREATE TABLE Categories (
category_id INT PRIMARY KEY,
category_name VARCHAR(100)
);
Brands
CREATE TABLE Brands (
brand_id INT PRIMARY KEY,
brand_name VARCHAR(100)
);
Products
CREATE TABLE Products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category_id INT,
brand_id INT,
price DECIMAL(10,2),
stock_qty INT,
FOREIGN KEY (category_id) REFERENCES Categories(category_id),
FOREIGN KEY (brand_id) REFERENCES Brands(brand_id)
);
Orders
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
order_status VARCHAR(50),
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
Order_Items
CREATE TABLE Order_Items (
order_item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
unit_price DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES Orders(order_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
Payments
CREATE TABLE Payments (
payment_id INT PRIMARY KEY,
order_id INT,
payment_method VARCHAR(50),
payment_status VARCHAR(50),
amount DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);
Shipments
CREATE TABLE Shipments (
shipment_id INT PRIMARY KEY,
order_id INT,
warehouse_id INT,
shipment_status VARCHAR(50)
);
Warehouses
CREATE TABLE Warehouses (
warehouse_id INT PRIMARY KEY,
warehouse_name VARCHAR(100),
city VARCHAR(50)
);
Reviews
CREATE TABLE Reviews (
review_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
rating INT,
review_text VARCHAR(200),
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
2. Relationships Between Tables
Customers.customer_id → Orders.customer_id
Orders.order_id → Order_Items.order_id
Products.product_id → Order_Items.product_id
Products.category_id → Categories.category_id
Products.brand_id → Brands.brand_id
Orders.order_id → Payments.order_id
Orders.order_id → Shipments.order_id
Shipments.warehouse_id → Warehouses.warehouse_id
Customers.customer_id → Reviews.customer_id
Products.product_id → Reviews.product_id
3. INSERT Statements (10 Rows Each)
-- Customers
INSERT INTO Customers VALUES
(1,'Rahul','rahul@[Link]','Hyderabad','2025-01-01'),
(2,'Sneha','sneha@[Link]','Bangalore','2025-01-02'),
(3,'Arjun','arjun@[Link]','Chennai','2025-01-03'),
(4,'Priya','priya@[Link]','Mumbai','2025-01-04'),
(5,'Kiran','kiran@[Link]','Delhi','2025-01-05'),
(6,'Meena','meena@[Link]','Pune','2025-01-06'),
(7,'Vikram','vikram@[Link]','Kolkata','2025-01-07'),
(8,'Divya','divya@[Link]','Vizag','2025-01-08'),
(9,'Ravi','ravi@[Link]','Hyderabad','2025-01-09'),
(10,'Anjali','anjali@[Link]','Bangalore','2025-01-10');
-- Categories
INSERT INTO Categories VALUES
(1,'Mobiles'),
(2,'Electronics'),
(3,'Fashion'),
(4,'Books'),
(5,'Home'),
(6,'Kitchen'),
(7,'Sports'),
(8,'Beauty'),
(9,'Toys'),
(10,'Groceries');
-- Brands
INSERT INTO Brands VALUES
(1,'Apple'),
(2,'Samsung'),
(3,'Nike'),
(4,'Sony'),
(5,'LG'),
(6,'Puma'),
(7,'Dell'),
(8,'HP'),
(9,'Boat'),
(10,'Adidas');
-- Products
INSERT INTO Products VALUES
(101,'iPhone 15',1,1,80000,20),
(102,'Samsung TV',2,2,50000,10),
(103,'Nike Shoes',3,3,6000,50),
(104,'Sony Headphones',2,4,7000,30),
(105,'LG Refrigerator',5,5,40000,15),
(106,'Puma TShirt',3,6,2000,60),
(107,'Dell Laptop',2,7,75000,12),
(108,'HP Printer',2,8,15000,18),
(109,'Boat Speaker',2,9,3000,45),
(110,'Adidas Track Pant',3,10,3500,35);
-- Orders
INSERT INTO Orders VALUES
(1001,1,'2025-05-01','Delivered'),
(1002,2,'2025-05-02','Shipped'),
(1003,3,'2025-05-03','Cancelled'),
(1004,4,'2025-05-04','Delivered'),
(1005,5,'2025-05-05','Processing'),
(1006,6,'2025-05-06','Delivered'),
(1007,7,'2025-05-07','Shipped'),
(1008,8,'2025-05-08','Delivered'),
(1009,9,'2025-05-09','Delivered'),
(1010,10,'2025-05-10','Processing');
-- Order_Items
INSERT INTO Order_Items VALUES
(1,1001,101,1,80000),
(2,1002,102,1,50000),
(3,1003,103,2,6000),
(4,1004,104,1,7000),
(5,1005,105,1,40000),
(6,1006,106,3,2000),
(7,1007,107,1,75000),
(8,1008,108,1,15000),
(9,1009,109,2,3000),
(10,1010,110,1,3500);
-- Payments
INSERT INTO Payments VALUES
(1,1001,'UPI','Success',80000),
(2,1002,'Card','Success',50000),
(3,1003,'UPI','Failed',12000),
(4,1004,'NetBanking','Success',7000),
(5,1005,'Card','Pending',40000),
(6,1006,'UPI','Success',6000),
(7,1007,'Card','Success',75000),
(8,1008,'UPI','Success',15000),
(9,1009,'Cash','Success',6000),
(10,1010,'UPI','Pending',3500);
-- Warehouses
INSERT INTO Warehouses VALUES
(1,'Hyderabad Warehouse','Hyderabad'),
(2,'Bangalore Warehouse','Bangalore'),
(3,'Mumbai Warehouse','Mumbai'),
(4,'Delhi Warehouse','Delhi'),
(5,'Chennai Warehouse','Chennai'),
(6,'Pune Warehouse','Pune'),
(7,'Vizag Warehouse','Vizag'),
(8,'Kolkata Warehouse','Kolkata'),
(9,'Ahmedabad Warehouse','Ahmedabad'),
(10,'Jaipur Warehouse','Jaipur');
-- Shipments
INSERT INTO Shipments VALUES
(1,1001,1,'Delivered'),
(2,1002,2,'In Transit'),
(3,1003,3,'Cancelled'),
(4,1004,4,'Delivered'),
(5,1005,5,'Packing'),
(6,1006,6,'Delivered'),
(7,1007,7,'In Transit'),
(8,1008,8,'Delivered'),
(9,1009,9,'Delivered'),
(10,1010,10,'Packing');
-- Reviews
INSERT INTO Reviews VALUES
(1,1,101,5,'Excellent phone'),
(2,2,102,4,'Good TV'),
(3,3,103,5,'Comfortable shoes'),
(4,4,104,4,'Nice sound'),
(5,5,105,5,'Very spacious'),
(6,6,106,3,'Average quality'),
(7,7,107,5,'Fast laptop'),
(8,8,108,4,'Useful printer'),
(9,9,109,5,'Amazing speaker'),
(10,10,110,4,'Comfortable wear');
4. Advanced JOIN Queries with Simple Explanation
1. Find total amount spent by each customer
SELECT c.customer_name,
SUM([Link] * oi.unit_price) AS total_spent
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN Order_Items oi ON o.order_id = oi.order_id
GROUP BY c.customer_name;
Simple Explanation: This query joins Customers → Orders → Order_Items tables. It
calculates how much each customer spent in Amazon.
2. Find products never ordered
SELECT p.product_name
FROM Products p
LEFT JOIN Order_Items oi
ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;
Simple Explanation: This query uses LEFT JOIN. It finds products that are present in
Products table but never purchased.
3. Find top selling products
SELECT p.product_name,
SUM([Link]) AS total_quantity
FROM Products p
JOIN Order_Items oi
ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY total_quantity DESC;
Simple Explanation: This query calculates which products sold the most quantity.
4. Find customer orders with payment status
SELECT c.customer_name,
o.order_id,
p.payment_status
FROM Customers c
JOIN Orders o
ON c.customer_id = o.customer_id
JOIN Payments p
ON o.order_id = p.order_id;
Simple Explanation: This query combines customer, order and payment information
together.
5. Find warehouse handling each shipment
SELECT o.order_id,
w.warehouse_name,
s.shipment_status
FROM Orders o
JOIN Shipments s
ON o.order_id = s.order_id
JOIN Warehouses w
ON s.warehouse_id = w.warehouse_id;
Simple Explanation: This query shows from which warehouse each order was shipped.
6. Find customers who gave 5-star reviews
SELECT c.customer_name,
p.product_name,
[Link]
FROM Customers c
JOIN Reviews r
ON c.customer_id = r.customer_id
JOIN Products p
ON r.product_id = p.product_id
WHERE [Link] = 5;
Simple Explanation: This query finds customers who gave excellent ratings.
7. Find category-wise revenue
SELECT cat.category_name,
SUM([Link] * oi.unit_price) AS revenue
FROM Categories cat
JOIN Products p
ON cat.category_id = p.category_id
JOIN Order_Items oi
ON p.product_id = oi.product_id
GROUP BY cat.category_name;
Simple Explanation: This query calculates revenue generated by each category.
8. Find customers who ordered multiple products
SELECT c.customer_name,
COUNT(DISTINCT oi.product_id) AS total_products
FROM Customers c
JOIN Orders o
ON c.customer_id = o.customer_id
JOIN Order_Items oi
ON o.order_id = oi.order_id
GROUP BY c.customer_name
HAVING COUNT(DISTINCT oi.product_id) > 1;
Simple Explanation: This query uses HAVING clause to find customers who purchased
multiple products.
9. Find latest order of every customer
SELECT c.customer_name,
MAX(o.order_date) AS latest_order
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_name;
Simple Explanation: This query uses MAX function to get the latest order date for each
customer.
10. Find products contributing more than average sales
SELECT p.product_name,
SUM([Link] * oi.unit_price) AS sales
FROM Products p
JOIN Order_Items oi
ON p.product_id = oi.product_id
GROUP BY p.product_name
HAVING SUM([Link] * oi.unit_price) >
(
SELECT AVG(total_sales)
FROM (
SELECT SUM(quantity * unit_price) AS total_sales
FROM Order_Items
GROUP BY product_id
) x
);
Simple Explanation: This is an advanced query using subquery + HAVING clause. It finds
products whose sales are greater than average product sales.
