50 SQL Queries

1. Retrieve all customer names and their contact numbers

SELECT Name, ContactNumber FROM Customer;

2. List all products with their name, category, and price

SELECT Name, Category, Price FROM Product;

3. Get all suppliers located in a specific city (e.g., 'Manila')

SELECT * FROM Supplier WHERE Address LIKE '%Vinzons%';

4. Show all staff members with their positions and email addresses
SELECT Name, Position, Email FROM Staff;

5. Retrieve all store locations and capacities

SELECT Location, Capacity FROM Store;

6. Find the total number of customers

SELECT COUNT(*) AS TotalCustomers FROM Customer;

7. Count how many products are in the chosen category

SELECT COUNT(*) AS ElectronicsCount FROM Product WHERE Category = 'Nuts';

8. Display all purchases made on a specific date

SELECT * FROM Purchases WHERE PurchaseDate = '2025-04-01';

9. List all suppliers and the number of products they supply

SELECT SupplierID, COUNT(*) AS ProductsSupplied FROM Supplied_By GROUP
BY SupplierID;

10. Find all staff who have no contact number

SELECT * FROM Staff WHERE ContactNumber IS NULL;

11. Get the names of customers who purchased a product

SELECT DISTINCT C.Name FROM Customer C JOIN Purchases P ON
C.CustomerID = P.CustomerID;

12. Show all products and the suppliers who supplied them
SELECT P.Name, S.CompanyName FROM Product P JOIN Supplied_By SB ON
P.ProductID = SB.ProductID JOIN Supplier S ON SB.SupplierID = S.SupplierID;

13. List the staff who handled each customer

SELECT C.Name AS CustomerName, S.Name AS StaffName FROM Handled_By
HB JOIN Customer C ON HB.CustomerID = C.CustomerID JOIN Staff S ON
HB.StaffID = S.StaffID;

14. Find the products available in each store

SELECT S.Location, P.Name FROM Available_In AI JOIN Product P ON
AI.ProductID = P.ProductID JOIN Store S ON AI.StoreID = S.StoreID;

15. Get the names of staff and the stores they work in
SELECT Stf.Name, Str.Location FROM Staff_Store SS JOIN Staff Stf ON SS.StaffID
= Stf.StaffID JOIN Store Str ON SS.StoreID = Str.StoreID;

16. Retrieve all purchases along with product names and prices
SELECT C.Name, P.Name AS ProductName, P.Price FROM Purchases PU JOIN
Customer C ON PU.CustomerID = C.CustomerID JOIN Product P ON PU.ProductID
= P.ProductID;

17. Show all suppliers with their supplied product names and supply dates
SELECT S.CompanyName, P.Name AS ProductName, SB.SupplyDate FROM
Supplied_By SB JOIN Supplier S ON SB.SupplierID = S.SupplierID JOIN Product P
ON SB.ProductID = P.ProductID;

18. List customers and the staff who handled them, along with the handle type
SELECT C.Name AS Customer, S.Name AS Staff, HB.HandleType FROM
Handled_By HB JOIN Customer C ON HB.CustomerID = C.CustomerID JOIN Staff S
ON HB.StaffID = S.StaffID;

19. Get store locations where a specific product is available

SELECT S.Location FROM Available_In AI JOIN Store S ON AI.StoreID = S.StoreID
WHERE AI.ProductID = 1;

20. Show product details and their store stock levels

SELECT P.Name, S.Location, AI.StockLevel FROM Available_In AI JOIN Product P
ON AI.ProductID = P.ProductID JOIN Store S ON AI.StoreID = S.StoreID;

21. Get customer name, product name, and purchase date for every transaction
SELECT C.Name AS Customer, P.Name AS Product, PU.PurchaseDate FROM
Purchases PU JOIN Customer C ON PU.CustomerID = C.CustomerID JOIN Product
P ON PU.ProductID = P.ProductID;

22. Find all stores and the products they manage with the management period
SELECT S.Location, P.Name, M.StartDate, M.EndDate FROM Manages M JOIN
Store S ON M.StoreID = S.StoreID JOIN Product P ON M.ProductID = P.ProductID;

23. Show staff members who are managing multiple stores

SELECT StaffID, COUNT(*) AS StoreCount FROM Staff_Store GROUP BY StaffID
HAVING COUNT(*) > 1;

24. List stores and the total quantity of products supplied to them
SELECT S.StoreID, S.Location, SUM(SB.SupplyQuantity) AS TotalSupplied FROM
Store S JOIN Available_In AI ON S.StoreID = AI.StoreID JOIN Supplied_By SB ON
AI.ProductID = SB.ProductID GROUP BY S.StoreID;

25. Show all suppliers that have supplied more than 100 units of a product
SELECT SupplierID, ProductID FROM Supplied_By WHERE SupplyQuantity > 100;

26. Count the number of products per category

SELECT Category, COUNT(*) AS ProductCount FROM Product GROUP BY
Category;

27. Find the average price of products in each category

SELECT Category, AVG(Price) AS AvgPrice FROM Product GROUP BY Category;

28. Get the total quantity of each product available in all stores
SELECT ProductID, SUM(Quantity) AS TotalAvailable FROM Product GROUP BY
ProductID;

29. Find the highest-priced product in each category

SELECT Category, MAX(Price) AS MaxPrice FROM Product GROUP BY Category;

30. Count the number of purchases made by each customer

SELECT CustomerID, COUNT(*) AS PurchaseCount FROM Purchases GROUP BY
CustomerID;

31. Calculate the total supply quantity provided by each

supplier
SELECT SupplierID, SUM(SupplyQuantity) AS TotalSupplied FROM
Supplied_By GROUP BY SupplierID;
32. Find the store with the highest capacity
SELECT * FROM Store ORDER BY Capacity DESC LIMIT 1;

33. Show the total number of staff per position

SELECT Position, COUNT(*) AS StaffCount FROM Staff GROUP BY
Position;

34. List the average number of products managed per store

SELECT StoreID, AVG(ProductCount) FROM (SELECT StoreID, COUNT(ProductID)
AS ProductCount FROM Manages GROUP BY StoreID) AS Sub GROUP BY
StoreID;
35. Display the total revenue per product
SELECT P.ProductID, P.Name, COUNT(PU.ProductID) * P.Price AS Revenue FROM
Purchases PU JOIN Product P ON PU.ProductID = P.ProductID GROUP BY
P.ProductID;

36. Find customers who have never made a purchase

SELECT * FROM Customer WHERE CustomerID NOT IN (SELECT DISTINCT
CustomerID FROM Purchases);
37. List products not available in any store
SELECT * FROM Product WHERE ProductID NOT IN (SELECT DISTINCT
ProductID FROM Available_In);

38. Show suppliers who have supplied all products in the chosen category
SELECT DISTINCT SupplierID FROM Supplied_By WHERE ProductID IN (SELECT
ProductID FROM Product WHERE Category = 'Confectionery') GROUP BY
SupplierID HAVING COUNT(DISTINCT ProductID) = (SELECT COUNT(*) FROM
Product WHERE Category = 'Confectionery');

39. Get the names of staff who handled more than 5 customers
SELECT S.Name FROM Handled_By HB JOIN Staff S ON HB.StaffID = S.StaffID
GROUP BY HB.StaffID HAVING COUNT(DISTINCT HB.CustomerID) > 5;

40. List stores where the stock level is below "Low"

SELECT * FROM Available_In WHERE StockLevel = 'Low';

41. Find products managed by more than one store

SELECT ProductID FROM Manages GROUP BY ProductID HAVING
COUNT(DISTINCT StoreID) > 1;
42. Display products that have never been supplied by any supplier
SELECT * FROM Product WHERE ProductID NOT IN (SELECT DISTINCT
ProductID FROM Supplied_By);

43. Retrieve staff who are not assigned to any store

SELECT * FROM Staff WHERE StaffID NOT IN (SELECT DISTINCT StaffID FROM
Staff_Store);

44. List customers handled by a specific staff member (e.g., StaffID = 2)

SELECT C.Name FROM Handled_By HB JOIN Customer C ON HB.CustomerID =
C.CustomerID WHERE HB.StaffID = 2;

45. Show the names of all customers who purchased a specific product (e.g.,
ProductID = 1)
SELECT C.Name FROM Purchases P JOIN Customer C ON P.CustomerID =
C.CustomerID WHERE P.ProductID = 1;

46. Get the top 3 most purchased products

SELECT ProductID, COUNT(*) AS PurchaseCount FROM Purchases GROUP BY
ProductID ORDER BY PurchaseCount DESC LIMIT 3;

47. Find the most active customer based on purchase count

ELECT CustomerID, COUNT(*) AS TotalPurchases FROM Purchases GROUP BY
CustomerID ORDER BY TotalPurchases DESC LIMIT 1;

48. Show the latest purchase made by each customer

SELECT CustomerID, MAX(PurchaseDate) AS LatestPurchase FROM Purchases
GROUP BY CustomerID;

49. List all products with their current availability in each store
SELECT P.Name, S.Location, AI.StockLevel FROM Available_In AI JOIN Product P
ON AI.ProductID = P.ProductID JOIN Store S ON AI.StoreID = S.StoreID;

50. Determine which store has the most diverse product availability
SELECT StoreID, COUNT(DISTINCT ProductID) AS ProductVariety FROM
Available_In GROUP BY StoreID ORDER BY ProductVariety DESC LIMIT 1;