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
(
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;
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;