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;

Products with price lower than average

SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice <
   (SELECT AVG(UnitPrice) From Products)
ORDER BY UnitPrice DESC;

Products with price within 10% of average:

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;