Queries for Optimization
Simple Queries
1. Select All Customers
SELECT * FROM Customers;
2. Select All Products
SELECT * FROM Products;
3. Select All Orders
SELECT * FROM Orders;
Complex Queries
4. Join Orders with Customers
SELECT Orders.OrderID, Customers.FirstName, Customers.LastName,
Orders.OrderDate, Orders.TotalAmount
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
5. Join Orders with OrderDetails and Products
SELECT Orders.OrderID, Customers.FirstName, Customers.LastName, Orders.OrderDate,
Products.ProductName, OrderDetails.Quantity, OrderDetails.UnitPrice FROM Orders JOIN Customers ON
Orders.CustomerID = Customers.CustomerID JOIN OrderDetails ON Orders.OrderID =
OrderDetails.OrderID JOIN Products ON OrderDetails.ProductID = Products.ProductID;
6. **Find Total Amount Spent by Each Customer**
SELECT Customers.CustomerID, Customers.FirstName, Customers.LastName,
SUM(Orders.TotalAmount) AS TotalSpent FROM Orders JOIN Customers ON Orders.CustomerID
= Customers.CustomerID GROUP BY Customers.CustomerID, Customers.FirstName,
Customers.LastName;
7. **Find the Most Purchased Product**
SELECT Products.ProductID, Products.ProductName, SUM(OrderDetails.Quantity) AS
TotalQuantity FROM OrderDetails JOIN Products ON OrderDetails.ProductID =
Products.ProductID GROUP BY Products.ProductID, Products.ProductName ORDER BY
TotalQuantity DESC LIMIT 1;
8. **Find Orders with More Than One Product**
SELECT Orders.OrderID, COUNT(OrderDetails.ProductID) AS ProductCount FROM Orders JOIN
OrderDetails ON Orders.OrderID = OrderDetails.OrderID GROUP BY Orders.OrderID HAVING
ProductCount > 1;
9. **Find Customers Who Have Spent Over a Certain Amount**
SELECT Customers.CustomerID, Customers.FirstName, Customers.LastName,
SUM(Orders.TotalAmount) AS TotalSpent FROM Orders JOIN Customers ON Orders.CustomerID
= Customers.CustomerID GROUP BY Customers.CustomerID, Customers.FirstName,
Customers.LastName HAVING TotalSpent > 1000;
10. **Find All Products That Have Never Been Ordered**
SELECT Products.ProductID, Products.ProductName FROM Products LEFT JOIN OrderDetails ON
Products.ProductID = OrderDetails.ProductID WHERE OrderDetails.ProductID IS NULL;
