Sales Management System.sql

--1. List all customers from Mumbai
SELECT * FROM Customers
WHERE City = 'Mumbai';

--2. Find total number of orders placed
SELECT COUNT(*) AS TotalOrders FROM Orders;

-- 3. Total quantity ordered per product
SELECT p.Name, SUM(o.Quantity) AS TotalQuantity
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY p.Name;

-- 4. Total revenue generated per product
SELECT p.Name, SUM(o.Quantity * p.Price) AS Revenue
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY p.Name;

-- 5. Top 3 customers by spending
SELECT c.Name, SUM(o.Quantity * p.Price) AS TotalSpent
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY c.Name
ORDER BY TotalSpent DESC
FETCH FIRST 3 ROWS ONLY;

-- 6. Show orders placed in the last 7 days
SELECT * FROM Orders
WHERE OrderDate >= SYSDATE - 7;

-- 7. Find average order value
SELECT AVG(o.Quantity * p.Price) AS AvgOrderValue
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID;

-- 8. Revenue generated by each product category
SELECT p.Category, SUM(o.Quantity * p.Price) AS Revenue
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY p.Category;

-- 9. Which city has the most customers?
SELECT City, COUNT(*) AS CustomerCount
FROM Customers
GROUP BY City
ORDER BY CustomerCount DESC;

-- 10. Total revenue from each customer
SELECT c.Name, SUM(o.Quantity * p.Price) AS Revenue
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY c.Name;

-- 11. List customers who placed more than 1 order
SELECT c.Name, COUNT(o.OrderID) AS OrderCount
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Name
HAVING COUNT(o.OrderID) > 1;

-- 12. Get the highest-priced product
SELECT * FROM Products
ORDER BY Price DESC
FETCH FIRST 1 ROWS ONLY;

-- 13. List customers and their first order date
SELECT c.Name, MIN(o.OrderDate) AS FirstOrder
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Name;

-- 14. Find customers who haven�t placed any orders
SELECT c.Name
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

-- 15. Total revenue per month
SELECT 
    TO_CHAR(OrderDate, 'YYYY-MM') AS Month,
    SUM(o.Quantity * p.Price) AS Revenue
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY TO_CHAR(OrderDate, 'YYYY-MM')
ORDER BY TO_CHAR(OrderDate, 'YYYY-MM');


-- 16. Most popular product (highest quantity sold)
SELECT p.Name, SUM(o.Quantity) AS TotalSold
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY p.Name
ORDER BY TotalSold DESC
FETCH FIRST 1 ROWS ONLY;

-- 17. Total order count and total quantity by each customer
SELECT c.Name, COUNT(o.OrderID) AS Orders, SUM(o.Quantity) AS TotalQty
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Name;