SQL PROJECT
BY / Wesal Medhat
A simple SQL project focused on designing basic relational tables and writing fundamental queries (SELECT, JOIN, GROUP BY) to answer straightforward business questions about customers and orders.
ABOUT THE PROJECT:
THE RELATIONAL SCHEMA
SELECT Customer_Name ,City
FROM Customers;
Q1/ List all customers and the cities they live in
Q2/How many unique cities do customers come from?
SELECT COUNT(DISTINCT City) AS NumOfCities’
FROM Customers ;
Q3/Show all customers who live in Alexandria.
SELECT Customer_Name,City,Customer_ID
FROM Customers
WHERE City ='Alex';
Q4/List customers who live in Cairo or Giza.
SELECT Customer_Name ,City,Customer_ID
FROM Customers
WHERE City ='Cairo' OR City = 'Giza';
Q5/How many customers are there in each city?
SELECT City , COUNT(Customer_ID) ASNumberOfCustomers
FROM Customers
GROUP BY City;
Q6/Which cities have more than 2 customers?
SELECT City , COUNT(Customer_ID) AS NumberOfCustomers
FROM Customers
GROUP BY City
HAVING COUNT(Customer_ID) >2;
Q7/List products that cost more than 1000.
SELECT Product_Name,Price
FROM Products
WHERE Price >1000;
Q8/Sort products from most expensive to cheapest.
SELECT Product_name,Price
FROM Products
ORDER BY Price DESC;
Q9/ What is the average product price?
SELECT AVG(Price) AS AveragePrice
FROM Products;
Q10/Show orders where quantity is greater than 1.
SELECT *
FROM Orders
WHERE Quantity >1;
Q11/Show the earliest 5 orders.
SELECT *
FROM Orders
ORDER BY Order_Date ASC
LIMIT 5;
Q12/What is the total quantity sold?
SELECT SUM(Quantity) AS TotalQuantitySold
FROM Orders;
Q13/Show total quantity sold per product.
SELECT Products.Product_Name, SUM(Orders.Quantity) AS TotalQuantitySold
FROM Products
JOIN Orders ON Products.Product_ID = Orders.Product_ID
GROUP BY Products.Product_Name;
Q14/Which products sold more than 3 units?
SELECT Products.Product_Name, SUM(Orders.Quantity) AS TotalQuantitySold
FROM Products
JOIN Orders ON Products.Product_ID = Orders.Product_ID
GROUP BY Products.Product_Name
HAVING SUM(Orders.Quantity) >3;
Q15/Show total revenue per product.
SELECT Products.Product_Name, SUM(Orders.Quantity* Products.Price) AS TotalRevenue
FROM Products
JOIN Orders ON Products.Product_ID = Orders.Product_ID
GROUP BY Products.Product_Name;
Q16/Which product generated the highest revenue?
SELECT Products.Product_Name, SUM(Orders.Quantity* Products.Price) AS TotalRevenue
FROM Products
JOIN Orders ON Products.Product_ID = Orders.Product_ID
GROUP BY Products.Product_Name
LIMIT 1;
Q17/Show each order with customer name.
SELECT Customers.Customer_Name ,Orders.*
FROM Customers
LEFT JOIN Orders ON Customers.Customer_ID = Orders.Customer_ID;
Q18/Show customer name and product name for each order.
SELECT Customers.Customer_Name ,Orders.* ,Products.Product_Name
FROM Customers
LEFT JOIN Orders ON Customers.Customer_ID = Orders.Customer_ID
LEFT JOIN Products ON Products.Product_ID = Orders.Product_ID;
Q19/How many orders did each customer make?
SELECT Customers.Customer_Name ,COUNT(Orders.Order_ID) AS NumberOfOrders
FROM Customers
JOIN Orders ON Orders.Customer_ID = Customers.Customer_ID
GROUP BY Customer_Name;
Q20/Which customers made more than one order?
SELECT Customers.Customer_Name ,COUNT(Orders.Order_ID) AS NumberOfOrders
FROM Customers
JOIN Orders ON Orders.Customer_ID = Customers.Customer_ID
GROUP BY Customer_Name
HAVING COUNT(Orders.Order_ID) >1;
Q21/Show total quantity ordered by each customer.
SELECT Customers.Customer_Name ,SUM(Orders.Quantity) AS TotalQuantityOrdered
FROM Customers
JOIN Orders ON Orders.Customer_ID = Customers.Customer_ID
GROUP BY Customer_Name;
Q22/Show total revenue generated by each customer.
SELECT Customers.Customer_Name ,SUM(Orders.Quantity* Products.Price) AS TotalRevenue
FROM Customers
JOIN Orders ON Orders.Customer_ID = Customers.Customer_ID
JOIN Products ON Products.Product_ID = Orders.Product_ID
GROUP BY Customer_Name;
Q23/Which city generated the highest number of orders?
SELECT Customers.City,COUNT(Orders.order_id) AS OrderCount
FROM Customers
JOIN Orders ON Customers.Customer_id = Orders.Customer_id
GROUP BY Customers.City
ORDER BY OrderCount DESC
LIMIT 1;
Q24/Show number of orders per city.
SELECT Customers.City,COUNT(Orders.order_id) AS OrderCount
FROM Customers
LEFT JOIN Orders ON Customers.Customer_id = Orders.Customer_id
GROUP BY Customers.City;
Q25/Identify the top-selling product based on quantity.
SELECT Products.Product_name,SUM(Orders.Quantity) AS TotalQuantity
FROM Products
JOIN Orders ON Products.Product_id = Orders.Product_id
GROUP BY Products.Product_name
ORDER BY TotalQuantity DESC
LIMIT 1;
THANK
YOU