PROJECT2.sql

-- Task1
USE `modelcarsdb`;
-- Find the total no.of employees
select count(employeeNumber)as total_no_of_employee from employees;
-- List all employees with their basic information
select employeeNumber,firstName,lastName,email,jobTitle from employees;
-- Count the number of employees holding each job title
select count(employeeNumber),jobTitle from employees group by jobTitle;
-- Find the employees who don't have a manager (reports To is NULL)
select employeeNumber,firstName,lastName from employees where reportsTo ='NULL';
-- Calculate total sales generated by each sales representative.
select e.employeeNumber, e.jobTitle, SUM(od.quantityOrdered * od.priceEach) as totalsales from employees e
join customers c on e.lastName = c.contactLastName
join  orders o on o.customerNumber = c.customerNumber
join  orderdetails od on od.orderNumber = o.orderNumber
group by e.employeeNumber, e.jobTitle;
-- Find the most profitable sales representative based on total sales
select e.employeeNumber, e.firstName, e.lastName,e.jobTitle, SUM(od.quantityOrdered * od.priceEach) as totalsales from employees e
join customers c on e.lastName = c.contactLastName
join orders o on o.customerNumber = c.customerNumber
join orderdetails od on od.orderNumber = o.orderNumber
group by e.employeeNumber, e.firstName, e.lastName,e.jobTitle
order by totalsales desc
limit 1;
-- Find the names of all employees who have sold more than the average sales amount for their office.
select concat(e.firstName," ",e.lastName) as Employee_full_name, sum(quantityordered* priceEach) as Total_sales, avg(quantityordered *PriceEach) as avg_sales
from employees e 
join customers c on e.employeeNumber = c.salesRepEmployeeNumber
join orders o on c.customerNumber =o.customerNumber
join orderdetails od on o.orderNumber=od.orderNumber
group by Employee_full_name
having Total_sales> avg_sales;
---------------------------------------------------------------------------------------------------------------------------------------------
-- Task2
-- Find the average order amount for each customer.
select c.customerName, c.customerNumber, AVG(p.amount) from customers c 
join payments p on p.customerNumber = c.customerNumber 
group by  c.customerName, c.customerNumber ;
-- Find the number of orders placed in each month
select DATE_FORMAT(orderDate, '%Y-%m') as order_month, COUNT(*) as order_count from orders 
group by order_month 
order by order_month;
-- Identify orders that are still pending shipment (status = 'Pending').
select  orderNumber from  orders where status = 'Pending';
-- List orders along with customer details
select o.orderNumber, o.orderDate, o.status, c.customerName, c.contactLastName,c.contactFirstName, c.country from orders o
join  customers c on o.customerNumber = c.customerNumber;
-- Retrieve the most recent orders (based on order date).
select orderNumber,orderDate from  orders
order by orderDate desc
limit 10;  
-- Calculate total sales for each order
select o.orderNumber,SUM(od.quantityOrdered * od.priceEach) as totalSales from orders o
join orderDetails od ON o.orderNumber = od.orderNumber 
group by o.orderNumber
order by totalSales desc; 
-- Find the highest-value order based on total sales.
select o.orderNumber,SUM(od.quantityOrdered * od.priceEach) as totalSales from orders o
join  orderDetails od on o.orderNumber = od.orderNumber
group by o.orderNumber
order by  totalSales desc
limit 1;
--  List all orders with their corresponding order details.
select o.orderNumber,o.orderDate,o.status,od.productCode,od.quantityOrdered,od.priceEach,od.quantityOrdered * od.priceEach as totalPrice
from orders o
join  orderDetails od on o.orderNumber = od.orderNumber
order by o.orderNumber; 
-- List the most frequently ordered products
select  od.productCode,COUNT(od.productCode) as orderCount
from  orderDetails od
group by od.productCode
order by orderCount desc;
-- Calculate total revenue for each order
select o.orderNumber,SUM(od.quantityOrdered * od.priceEach) as totalRevenue
from orders o
join orderDetails od on o.orderNumber = od.orderNumber
group by o.orderNumber
order by totalRevenue desc; 
-- Identify the most profitable orders based on total revenue.
select o.orderNumber,SUM(od.quantityOrdered * od.priceEach) as totalRevenue
from orders o
join orderDetails od on o.orderNumber = od.orderNumber
group by o.orderNumber
order by totalRevenue desc limit 1; 
-- List all orders with detailed product information
select o.orderNumber,o.orderDate,o.status,od.productCode,p.productName,od.quantityOrdered,od.priceEach,(od.quantityOrdered * od.priceEach) as totalPrice
from orders o
join orderDetails od on o.orderNumber = od.orderNumber
join products p on od.productCode = p.productCode
order by o.orderNumber, od.productCode; 
-- Identify orders with delayed shipping (shippedDate > required Date).
select o.orderNumber,o.orderDate,o.requiredDate,o.shippedDate,o.status
from orders o
where o.shippedDate > o.requiredDate;
-- Find the most popular product combinations within order
select od1.productCode as productA,od2.productCode as productB,COUNT(*) as combinationCount
from orderDetails od1
join orderDetails od2 on od1.orderNumber = od2.orderNumber and od1.productCode < od2.productCode
group by productA, productB
order by combinationCount desc
limit 10; 
-- Calculate revenue for each order and identify the top 10 most profitable.
select o.orderNumber,SUM(od.quantityOrdered * od.priceEach) as totalRevenue
from  orders o
join orderDetails od on o.orderNumber = od.orderNumber
group by o.orderNumber
order by totalRevenue desc
limit 10;
-- Create a trigger that automatically updates a customer's credit limit after a new order is placed, reducing it by the order total.
DELIMITER //
CREATE TRIGGER update_credit_limit
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE totalOrderAmount DECIMAL(10, 2);
    -- Calculate the total order amount
    SELECT SUM(od.quantityOrdered * od.priceEach) INTO totalOrderAmount
    FROM orderDetails od
    WHERE od.orderNumber = NEW.orderNumber;
    -- Update the customer's credit limit
    UPDATE customers
    SET creditLimit = creditLimit - totalOrderAmount
    WHERE customerNumber = NEW.customerNumber;
END;//
-- Create a trigger that logs product quantity changes whenever an order detail is inserted or updated.
DELIMITER //
CREATE TRIGGER log_product_quantity_update
AFTER UPDATE ON orderDetails
FOR EACH ROW
BEGIN
    INSERT INTO product_quantity_log (productCode, quantityChanged, changeType)-- changeType is upadate or insert
    VALUES (NEW.productCode, NEW.quantityOrdered, 'UPDATE');
END;//