Trust Coding : Programming: 30 SQL Subquery & Join Questions with Explanations and Solutions

📘 Final Lab Practice – SQL Subquery & Join (30 Questions with Schema, Explanation & Solutions)

This post contains 30 comprehensive SQL final lab exam questions with solutions, schema, and explanations. Focused on Products, Customers, Orders, Invoices, Payments, and Online Order Types. Includes subqueries, joins, group by, having, exists, not exists, in, not in.

🔹 Database Schema

Product(ProductID, ProductName, Price, CategoryID)
Category(CategoryID, CategoryName)
Customer(CustomerID, CustomerName)
Orders(OrderID, CustomerID, OrderDate, OrderTypeID)
OrderType(OrderTypeID, TypeName)
Invoice(InvoiceID, OrderID, TotalAmount, InvoiceDate)
InvoiceDetails(InvoiceID, ProductID, Quantity)
Payment(PaymentID, InvoiceID, Amount, PaymentDate)

This schema simulates an online sales and billing system. Products belong to categories, customers place orders, invoices are generated, and payments are tracked. All questions use this schema.

🔹 Products (Q1–Q10)

Q1. Product(s) with the highest price
Explanation: Use MAX() subquery.
SELECT ProductName, Price FROM Product WHERE Price = (SELECT MAX(Price) FROM Product);

Q2. Second highest price
Explanation: Exclude max price in subquery.
SELECT ProductName, Price FROM Product WHERE Price = (SELECT MAX(Price) FROM Product WHERE Price < (SELECT MAX(Price) FROM Product));

Q3. Products never sold
Explanation: LEFT JOIN with InvoiceDetails, check NULL.
SELECT p.ProductName FROM Product p LEFT JOIN InvoiceDetails d ON p.ProductID = d.ProductID WHERE d.ProductID IS NULL;

Q4. Top 5 best-selling products
Explanation: SUM quantities, GROUP BY, ORDER DESC.
SELECT p.ProductName, SUM(d.Quantity) AS TotalSold FROM Product p JOIN InvoiceDetails d ON p.ProductID = d.ProductID GROUP BY p.ProductName ORDER BY TotalSold DESC LIMIT 5;

Q5. Products above average price
Explanation: Compare price with AVG.
SELECT ProductName, Price FROM Product WHERE Price > (SELECT AVG(Price) FROM Product);

Q6. Products sold more than 100 units
Explanation: SUM and HAVING > 100.
SELECT p.ProductName, SUM(d.Quantity) AS TotalSold FROM Product p JOIN InvoiceDetails d ON p.ProductID = d.ProductID GROUP BY p.ProductName HAVING SUM(d.Quantity) > 100;

Q7. Products in categories with avg price > 500
SELECT p.ProductName, p.Price FROM Product p WHERE p.CategoryID IN (SELECT CategoryID FROM Product GROUP BY CategoryID HAVING AVG(Price) > 500);

Q8. Cheapest product per category
SELECT p.ProductName, p.Price, p.CategoryID FROM Product p WHERE Price = (SELECT MIN(Price) FROM Product WHERE CategoryID = p.CategoryID);

Q9. Products sold in at least 10 invoices
SELECT p.ProductName, COUNT(DISTINCT d.InvoiceID) AS InvoiceCount FROM Product p JOIN InvoiceDetails d ON p.ProductID = d.ProductID GROUP BY p.ProductName HAVING COUNT(DISTINCT d.InvoiceID) >= 10;

Q10. Products ordered by every customer
SELECT p.ProductName FROM Product p WHERE NOT EXISTS (SELECT c.CustomerID FROM Customer c WHERE NOT EXISTS (SELECT 1 FROM Orders o JOIN Invoice i ON o.OrderID = i.OrderID JOIN InvoiceDetails d ON i.InvoiceID = d.InvoiceID WHERE o.CustomerID = c.CustomerID AND d.ProductID = p.ProductID));

🔹 Customers & Orders (Q11–Q20)

Q11. Customer with maximum orders
SELECT c.CustomerName FROM Customer c JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName HAVING COUNT(o.OrderID) = (SELECT MAX(OrderCount) FROM (SELECT COUNT(OrderID) AS OrderCount FROM Orders GROUP BY CustomerID) AS t);

Q12. Customers who never ordered
SELECT c.CustomerName FROM Customer c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.OrderID IS NULL;

Q13. Customers who bought at least one product from every category
SELECT c.CustomerName FROM Customer c WHERE NOT EXISTS (SELECT cat.CategoryID FROM Category cat WHERE NOT EXISTS (SELECT 1 FROM Orders o JOIN Invoice i ON o.OrderID = i.OrderID JOIN InvoiceDetails d ON i.InvoiceID = d.InvoiceID JOIN Product p ON d.ProductID = p.ProductID WHERE o.CustomerID = c.CustomerID AND p.CategoryID = cat.CategoryID));

Q14. Latest order date per customer
SELECT c.CustomerName, o.OrderID, o.OrderDate FROM Orders o JOIN Customer c ON o.CustomerID = c.CustomerID WHERE o.OrderDate = (SELECT MAX(o2.OrderDate) FROM Orders o2 WHERE o2.CustomerID = o.CustomerID);

Q15. Customers buying same product >3 times
SELECT c.CustomerName, p.ProductName, SUM(d.Quantity) AS TotalBought FROM Customer c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN Invoice i ON o.OrderID = i.OrderID JOIN InvoiceDetails d ON i.InvoiceID = d.InvoiceID JOIN Product p ON d.ProductID = p.ProductID GROUP BY c.CustomerName, p.ProductName HAVING SUM(d.Quantity) > 3;

Q16. Customer who spent highest total
SELECT c.CustomerName, SUM(i.TotalAmount) AS TotalSpent FROM Customer c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN Invoice i ON o.OrderID = i.OrderID GROUP BY c.CustomerName ORDER BY TotalSpent DESC LIMIT 1;

Q17. Customers ordering only Online
SELECT c.CustomerName FROM Customer c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID AND o.OrderTypeID <> (SELECT OrderTypeID FROM OrderType WHERE TypeName = 'Online'));

Q18. Customers who placed both Online and COD orders
SELECT DISTINCT c.CustomerName FROM Customer c WHERE EXISTS (SELECT 1 FROM Orders o JOIN OrderType t ON o.OrderTypeID = t.OrderTypeID WHERE o.CustomerID = c.CustomerID AND t.TypeName = 'Online') AND EXISTS (SELECT 1 FROM Orders o JOIN OrderType t ON o.OrderTypeID = t.OrderTypeID WHERE o.CustomerID = c.CustomerID AND t.TypeName = 'COD');

Q19. Top 3 customers by invoice count
SELECT c.CustomerName, COUNT(i.InvoiceID) AS InvoiceCount FROM Customer c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN Invoice i ON o.OrderID = i.OrderID GROUP BY c.CustomerName ORDER BY InvoiceCount DESC LIMIT 3;

Q20. Customers with pending invoices
SELECT DISTINCT c.CustomerName FROM Customer c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN Invoice i ON o.OrderID = i.OrderID WHERE i.InvoiceID NOT IN (SELECT InvoiceID FROM Payment);

🔹 Invoices & Payments (Q21–Q30)

Q21. Invoices above average total
SELECT InvoiceID, TotalAmount FROM Invoice WHERE TotalAmount > (SELECT AVG(TotalAmount) FROM Invoice);

Q22. Invoice with maximum total
SELECT InvoiceID, TotalAmount FROM Invoice WHERE TotalAmount = (SELECT MAX(TotalAmount) FROM Invoice);

Q23. Unpaid invoices
SELECT i.InvoiceID, i.TotalAmount FROM Invoice i WHERE i.InvoiceID NOT IN (SELECT InvoiceID FROM Payment);

Q24. Customer(s) who paid highest invoice
SELECT c.CustomerName, i.TotalAmount FROM Customer c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN Invoice i ON o.OrderID = i.OrderID WHERE i.TotalAmount = (SELECT MAX(TotalAmount) FROM Invoice);

Q25. Invoices containing product price >1000
SELECT DISTINCT i.InvoiceID FROM Invoice i JOIN InvoiceDetails d ON i.InvoiceID = d.InvoiceID JOIN Product p ON d.ProductID = p.ProductID WHERE p.Price > 1000;

Q26. Invoices with total items >5
SELECT i.InvoiceID, SUM(d.Quantity) AS ItemCount FROM Invoice i JOIN InvoiceDetails d ON i.InvoiceID = d.InvoiceID GROUP BY i.InvoiceID HAVING SUM(d.Quantity) > 5;

Q27. Invoices issued on latest order date per customer
SELECT i.InvoiceID, i.TotalAmount FROM Invoice i JOIN Orders o ON i.OrderID = o.OrderID WHERE o.OrderDate = (SELECT MAX(o2.OrderDate) FROM Orders o2 WHERE o2.CustomerID = o.CustomerID);

Q28. Invoices including all products of CategoryID=1
SELECT i.InvoiceID FROM Invoice i WHERE NOT EXISTS (SELECT p.ProductID FROM Product p WHERE p.CategoryID = 1 AND NOT EXISTS (SELECT 1 FROM InvoiceDetails d WHERE d.InvoiceID = i.InvoiceID AND d.ProductID = p.ProductID));

Q29. Invoices higher than all invoices of Customer X
SELECT InvoiceID, TotalAmount FROM Invoice WHERE TotalAmount > ALL (SELECT i.TotalAmount FROM Customer c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN Invoice i ON o.OrderID = i.OrderID WHERE c.CustomerName = 'Customer X');

Q30. Invoices with max total for 'Online' orders
SELECT i.InvoiceID, i.TotalAmount FROM Invoice i JOIN Orders o ON i.OrderID = o.OrderID WHERE i.TotalAmount = (SELECT MAX(i2.TotalAmount) FROM Invoice i2 JOIN Orders o2 ON i2.OrderID = o2.OrderID JOIN OrderType t ON o2.OrderTypeID = t.OrderTypeID WHERE t.TypeName = 'Online');

Trust Coding : Programming

30 SQL Subquery & Join Questions with Explanations and Solutions