-- create the database

create database retailDB;

-- create table customer

create table customer(

CustomerID INT Primary key Auto_Increment,

FirstName varchar(50),

LastName varchar(50),

Email Varchar(50),

Phone varchar(50),

Address varchar(255),

City varchar(50),

State varchar(50),

Zipcode varchar(50),

Country varchar(50),

CreatedAt DATETIME Default current_timestamp()

);

-- create table product

create table product (

ProductID INT Primary Key Auto_Increment,

ProductName varchar(50),

CategoryID int,

Price DECIMAL(10,2),

Stock INT,

CreatedAT DATETIME Default current_timestamp()

);

-- create table categories

Create table categories (

CategoryID INT Primary Key Auto_Increment,

CategoryName Varchar(100),

Description varchar (255)

);

-- create orderss table

create table Orderss (

OrderID INT Primary Key Auto_Increment,

CustomerID int,

OrderDate Datetime Default current_timestamp(),

TotalAmount Decimal(10,2),

Foreign Key (CustomerID) references Customer(CustomerID)

);

-- create orderItemss table

create table OrdersItemss (

OrderItemID int primary key auto_increment,

OrderID int,

ProductID int,

Quantity int,

Price Decimal(10,2),

Foreign Key (ProductID) References Product (productID),

Foreign Key (OrderID) References Orderss(OrderID)

);

-- insert sample data into Categories table

Insert into Categories (CategoryName, Description)

values('Electronics', 'Devices and Gadgets'),

('Clothing','Apparel and Accessories'),

('Books','Printed and Electronic Books ');

-- insert sample data into product table

Insert into product( ProductName, CategoryID, Price, Stock)

values ('Smartphone',1,699.99,50),

('Laptop',1,999.99, 30),

('T-shirt',2,19.99,100),

('jeans',2,49.99,60),

('Fiction Novel',3,14.99,200),

('Science Journal',3,29.99,150);

-- insert sample data into customer

Insert into Customer (FirstName,LastName,Email,Phone, Address, City, State, Zipcode, Country)

Values ('john','king','john.king@gmail.com','123-456-0789','564 Queen St.','Toronto','ON','L1Z0M2','Canada'),

('Rick','willliams','rick.williams@gmail.com','345-674-0986','23 Spadina Avenue','Toronto','ON','LP6152','Canada'),

('Mckenzie','Burns','Mckenzie.burns@gmail.com','456-870-1345','23 Bloor St.','Toronto','ON','LH20NW','Canada');

-- insert sampe data into orderss

Insert into orderss(CustomerID, OrderDate, TotalAmount)

Values (1,now(),719.98),

(2,now(),49.99),

(3,now(),44.98);

-- insert sample data into ordersitemss

Insert into Ordersitemss(OrderId, ProductID, Quantity, Price)

values(1,1,1,699.99),

(1,3,1,19.99),

(2,4,1,49.99),

(3,5,1,14.99),

(3,6,1,29.99);

-- QUERIES EXECUTION

-- Query 1: Retrieve all orders for a specific customer

SELECT

o.OrderID,

o.OrderDate,

o.TotalAmount,

oi.ProductID,

oi.Quantity,

oi.Price,

p.ProductName

FROM

orderss o

JOIN

ordersitemss oi ON o.OrderID = oi.OrderID

JOIN

product p ON oi.ProductID = p.ProductID

WHERE

customerID IN (2 , 3);

-- Query 2: Find Total Sales of each product

SELECT

p.ProductName,

p.ProductName,

oi.ProductID,

SUM(oi.Quantity * oi.Price) AS Total_Sales

FROM

ordersitemss oi

JOIN

product p ON oi.ProductID = p.ProductID

GROUP BY oi.ProductID

ORDER BY Total_Sales DESC;

-- Query 3: Average order value

select avg(TotalAmount) as 'average total price' from orderss;

-- Query 4: List top 5 customers by total spending

SELECT

o.orderID,

o.TotalAmount AS TotalSpent,

c.FirstName,

c.LastName

FROM

orderss o

JOIN

customer c ON o.customerId = c.customerID

ORDER BY TotalSpent DESC

LIMIT 5;

-- Query 5 : Retreive most popular Product Category;

Select c.CategoryID, c.CategoryName, sum(oi.Quantity) as 'total quantity',

row_number() over(order by sum(oi.Quantity) desc) as rn

from categories c

join product p on c.CategoryID = p.CategoryID

join ordersitemss oi on oi.ProductID = p.ProductID

Group by c.CategoryID, c.CategoryName

limit 1;

-- Query 6: List all products that are out of stock

SELECT

ProductName

FROM

product

WHERE

stock = 0;

-- with category name

SELECT

c.CategoryName, p.ProductName, p.ProductID

FROM

product p

JOIN

categories c ON p.CategoryID = c.CategoryID

WHERE

stock = 0;

-- Query 7: Find customers who placed orders in the last 30 days

SELECT

c.CustomerId, c.FirstName, c.LastName, c.Email, c.Phone

FROM

customer c

JOIN

orderss o ON c.CustomerID = o.CustomerID

WHERE

o.OrderDate >= DATE_ADD(NOW(), INTERVAL - 30 DAY);

-- Query 8: Calculate the total number of orders placed each month

SELECT

YEAR(OrderDate) AS orderyear,

MONTH(OrderDate) AS ordermonth,

COUNT(OrderID) AS totalorders

FROM

orderss

GROUP BY MONTH(OrderDate), Year(OrderDate);

-- Query 9: Retreive the details of most recent order

SELECT

o.OrderID,

o.OrderDate,

o.TotalAmount,

c.FirstName,

c.LastName

FROM

orderss o

JOIN

customer c ON o.CustomerId = c.CustomerID

ORDER BY o.OrderDate DESC

LIMIT 1;

-- Query 10: Find Average price of products of each category

SELECT

c.CategoryID, c.categoryName, AVG(p.price) AS AveragePrice

FROM

Categories c

JOIN

Product p ON c.CategoryID = p.CategoryID

GROUP BY c.CategoryId , c.CategoryName;

-- Query 11 List customers who have never placed an order

SELECT

CustomerID, FirstName, LastName, Email, Phone

FROM

customer

WHERE

CustomerID NOT IN (SELECT

CustomerID

FROM

orderss);

-- Query 12: Retrieve the total quantity sold for each product

SELECT

p.ProductName,

p.ProductId,

SUM(oi.Quantity) AS TotalQuantitySold

FROM

Ordersitemss oi

JOIN

product p ON oi.ProductId = p.ProductID

GROUP BY p.ProductName , p.ProductID;

-- Query 13: Calculate the total revenue generated by each category

SELECT

c.CategoryID,

c.CategoryName,

SUM(oi.Quantity * oi.price) AS TotalRevenue

FROM

ordersitemss oi

JOIN

product p ON oi.ProductID = p.ProductID

JOIN

categories c ON p.CategoryId = c.categoryID

GROUP BY c.CategoryID , c.CategoryName

ORDER BY TotalRevenue DESC;

-- Query 14: Find the highest-prices product in each category

select c.CategoryName, C.CategoryID,

max(p.price) over( partition by c.CategoryID)

from product p join categories c on p.CategoryID = c.CategoryID;

-- Alternate Method

SELECT

c.CategoryID, c.CategoryName, p1.price

FROM

product p1

JOIN

categories c ON p1.CategoryId = c.CategoryID

WHERE

p1.price IN (SELECT

MAX(p2.price)

FROM

product p2

WHERE

p1.CategoryID = p2.CategoryID);

-- Query 15: Retreive orders with a total amount greater than a specific value (e.g, $500)

SELECT

o.OrderID,

c.CustomerID,

c.FirstName,

c.LastName,

o.TotalAmount

FROM

Orderss o

JOIN

customer c ON o.CustomerID = c.customerID

WHERE

o.TotalAmount >= 49.99

ORDER BY o.TotalAmount DESC;

-- Query 16: List orders with more than a specified number of items (e.g, 5 items)

SELECT

o.OrderID,

c.CustomerID,

c.FirstName,

c.LastName,

COUNT(oi.OrderItemID) AS TotalItems

FROM

ordersitems oi

JOIN

orderss o ON oi.OrderId = o.OrderID

JOIN

customer c ON c.customerID = o.customerID

GROUP BY o.OrderID , c.customerID , c.FirstName , c.lastName

HAVING COUNT(oi.OrderItemID) > 5;

-- Trigers Initiation

-- Create a log table

create table changelog (LogID INT Primary Key Auto_Increment,

TableName varchar(50),

operation varchar(50),

RecordID INT,

ChangeDate Datetime default current_timestamp(),

changedBy varchar(100)

);

-- insert trigger for product table

Delimiter $$

create trigger trg_1 after insert on product for each row

Begin

Insert into changelog (TableName, Operation, RecordID, Changedby)

values ('Product','Insert', new.productID, current_timestamp());

End $$

Delimiter ;

insert into product (ProductName, CategoryID, Price, stock)

values('wireless mouse',1,4.99,20);

-- update trigger for product table

Delimiter $$

create trigger trig_2 after update on product for each row

Begin

Insert into changelog(TableName, Operation,RecordID,ChangedBy)

values('Product','Update',new.productID, current_timestamp());

End $$

Delimiter ;

update product set price = price -300 where productID = 2;

select * from changelog;

-- delete trigger for product table

Delimiter $$

create trigger trig_3 after delete on product for each row

Begin

Insert into changelog(TableName,Operation,RecordID,ChangedBy)

values ('Product','Delete',old.ProductID,current_timestamp());

End $$

Delimiter ;

--

-- Implementing Views

--

CREATE VIEW vw_ProductDetails AS

SELECT

p.ProductId, p.ProductName, p.Price, p.Stock, c.CategoryName

FROM

product p

JOIN

categories c ON p.CategoryID = c.categoryID;

-- Display product details with category names using view

select * from vw_ProductDetails;

-- view for customer orders: A view to get a summary of orders placed by each customer.

create view vw_customerorders as

SELECT

c.customerID,

c.firstname,

c.lastname,

COUNT(o.orderId) AS totalorders,

SUM(oi.Quantity * p.Price) AS TotalAmount

FROM

customer c

JOIN

orderss o ON c.customerId = o.customerID

JOIN

ordersitemss oi ON o.orderID = oi.OrderId

JOIN

Product p ON oi.ProductId = p.productId

GROUP BY c.customerId , c.firstname , c.lastname;

-- view for Recent Orders: A view to display orders placed in the last 30 days.

create view vw_RecentOrders as

SELECT

c.customerID,

c.firstname,

c.lastname,

o.orderId,

o.OrderDate,

COUNT(o.orderId) AS totalorders,

SUM(oi.Quantity * p.Price) AS TotalAmount

FROM

customer c

JOIN

orderss o ON c.customerId = o.customerID

JOIN

ordersitemss oi ON o.orderID = oi.OrderId

JOIN

Product p ON oi.ProductId = p.productId

where o.orderDate >= date_add(now(), Interval - 30 DAY)

GROUP BY c.customerId , c.firstname , c.lastname, o.orderId, o.orderDate

;

-- Query Retreive all products with category names

-- using vw_ProductDetails

select * from vw_ProductDetails;

-- Query Retrieve products within a specified product range

select * from vw_ProductDetails where price between 100 and 500;

-- Query Count the number of products in each category

select categoryName, count(ProductId) as productcount

from vw_ProductDetails Group by categoryName;

-- Query Retrieve customers with more than 5 orders;

select * from vw_Customerorders where totalorders > 1;

-- Query Retreive the total mount spent by each customer

SELECT

customerId, firstname, lastname, TotalAmount

FROM

vw_customerorders

ORDER BY TotalAmount DESC;

-- Query Retreive recent order above a certain amount

select * from vw_recentorders

where totalamount > 1000;

-- Retreive the latest order for each customer

SELECT

ro.OrderID,

ro.OrderDate,

ro.CustomerID,

ro.FirstName,

ro.LastName,

ro.totalamount

FROM

vw_Recentorders ro

JOIN

(SELECT

customerId, MAX(OrderDate) AS LatestOrderDate

FROM

vw_RecentOrders

GROUP BY customerID) latest ON ro.customerID = latest.customerID

AND ro.Orderdate = latest.latestorderdate

ORDER BY ro.orderdate DESC;

-- Retreive products in a specific category

SELECT

*

FROM

vw_ProductDetails

WHERE

CategoryName = 'Books';

-- Retreive Total Sales for each category

SELECT

co.customerId,

co.firstname,

co.lastname,

o.orderID,

o.orderDate,

pd.productname,

oi.Quantity,

pd.price

FROM

orderss o

JOIN

ordersitemss oi ON o.orderId = oi.OrderID

JOIN

vw_ProductDetails pd ON oi.ProductId = pd.productId

JOIN

vw_customerorders co ON o.customerID = co.customerId

ORDER BY o.orderdate DESC;

-- Query: Retreive Products sold in the last month

SELECT

p.Productid, p.ProductName, SUM(oi.Quantity) AS totalsold

FROM

orderss o

JOIN

ordersitemss oi ON o.orderID = oi.OrderID

JOIN

Product p ON oi.ProductID = p.ProductID

WHERE

o.OrderDate > DATE_ADD(NOW(), INTERVAL - 1 MONTH)

GROUP BY p.ProductId , p.ProductName

ORDER BY TotalSold DESC;