Furthermore, here is how I completed labs four and five using Set Operators and Functions to Aggregate Data:
Lab 4
1. Write a query that retrieves the company name, first line of the street address, city, and a column named AddressType with the value ‘Billing’ for customers where the address type in the SalesLT.CustomerAddress table is ‘Main Office’. Make sure to use the aliases provided, and default column names elsewhere.
SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Main Office';
- Both join statements here are inner joins. The first finds the similarities in the CustomerID column in both Customer and CustomerAddress tables. The second join only finds the similarities in the AddressID column in the CustomerAddress and Address tables.
2. Adapt the query to retrieve the company name, first line of the street address, city, and a column named AddressType with the value ‘Shipping’ for customers where the address type in the SalesLT.CustomerAddress table is ‘Shipping’. Make sure to use the aliases provided, and default column names elsewhere.
SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping';
3. Use UNION ALL to combine the results returned by the two queries to create a list of all customer addresses that is sorted by company name and then address type.
SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Main Office'
UNION ALL
SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping'
ORDER BY c.CompanyName, AddressType;
- Basic Steps: write out the first query, UNION ALL, write out the second query and use ORDER BY to determine how it is sorted
- UNION vs. UNION ALL: Use UNION when you do not want to see distinct rows, UNION ALL is faster and used when you dont need to filter through duplicate rows.
4. Write a query that returns the company name of each company that appears in a table of customers with a ‘Main Office’ address, but not in a table of customers with a ‘Shipping’ address.
SELECT c.CompanyName
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Main Office'
EXCEPT
SELECT c.CompanyName
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping'
ORDER BY c.CompanyName;
- EXCEPT vs. INTERSECT: EXCEPT returns distinct rows from the left input query that aren’t output by the right input query. INTERSECT returns distinct rows that are output by both the left and right input queries. So EXCEPT looks like a Outer-Left Join, and INTERSECT looks like an Inner-Join.
- Rules: Data types must be compatible and the number and order of columns must be the same for both tables
5. Write a query that returns the company name of each company that appears in a table of customers with a ‘Main Office’ address, and also in a table of customers with a ‘Shipping’ address. Make sure to use the aliases provided, and default column names elsewhere.
SELECT c.CompanyName
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Main Office'
INTERSECT
SELECT c.CompanyName
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping'
ORDER BY c.CompanyName;
lab 5
1. Write a query to return the product ID of each product, together with the product name formatted as upper case and a column named ApproxWeight with the weight of each product rounded to the nearest whole unit. Make sure to use the aliases provided, and default column names elsewhere.
SELECT ProductID,
UPPER(Name) AS ProductName,
ROUND(Weight, 0) AS ApproxWeight
FROM SalesLT.Product;
- ROUND(numeric_expression, length): There are many ways to use the ROUND function. When length(l) = 0, it rounds to the nearest whole number, when l = -1, it rounds from the 10th position, l = -2 rounds from the 100th position and so on. When l = 2(+), it considers the tenth position after the decimal [eg. ROUND(13.885, 2) = 13.89]
2. Extend your query to include columns named SellStartYear and SellStartMonth containing the year and month in which AdventureWorks started selling each product. The month should be displayed as the month name (e.g. ‘January’). Make sure to use the aliases provided, and default column names elsewhere.
SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight,
YEAR(SellStartDate) as SellStartYear, DATENAME(m, SellStartDate) as SellStartMonth
FROM SalesLT.Product;
- DATENAME(datepart_abbreviation, date)
3. Extend your query to include a column named ProductTypethat contains the leftmost two characters from the product number. Make sure to use the aliases provided, and default column names elsewhere.
SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight,
YEAR(SellStartDate) as SellStartYear, DATENAME(m, SellStartDate) as SellStartMonth,
LEFT(ProductNumber, 2) AS ProductType
FROM SalesLT.Product;
- LEFT, RIGHT are used to create abbreviations. An example of using this could be abbreviating states, Washington -> WA, or when generating unique identifiable usernames like June Caden 1994 -> JC94
4. Extend your query to filter the product returned so that only products with a numeric size are included. Make sure to use the aliases provided, and default column names elsewhere.
SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight,
YEAR(SellStartDate) as SellStartYear, DATENAME(m, SellStartDate) as SellStartMonth,
LEFT(ProductNumber, 2) AS ProductType
FROM SalesLT.Product
WHERE ISNUMERIC(Size) = 1;
5. Write a query that returns a list of company names with a ranking of their place in a list of highest TotalDue values from the SalesOrderHeader table. Make sure to use the aliases provided, and default column names elsewhere.
SELECT CompanyName,
TotalDue AS Revenue,
RANK() OVER (ORDER BY TotalDue DESC) AS RankByRevenue
FROM SalesLT.SalesOrderHeader AS SOH
JOIN SalesLT.Customer AS C
ON SOH.CustomerID=C.CustomerID;
- RANK vs. ROWNUM: Rank allows equal values to be ranked the same, whereas ROWNUM gives unique identifiers to all values
6. Write a query to retrieve a list of the product names and the total revenue calculated as the sum of the LineTotal from the SalesLT.SalesOrderDetail table, with the results sorted in descending order of total revenue. Make sure to use the aliases provided, and default column names elsewhere.
SELECT Name,SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
GROUP BY P.Name
ORDER BY TotalRevenue DESC;
7. Modify the previous query to include sales totals for products that have a list price of more than 1000. Make sure to use the aliases provided, and default column names elsewhere.
SELECT Name, SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P ON SOD.ProductID = P.ProductID
WHERE P.LISTPRICE > 1000
GROUP BY P.Name
ORDER BY TotalRevenue DESC;
8. Modify the previous query to only include products with total sales greater than 20000. Make sure to use the aliases provided, and default column names elsewhere.
SELECT Name, SUM(LineTotal) AS TotalRevenue
FROM SalesLT.SalesOrderDetail AS SOD
JOIN SalesLT.Product AS P ON SOD.ProductID = P.ProductID
WHERE P.ListPrice > 1000
GROUP BY P.Name
HAVING SUM(LineTotal) > 20000
ORDER BY TotalRevenue DESC;
June Cadenhead 