CREATE TABLE Categories (
CategoryID INT PRIMARY KEY AUTO_INCREMENT,
CategoryName VARCHAR(255) NOT NULL,
Description TEXT
);
CREATE TABLE Products (
ProductID INT PRIMARY KEY AUTO_INCREMENT,
ProductName VARCHAR(255) NOT NULL,
Description TEXT,
CategoryID INT NOT NULL,
Price DECIMAL(10,2) NOT NULL,
StockQuantity INT NOT NULL,
DateAdded DATE NOT NULL,
FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
FullName VARCHAR(255) NOT NULL,
Email VARCHAR(255) NOT NULL,
Phone VARCHAR(15),
Address TEXT
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL,
TotalAmount DECIMAL(10,2) NOT NULL,
OrderStatus VARCHAR(50) NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
CREATE TABLE OrderDetails (
OrderDetailID INT PRIMARY KEY AUTO_INCREMENT,
OrderID INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
UnitPrice DECIMAL(10,2) NOT NULL,
SubTotal DECIMAL(10,2) NOT NULL,
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
INSERT Categories (CategoryID,CategoryName,Description) VALUES
(1,'Electronics','Devices and gadgets'),
(2,'Clothing','Apparel and accessories'),
(3,'Books','Printed and digital books'),
(4,'Home Appliances','Household electrical appliances'),
(5,'Toys','Toys and games for children');
INSERT Products (ProductID,ProductName,Description,CategoryID,Price,StockQuantity,DateAdded) VALUES
(1,'Smartphone','Latest model smartphone',1,699.99,50,'2025-01-01'),
(2,'Laptop','High-performance laptop',1,999.99,30,'2025-01-02'),
(3,'Headphones','Noise-cancelling headphones',1,199.99,100,'2025-01-03'),
(4,'T-shirt','Cotton t-shirt',2,19.99,200,'2025-01-01'),
(5,'Jeans','Denim jeans',2,49.99,150,'2025-01-01'),
(6,'Fiction Book','Bestselling fiction book',3,14.99,120,'2025-01-01'),
(7,'Cookbook','Recipes from around the world',3,24.99,80,'2025-01-01'),
(8,'Microwave','700W microwave oven',4,89.99,40,'2025-01-01'),
(9,'Blender','Multi-speed blender',4,49.99,60,'2025-01-01'),
(10,'Action Figure','Superhero action figure',5,14.99,300,'2025-01-01'),
(11,'Board Game','Family-friendly board game',5,29.99,100,'2025-01-01'),
(12,'Smartwatch','Wearable fitness tracker',1,299.99,70,'2025-01-01'),
(13,'Jacket','Water-resistant jacket',2,99.99,80,'2025-01-01'),
(14,'Notebook','Hardcover notebook',3,9.99,200,'2025-01-01'),
(15,'Vacuum Cleaner','Powerful vacuum cleaner',4,129.99,50,'2025-01-01'),
(16,'Doll','Fashion doll with accessories',5,19.99,150,'2025-01-01'),
(17,'Tablet','10-inch screen tablet',1,399.99,40,'2025-01-01'),
(18,'Sneakers','Comfortable running shoes',2,79.99,120,'2025-01-01'),
(19,'E-reader','E-ink display e-reader',3,129.99,60,'2025-01-01'),
(20,'Toaster','4-slice toaster',4,29.99,70,'2025-01-01');
INSERT Customers (CustomerID,FullName,Email,Phone,Address) VALUES
(1,'John Doe','john.doe@example.com','1234567890','123 Elm Street'),
(2,'Jane Smith','jane.smith@example.com','0987654321','456 Oak Avenue'),
(3,'Alice Johnson','alice.johnson@example.com','1122334455','789 Maple Lane'),
(4,'Bob Brown','bob.brown@example.com','6677889900','101 Pine Road'),
(5,'Charlie Davis','charlie.davis@example.com','4455667788','202 Birch Boulevard'),
(6,'Diana Evans','diana.evans@example.com','2233445566','303 Cedar Court'),
(7,'Evan Garcia','evan.garcia@example.com','9988776655','404 Spruce Circle'),
(8,'Fiona Harris','fiona.harris@example.com','8877665544','505 Willow Way'),
(9,'George Walker','george.walker@example.com','7766554433','606 Ash Drive'),
(10,'Hannah Young','hannah.young@example.com','6655443322','707 Fir Lane');
INSERT Orders (OrderID,CustomerID,OrderDate,TotalAmount,OrderStatus) VALUES
(1,1,'2025-01-01 10:00:00',199.99,'Shipped'),
(2,2,'2025-01-02 11:00:00',299.99,'Pending'),
(3,3,'2025-01-03 12:00:00',89.99,'Shipped'),
(4,4,'2025-01-04 13:00:00',49.99,'Processing'),
(5,5,'2025-01-05 14:00:00',399.99,'Shipped'),
(6,6,'2025-01-06 15:00:00',129.99,'Pending'),
(7,8,'2025-01-08 17:00:00',129.99,'Shipped'),
(8,9,'2025-01-09 18:00:00',49.99,'Processing'),
(9,10,'2025-01-10 19:00:00',89.99,'Shipped'),
(10,1,'2025-01-11 10:30:00',249.99,'Processing'),
(11,2,'2025-01-12 11:45:00',349.99,'Pending'),
(12,3,'2025-01-13 14:15:00',109.99,'Shipped'),
(13,4,'2025-01-14 16:00:00',59.99,'Delivered'),
(14,5,'2025-01-15 09:30:00',459.99,'Cancelled'),
(15,6,'2025-01-16 11:15:00',139.99,'Pending'),
(16,8,'2025-01-18 13:30:00',159.99,'Shipped'),
(17,9,'2025-01-19 14:50:00',69.99,'Cancelled'),
(18,10,'2025-01-20 15:40:00',99.99,'Delivered');
INSERT OrderDetails (OrderDetailID,OrderID,ProductID,Quantity,UnitPrice,Subtotal) VALUES
(1,1,1,1,699.99,699.99),
(2,1,3,2,199.99,399.98),
(3,2,2,1,999.99,999.99),
(4,2,4,3,19.99,59.97),
(5,3,5,1,49.99,49.99),
(6,3,7,2,24.99,49.98),
(7,4,8,1,89.99,89.99),
(8,4,9,1,49.99,49.99),
(9,5,10,1,14.99,14.99),
(10,5,12,1,299.99,299.99),
(11,6,11,2,29.99,59.98),
(12,6,13,1,99.99,99.99),
(13,7,14,1,9.99,9.99),
(14,7,15,2,129.99,259.98),
(15,8,16,1,19.99,19.99),
(16,8,17,1,399.99,399.99),
(17,9,18,3,79.99,239.97),
(18,9,19,2,129.99,259.98),
(19,10,20,2,29.99,59.98),
(20,10,1,1,699.99,699.99),
(21,11,2,2,999.99,1999.98),
(22,11,3,1,199.99,199.99),
(23,12,4,4,19.99,79.96),
(24,12,5,2,49.99,99.98),
(25,13,6,1,14.99,14.99),
(26,13,7,1,24.99,24.99),
(27,14,8,2,89.99,179.98),
(28,14,9,3,49.99,149.97),
(29,15,10,2,14.99,29.98),
(30,15,11,1,29.99,29.99),
(31,16,12,1,299.99,299.99),
(32,16,13,1,99.99,99.99),
(33,17,14,3,9.99,29.97),
(34,17,15,1,129.99,129.99),
(35,18,16,2,19.99,39.98),
(36,18,17,1,399.99,399.99);
-- SELECT Products.ProductName, Products.Price, Categories.CategoryName
-- FROM Products
-- JOIN Categories ON Categories.CategoryID=Products.CategoryID
-- WHERE Categories.CategoryName='Electronics'
-- SELECT FullName, Email, Address
-- FROM Customers
-- WHERE Customers.Address LIKE '%Willow Way'
-- SELECT *FROM Orders
-- WHERE TotalAmount>200
-- SELECT ProductName, Price
-- FROM Products
-- ORDER BY Price DESC LIMIT 5
-- SELECT Customers.FullName, COUNT(Orders.CustomerID) AS TotalOrders
-- FROM Customers
-- LEFT JOIN Orders ON Orders.CustomerID=Customers.CustomerID
-- GROUP BY Customers.FullName
-- SELECT Products.ProductName, Products.StockQuantity, Categories.CategoryName
-- FROM Products
-- JOIN Categories ON Categories.CategoryID=Products.CategoryID
-- WHERE Products.StockQuantity < 50
-- SELECT Orders.OrderID, Orders.OrderDate, Orders.TotalAmount, Customers.FullName
-- FROM Orders
-- JOIN Customers ON Orders.CustomerID=Customers.CustomerID
-- WHERE Orders.OrderDate > '2025-01-01' AND Orders.OrderDate < '2025-01-10'
-- ORDER BY Orders.OrderDate
-- SELECT Products.ProductName, SUM(Products.Price * OrderDetails.Quantity) AS TotalRevenue
-- FROM Products
-- JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID
-- JOIN Orders ON OrderDetails.OrderID=Orders.OrderID
-- GROUP BY Products.ProductID, Products.ProductName
-- ORDER BY TotalRevenue DESC
-- SELECT FullName, Email
-- FROM Customers
-- LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID
-- WHERE Orders.OrderID IS NULL
SELECT Customers.FullName, SUM(Orders.TotalAmount) AS TotalSpending
FROM Customers
JOIN Orders ON Customers.CustomerID=Orders.CustomerID
GROUP BY Customers.FullName
ORDER BY TotalSpending DESC LIMIT 3
To embed this project on your website, copy the following code and paste it into your website's HTML: