-- ============================================================================
-- SMARTSHOP DATABASE SYSTEM
-- Online Shopping Platform Database Design
-- ============================================================================
-- ============================================================================
-- SECTION 1: CREATE DATABASE AND TABLES
-- ============================================================================
-- Create Database
CREATE DATABASE IF NOT EXISTS SmartShop;
USE SmartShop;
-- ============================================================================
-- TABLE 1: CUSTOMERS
-- ============================================================================
-- Stores customer information
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
CustomerName VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Phone VARCHAR(15),
Address VARCHAR(255),
City VARCHAR(50),
State VARCHAR(50),
ZipCode VARCHAR(10),
RegistrationDate DATE,
Country VARCHAR(50)
);
-- ============================================================================
-- TABLE 2: PRODUCTS
-- ============================================================================
-- Stores product information
CREATE TABLE Products (
ProductID INT PRIMARY KEY AUTO_INCREMENT,
ProductName VARCHAR(150) NOT NULL,
Category VARCHAR(50),
Description TEXT,
Price DECIMAL(10, 2) NOT NULL,
StockQuantity INT DEFAULT 0,
ManufacturerID INT,
CreatedDate DATE,
LastUpdatedDate DATE
);
-- ============================================================================
-- TABLE 3: ORDERS
-- ============================================================================
-- Stores order information placed by customers
CREATE TABLE Orders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT NOT NULL,
OrderDate DATE NOT NULL,
ShippingAddress VARCHAR(255),
OrderStatus VARCHAR(50),
TotalAmount DECIMAL(10, 2),
ShippingCost DECIMAL(10, 2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);
-- ============================================================================
-- TABLE 4: ORDER_ITEMS (Junction Table)
-- ============================================================================
-- Stores the relationship between orders and products (many-to-many)
-- This table tracks which products are in each order and their quantities
CREATE TABLE Order_Items (
OrderItemID INT PRIMARY KEY AUTO_INCREMENT,
OrderID INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL DEFAULT 1,
UnitPrice DECIMAL(10, 2) NOT NULL,
LineTotal DECIMAL(10, 2),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE,
FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE
);
-- ============================================================================
-- SECTION 2: INSERT SAMPLE DATA INTO TABLES
-- ============================================================================
-- ============================================================================
-- INSERT CUSTOMERS (At least 5 records)
-- ============================================================================
INSERT INTO Customers (CustomerName, Email, Phone, Address, City, State, ZipCode, RegistrationDate, Country)
VALUES
('John Smith', '[Link]@[Link]', '555-0101', '123 Main St', 'New York', 'NY', '10001', '2024-01-15', 'USA'),
('Sarah Johnson', '[Link]@[Link]', '555-0102', '456 Oak Ave', 'Los Angeles', 'CA', '90001', '2024-02-
20', 'USA'),
('Michael Brown', '[Link]@[Link]', '555-0103', '789 Pine Rd', 'Chicago', 'IL', '60601', '2024-03-10',
'USA'),
('Emily Davis', '[Link]@[Link]', '555-0104', '321 Elm St', 'Houston', 'TX', '77001', '2024-04-05', 'USA'),
('Robert Wilson', '[Link]@[Link]', '555-0105', '654 Maple Dr', 'Phoenix', 'AZ', '85001', '2024-05-12',
'USA'),
('Jennifer Martinez', '[Link]@[Link]', '555-0106', '987 Cedar Ln', 'Philadelphia', 'PA', '19101',
'2024-06-08', 'USA'),
('David Anderson', '[Link]@[Link]', '555-0107', '159 Birch Blvd', 'San Antonio', 'TX', '78201', '2024-
07-22', 'USA');
-- ============================================================================
-- INSERT PRODUCTS (At least 5 records)
-- ============================================================================
INSERT INTO Products (ProductName, Category, Description, Price, StockQuantity, ManufacturerID, CreatedDate,
LastUpdatedDate) VALUES
('Wireless Mouse', 'Electronics', 'Bluetooth wireless mouse with ergonomic design', 29.99, 150, 1, '2024-01-01',
'2024-08-01'),
('USB-C Cable', 'Accessories', 'High-speed USB-C charging and data cable', 12.99, 200, 2, '2024-01-05', '2024-08-
02'),
('Mechanical Keyboard', 'Electronics', 'RGB mechanical keyboard with Cherry MX switches', 89.99, 75, 1, '2024-01-
10', '2024-08-03'),
('Laptop Stand', 'Office', 'Adjustable aluminum laptop stand for better posture', 39.99, 100, 3, '2024-01-15',
'2024-08-04'),
('Wireless Headphones', 'Audio', 'Noise-cancelling wireless headphones with 30-hour battery', 149.99, 50, 4,
'2024-01-20', '2024-08-05'),
('Screen Protector', 'Accessories', 'Tempered glass screen protector for phones', 9.99, 300, 2, '2024-02-01',
'2024-08-06'),
('Phone Case', 'Accessories', 'Durable silicone phone case with drop protection', 14.99, 250, 5, '2024-02-05',
'2024-08-07'),
('USB Hub', 'Electronics', '7-port USB 3.0 hub with fast charging', 34.99, 80, 3, '2024-02-10', '2024-08-08'),
('Webcam HD', 'Electronics', '1080p HD webcam with built-in microphone', 59.99, 60, 1, '2024-02-15', '2024-08-
09'),
('Phone Mount', 'Accessories', 'Adjustable universal phone mount for car dashboard', 19.99, 120, 6, '2024-02-20',
'2024-08-10');
-- ============================================================================
-- INSERT ORDERS (Orders placed by customers)
-- ============================================================================
INSERT INTO Orders (CustomerID, OrderDate, ShippingAddress, OrderStatus, TotalAmount, ShippingCost) VALUES
(1, '2024-06-01', '123 Main St, New York, NY 10001', 'Delivered', 159.96, 9.99),
(1, '2024-07-15', '123 Main St, New York, NY 10001', 'Shipped', 89.99, 5.00),
(2, '2024-06-10', '456 Oak Ave, Los Angeles, CA 90001', 'Delivered', 204.96, 10.00),
(3, '2024-06-20', '789 Pine Rd, Chicago, IL 60601', 'Delivered', 49.97, 7.50),
(4, '2024-07-05', '321 Elm St, Houston, TX 77001', 'Processing', 179.97, 8.00),
(5, '2024-07-25', '654 Maple Dr, Phoenix, AZ 85001', 'Shipped', 299.97, 12.00),
(2, '2024-08-01', '456 Oak Ave, Los Angeles, CA 90001', 'Processing', 124.97, 6.00);
-- ============================================================================
-- INSERT ORDER ITEMS (Products in each order with quantities)
-- ============================================================================
INSERT INTO Order_Items (OrderID, ProductID, Quantity, UnitPrice, LineTotal) VALUES
-- Order 1 (Customer 1): Wireless Mouse + USB-C Cable
(1, 1, 2, 29.99, 59.98),
(1, 2, 1, 12.99, 12.99),
(1, 3, 1, 89.99, 89.99),
-- Order 2 (Customer 1): Mechanical Keyboard
(2, 3, 1, 89.99, 89.99),
-- Order 3 (Customer 2): Wireless Headphones + USB Hub
(3, 5, 1, 149.99, 149.99),
(3, 8, 1, 34.99, 34.99),
-- Order 4 (Customer 3): Phone Case + Screen Protector
(4, 7, 1, 14.99, 14.99),
(4, 6, 1, 9.99, 9.99),
-- Order 5 (Customer 4): Laptop Stand + Phone Mount
(5, 4, 1, 39.99, 39.99),
(5, 10, 1, 19.99, 19.99),
(5, 1, 2, 29.99, 59.98),
-- Order 6 (Customer 5): Webcam HD + Wireless Headphones + USB-C Cable
(6, 9, 1, 59.99, 59.99),
(6, 5, 1, 149.99, 149.99),
(6, 2, 1, 12.99, 12.99),
-- Order 7 (Customer 2): Laptop Stand + Phone Case
(7, 4, 1, 39.99, 39.99),
(7, 7, 1, 14.99, 14.99);
-- ============================================================================
-- SECTION 3: BASIC SELECT QUERIES
-- ============================================================================
-- Query 1: Display all customers
SELECT * FROM Customers;
-- Query 2: Display all products
SELECT * FROM Products;
-- Query 3: Display all orders
SELECT * FROM Orders;
-- Query 4: Display all order items
SELECT * FROM Order_Items;
-- ============================================================================
-- SECTION 4: JOIN QUERIES
-- ============================================================================
-- ============================================================================
-- 4.1 INNER JOIN QUERIES
-- ============================================================================
-- Query 5: Show customers who have placed orders with their order details
-- (Only customers who have orders)
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Customers c
INNER JOIN Orders o ON [Link] = [Link]
ORDER BY [Link], [Link] DESC;
-- Query 6: Show all orders with customer and product details
-- (Complete order information with items)
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Orders o
INNER JOIN Customers c ON [Link] = [Link]
INNER JOIN Order_Items oi ON [Link] = [Link]
INNER JOIN Products p ON [Link] = [Link]
ORDER BY [Link], [Link];
-- Query 7: Show products that have been ordered at least once
-- (Only products with sales)
SELECT DISTINCT
[Link],
[Link],
[Link],
[Link],
COUNT([Link]) AS TimesOrdered,
SUM([Link]) AS TotalQuantitySold
FROM Products p
INNER JOIN Order_Items oi ON [Link] = [Link]
GROUP BY [Link], [Link], [Link], [Link]
ORDER BY TimesOrdered DESC;
-- Query 8: Show customer purchase history with product details
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Customers c
INNER JOIN Orders o ON [Link] = [Link]
INNER JOIN Order_Items oi ON [Link] = [Link]
INNER JOIN Products p ON [Link] = [Link]
WHERE [Link] = 1
ORDER BY [Link] DESC;
-- ============================================================================
-- 4.2 LEFT JOIN QUERIES
-- ============================================================================
-- Query 9: Show ALL customers even if they haven't placed any orders
-- (LEFT JOIN ensures all customers are displayed)
SELECT
[Link],
[Link],
[Link],
[Link],
COUNT([Link]) AS TotalOrders,
SUM([Link]) AS TotalSpent
FROM Customers c
LEFT JOIN Orders o ON [Link] = [Link]
GROUP BY [Link], [Link], [Link], [Link]
ORDER BY TotalOrders DESC, [Link];
-- Query 10: Show customer details with their recent orders (or NULL if no orders)
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Customers c
LEFT JOIN Orders o ON [Link] = [Link]
ORDER BY [Link], [Link] DESC;
-- Query 11: List all customers and check if they have made any purchases
SELECT
[Link],
[Link],
[Link],
CASE
WHEN [Link] IS NOT NULL THEN 'Has Purchased'
ELSE 'No Purchase Yet'
END AS PurchaseStatus,
COUNT([Link]) AS NumberOfOrders
FROM Customers c
LEFT JOIN Orders o ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
ORDER BY [Link];
-- ============================================================================
-- 4.3 RIGHT JOIN QUERIES
-- ============================================================================
-- Query 12: Show ALL products including those never ordered
-- (RIGHT JOIN perspective: all products from right table)
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
COUNT([Link]) AS TimesPurchased
FROM Order_Items oi
RIGHT JOIN Products p ON [Link] = [Link]
GROUP BY [Link], [Link], [Link], [Link], [Link], [Link]
ORDER BY TimesPurchased DESC, [Link];
-- Query 13: Show products with order information (or NULL for unordered products)
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Products p
LEFT JOIN Order_Items oi ON [Link] = [Link]
LEFT JOIN Orders o ON [Link] = [Link]
LEFT JOIN Customers c ON [Link] = [Link]
ORDER BY [Link], [Link] DESC;
-- ============================================================================
-- 4.4 FULL OUTER JOIN QUERIES (Simulated using UNION)
-- ============================================================================
-- Query 14: Show all customers and all products with their relationships
-- (FULL OUTER JOIN simulated with UNION - shows both ordered and unordered items)
SELECT
[Link],
[Link],
[Link],
[Link]
FROM Customers c
LEFT JOIN Orders o ON [Link] = [Link]
LEFT JOIN Order_Items oi ON [Link] = [Link]
LEFT JOIN Products p ON [Link] = [Link]
UNION
SELECT
[Link],
[Link],
[Link],
[Link]
FROM Products p
LEFT JOIN Order_Items oi ON [Link] = [Link]
LEFT JOIN Orders o ON [Link] = [Link]
LEFT JOIN Customers c ON [Link] = [Link]
ORDER BY CustomerName;
-- ============================================================================
-- SECTION 5: COMPLEX QUERIES WITH MULTIPLE JOINS
-- ============================================================================
-- Query 15: Complete order summary with customer, products, and totals
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
COUNT(DISTINCT [Link]) AS ProductCount,
SUM([Link]) AS TotalItems,
SUM([Link]) AS SubTotal,
[Link],
[Link]
FROM Orders o
INNER JOIN Customers c ON [Link] = [Link]
INNER JOIN Order_Items oi ON [Link] = [Link]
GROUP BY [Link], [Link], [Link], [Link], [Link], [Link], [Link], [Link]
ORDER BY [Link] DESC;
-- Query 16: Top customers by spending with average order value
SELECT
[Link],
[Link],
[Link],
COUNT([Link]) AS TotalOrders,
SUM([Link]) AS TotalSpent,
AVG([Link]) AS AvgOrderValue,
MAX([Link]) AS LastOrderDate
FROM Customers c
INNER JOIN Orders o ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
ORDER BY TotalSpent DESC;
-- Query 17: Product sales performance analysis
SELECT
[Link],
[Link],
[Link],
[Link],
COUNT(DISTINCT [Link]) AS OrderCount,
SUM([Link]) AS TotalQuantitySold,
SUM([Link]) AS TotalRevenue,
AVG([Link]) AS AvgSellingPrice
FROM Products p
LEFT JOIN Order_Items oi ON [Link] = [Link]
LEFT JOIN Orders o ON [Link] = [Link]
GROUP BY [Link], [Link], [Link], [Link]
ORDER BY TotalRevenue DESC;
-- Query 18: Orders with line item details sorted by order date
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
(SELECT SUM(LineTotal) FROM Order_Items WHERE OrderID = [Link]) AS OrderTotal
FROM Orders o
INNER JOIN Customers c ON [Link] = [Link]
INNER JOIN Order_Items oi ON [Link] = [Link]
INNER JOIN Products p ON [Link] = [Link]
ORDER BY [Link] DESC, [Link], [Link];
-- ============================================================================
-- SECTION 6: ADVANCED FILTERING QUERIES
-- ============================================================================
-- Query 19: Find customers who haven't placed any orders
SELECT
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Customers c
LEFT JOIN Orders o ON [Link] = [Link]
WHERE [Link] IS NULL
ORDER BY [Link] DESC;
-- Query 20: Find products that have never been ordered
SELECT
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Products p
LEFT JOIN Order_Items oi ON [Link] = [Link]
WHERE [Link] IS NULL
ORDER BY [Link];
-- Query 21: Find customers who ordered in the last 30 days
SELECT DISTINCT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Customers c
INNER JOIN Orders o ON [Link] = [Link]
WHERE [Link] >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY [Link] DESC;
-- Query 22: Find products ordered by a specific customer (John Smith)
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Customers c
INNER JOIN Orders o ON [Link] = [Link]
INNER JOIN Order_Items oi ON [Link] = [Link]
INNER JOIN Products p ON [Link] = [Link]
WHERE [Link] = 'John Smith'
ORDER BY [Link] DESC;
-- ============================================================================
-- SECTION 7: ANALYTICAL QUERIES
-- ============================================================================
-- Query 23: Calculate revenue by product category
SELECT
[Link],
COUNT(DISTINCT [Link]) AS NumberOfOrders,
SUM([Link]) AS TotalItemsSold,
SUM([Link]) AS TotalCategoryRevenue,
AVG([Link]) AS AvgProductPrice
FROM Products p
LEFT JOIN Order_Items oi ON [Link] = [Link]
LEFT JOIN Orders o ON [Link] = [Link]
GROUP BY [Link]
ORDER BY TotalCategoryRevenue DESC;
-- Query 24: Monthly sales report
SELECT
DATE_FORMAT([Link], '%Y-%m') AS Month,
COUNT([Link]) AS TotalOrders,
COUNT(DISTINCT [Link]) AS UniqueCustomers,
SUM([Link]) AS MonthlyRevenue,
AVG([Link]) AS AvgOrderValue
FROM Orders o
INNER JOIN Customers c ON [Link] = [Link]
GROUP BY DATE_FORMAT([Link], '%Y-%m')
ORDER BY Month DESC;
-- Query 25: Customer lifetime value analysis
SELECT
[Link],
[Link],
[Link],
COUNT([Link]) AS LifetimeOrders,
SUM([Link]) AS LifetimeValue,
MAX([Link]) AS LastPurchaseDate,
DATEDIFF(CURDATE(), MAX([Link])) AS DaysSinceLastPurchase,
CASE
WHEN COUNT([Link]) >= 3 THEN 'High Value'
WHEN COUNT([Link]) = 2 THEN 'Medium Value'
ELSE 'Low Value'
END AS CustomerSegment
FROM Customers c
LEFT JOIN Orders o ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
ORDER BY LifetimeValue DESC;
-- Query 26: Most popular products by quantity sold
SELECT
[Link],
[Link],
[Link],
COUNT([Link]) AS TimesPurchased,
SUM([Link]) AS TotalQuantitySold,
SUM([Link]) AS TotalRevenue,
ROUND(AVG([Link]), 2) AS AvgQuantityPerOrder
FROM Products p
LEFT JOIN Order_Items oi ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
ORDER BY TotalQuantitySold DESC;
-- ============================================================================
-- SECTION 8: SELF JOIN QUERIES (If needed for comparison)
-- ============================================================================
-- Query 27: Compare products by price range
SELECT
[Link] AS Product1,
[Link] AS Price1,
[Link] AS Product2,
[Link] AS Price2,
ROUND(ABS([Link] - [Link]), 2) AS PriceDifference
FROM Products p1
INNER JOIN Products p2 ON [Link] < [Link]
WHERE [Link] = [Link]
ORDER BY [Link], PriceDifference DESC;
-- ============================================================================
-- SECTION 9: VIEWS (Optional but useful)
-- ============================================================================
-- Create a view for customer purchase summary
CREATE VIEW CustomerPurchaseSummary AS
SELECT
[Link],
[Link],
[Link],
COUNT([Link]) AS TotalOrders,
SUM([Link]) AS TotalSpending,
MAX([Link]) AS LastOrderDate,
AVG([Link]) AS AvgOrderValue
FROM Customers c
LEFT JOIN Orders o ON [Link] = [Link]
GROUP BY [Link], [Link], [Link];
-- Query from the view
-- SELECT * FROM CustomerPurchaseSummary ORDER BY TotalSpending DESC;
-- Create a view for product sales performance
CREATE VIEW ProductSalesPerformance AS
SELECT
[Link],
[Link],
[Link],
[Link],
COUNT(DISTINCT [Link]) AS TimesSold,
SUM([Link]) AS TotalQuantitySold,
SUM([Link]) AS TotalRevenue
FROM Products p
LEFT JOIN Order_Items oi ON [Link] = [Link]
GROUP BY [Link], [Link], [Link], [Link];
-- Query from the view
-- SELECT * FROM ProductSalesPerformance ORDER BY TotalRevenue DESC;
-- ============================================================================
-- SECTION 10: DATA INTEGRITY & INDEX QUERIES
-- ============================================================================
-- Query 28: Check for orphaned orders (orders with no customer - shouldn't exist)
SELECT
[Link],
[Link],
[Link]
FROM Orders o
WHERE NOT EXISTS (
SELECT 1 FROM Customers c WHERE [Link] = [Link]
);
-- Query 29: Check for orphaned order items (items with no order - shouldn't exist)
SELECT
[Link],
[Link],
[Link]
FROM Order_Items oi
WHERE NOT EXISTS (
SELECT 1 FROM Orders o WHERE [Link] = [Link]
) OR NOT EXISTS (
SELECT 1 FROM Products p WHERE [Link] = [Link]
);
-- Query 30: Verify data consistency - Total amount should match sum of line items
SELECT
[Link],
[Link],
SUM([Link]) AS CalculatedTotal,
[Link],
ROUND(SUM([Link]) + [Link], 2) AS ExpectedTotal,
CASE
WHEN [Link] = ROUND(SUM([Link]) + [Link], 2) THEN 'OK'
ELSE 'MISMATCH'
END AS Status
FROM Orders o
LEFT JOIN Order_Items oi ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
HAVING Status = 'MISMATCH';
-- ============================================================================
-- END OF SQL QUERIES
-- ============================================================================