SQL Part 3 - SQL statements for all exercises Order Details Extended, simplified: SELECT OrderID, od.ProductID, ProductName, od.UnitPrice, Quantity, Discount, od.UnitPrice * Quantity AS NondiscountPrice FROM Products p, [Order Details] od WHERE p.ProductID = od.ProductID ORDER BY OrderID; Category Sales for 1997, first quarter only: SELECT DISTINCTROW [Product Sales for 1997].CategoryName, Sum([Product Sales for 1997].ProductSales) AS CategorySales FROM [Product Sales for 1997] WHERE ShippedQuarter like "*1" GROUP BY [Product Sales for 1997].CategoryName; Category Sales for 1997, all quarters, totals greater than $100000 only: SELECT DISTINCTROW [Product Sales for 1997].CategoryName, Sum([Product Sales for 1997].ProductSales) AS CategorySales FROM [Product Sales for 1997] GROUP BY [Product Sales for 1997].CategoryName HAVING Sum(ProductSales) > 100000; Category Sales for 1997, first quarter only, totals greater than $20000: SELECT DISTINCTROW [Product Sales for 1997].CategoryName, Sum([Product Sales for 1997].ProductSales) AS CategorySales FROM [Product Sales for 1997] WHERE ShippedQuarter like "*1" GROUP BY [Product Sales for 1997].CategoryName HAVING Sum(ProductSales) > 20000; Number of products in the database: SELECT Count(*) AS ProductCount FROM Products; Number of products by category: SELECT CategoryID, Count(*) AS ProductCount FROM Products GROUP BY CategoryID; Average freight by shipper: SELECT ShipVia, Avg(Freight) AS AverageFreight FROM Orders GROUP BY ShipVia; Average freight by shipper looking at freight above $75 only: SELECT ShipVia, Avg(Freight) AS AverageFreight FROM Orders WHERE Freight > 75 GROUP BY ShipVia; Average freight by shipper greater than $75 (for all freight charges): SELECT ShipVia, Avg(Freight) AS AverageFreight FROM Orders GROUP BY ShipVia HAVING Avg(Freight) > 75; Most popular product: SELECT [Order Details].ProductID, Max([Order Details].Quantity) AS PopularityIndex FROM [Order Details] GROUP BY [Order Details].ProductID ORDER BY Max(Quantity) DESC; or perhaps SELECT [Order Details].ProductID, Sum([Order Details].Quantity) AS PopularitySum FROM [Order Details] GROUP BY [Order Details].ProductID ORDER BY Sum(Quantity) DESC; or perhaps (looking for repeated products) SELECT ProductID, Count(ProductID) AS Repeated FROM [Order Details] GROUP BY ProductID HAVING Count(ProductID) > 1 ORDER BY Count(ProductID) DESC; Same query as previous (repeated products) as created by the Find Duplicates wizard (ORDER BY clause added separately): SELECT First([Order Details].ProductID) AS [ProductID Field], Count([Order Details].ProductID) AS NumberOfDups FROM [Order Details] GROUP BY [Order Details].ProductID HAVING (((Count([Order Details].ProductID))>1)) ORDER BY Count(ProductID) DESC; Repeat instances of product and price in Order Details: SELECT ProductID, UnitPrice FROM [Order Details] GROUP BY ProductID, UnitPrice HAVING Count(ProductID) > 1 AND Count(UnitPrice) > 1; Same query as previous (repeat product and price) using the Find Duplicates wizard: SELECT First([Order Details].ProductID) AS [ProductID Field], First([Order Details].UnitPrice) AS [UnitPrice Field], Count([Order Details].ProductID) AS NumberOfDups FROM [Order Details] GROUP BY [Order Details].ProductID, [Order Details].UnitPrice HAVING (((Count([Order Details].ProductID))>1) AND ((Count([Order etails].UnitPrice))>1)); Customers located in the same city as a supplier (subquery): SELECT CompanyName, City, Country FROM Customers WHERE City IN (SELECT City FROM Suppliers); Customers located in the same city as a supplier (join): SELECT Customers.CompanyName, Customers.City, Customers.Country FROM Customers, Suppliers WHERE
Customers.City =Suppliers.City;
SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice Between (SELECT AVG([UnitPrice]) -AVG([UnitPrice])*0.1 From Products) And (SELECT AVG([UnitPrice])+AVG([UnitPrice])*0.1 From Products) ORDER BY UnitPrice DESC; Freight charges greater than average: SELECT ShipVia, Freight FROM Orders WHERE Freight > (SELECT Avg(Freight) FROM Orders); Number of higher-than-average charges by each shipper: SELECT ShipVia, Avg(Freight) AS AvgFreight, count(*) AS Shipments FROM Orders WHERE Freight > (SELECT Avg(Freight) FROM Orders) GROUP BY ShipVia; Suppliers who do not provide dairy products: SELECT CompanyName FROM Suppliers AS s WHERE NOT EXISTS (SELECT * FROM Products p, Categories c WHERE p.SupplierID = s.SupplierID AND p.CategoryID = c.CategoryID AND CategoryName LIKE "*Dairy*"); Customers located in the same city: SELECT DISTINCT c1.ContactName, c1.Address, c1.City FROM Customers AS c1, Customers AS c2 WHERE c1.City = c2.City AND c1.ContactName <> c2.ContactName ORDER BY c1.City, c1.ContactName; |