Notes on Using Table Expressions
Querying Views:
- A presentation of the data which provides abstraction and acts as a layer of security over the database. Named queries with definitions stored in a database.
Creating a View:
CREATE VIEW SalesLT.vCustomerAddress
AS
SELECT c.CustomerID, FirstName, LastName, AddressLine1, City, StateProvince
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;
Querying the View:
SELECT CustomerID, City
FROM SalesLT.vCustomerAddress
Joining the View to the Table:
SELESCT c.StateProvince, c.city, ISNULL(SUM(s.TotalDue), 0.00) AS Revenus)
FROM SalesLT.vCustomerAddress AS c
LEFT JOIN SalesLT.SalesOrderHeader AS s
ON s.CustomerID = c.CustomerID
GROUP BY c.StateProvince, c.City
ORDER BY c.StateProvince, Revenue DESC;
*From lecture slides*
Lab 6
1. AdventureWorks products each have a standard cost that indicates the cost of manufacturing the product, and a list price that indicates the recommended selling price for the product. This data is stored in the SalesLT.Product table.
Whenever a product is ordered, the actual unit price at which it was sold is also recorded in the SalesLT.SalesOrderDetail table.
Use subqueries to compare the cost and list prices for each product with the unit prices charged in each sale.
SELECT ProductID, Name, ListPrice from SalesLT.Product
WHERE ListPrice >
(SELECT AVG(UnitPrice) FROM SalesLT.SalesOrderDetail)
ORDER BY ProductID;
2. Retrieve the product ID, name, and list price for each product where the list price is 100 or more, and the product has been sold for (strictly) less than 100.
SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ProductID IN
(SELECT ProductID FROM SalesLT.SalesOrderDetail
WHERE UnitPrice < 100) AND ListPrice >= 100
ORDER BY ProductID;
3. Retrieve the product ID, name, cost, and list price for each product along with the average unit price for which that product has been sold. Make sure to use the aliases provided, and default column names elsewhere.
SELECT ProductID, Name, StandardCost, ListPrice,
(SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail AS SOD
WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
FROM SalesLT.Product AS P
ORDER BY P.ProductID;
4. Filter the query for the previous exercise to include only products where the cost is higher than the average selling price. Make sure to use the aliases provided, and default column names elsewhere.
SELECT ProductID, Name, StandardCost, ListPrice,
(SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail AS SOD
WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
FROM SalesLT.Product AS P
WHERE StandardCost >
(SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail AS SOD
WHERE P.ProductID = SOD.ProductID)
ORDER BY P.ProductID;
5. Retrieve the sales order ID, customer ID, first name, last name, and total due for all sales orders from the SalesLT.SalesOrderHeader table and the dbo.ufnGetCustomerInformation function. Make sure to use the aliases provided, and default column names elsewhere.
SELECT SOH.SalesOrderID, SOH.CustomerID, CI.FirstName, CI.LastName, SOH.TotalDue
FROM SalesLT.SalesOrderHeader AS SOH
CROSS APPLY dbo.ufnGetCustomerInformation(SOH.CustomerID) AS CI
ORDER BY SOH.SalesOrderID;
6. Retrieve the customer ID, first name, last name, address line 1 and city for all customers from the SalesLT.Address and SalesLT.CustomerAddress tables, and the dbo.ufnGetCustomerInformation function. Make sure to use the aliases provided, and default column names elsewhere.
SELECT CA.CustomerID, CI.FirstName, CI.LastName, A.AddressLine1, A.City
FROM SalesLT.Address AS A
JOIN SalesLT.CustomerAddress AS CA
ON A.AddressID = CA.AddressID
CROSS APPLY dbo.ufnGetCustomerInformation(CA.CustomerID) AS CI
ORDER BY CA.CustomerID;
Lab 7
1. Retrieve the product ID, product name, product model name, and product model summary for each product from the SalesLT.Product table and the SalesLT.vProductModelCatalogDescription view. Make sure to use the aliases provided, and default column names elsewhere.
SELECT P.ProductID, P.Name AS ProductName, PM.Name AS ProductModel, PM.Summary
FROM SalesLT.Product AS P
JOIN SalesLT.vProductModelCatalogDescription AS PM
ON P.ProductModelID = PM.ProductModelID
ORDER BY ProductID;
2. Create a table variable and populate it with a list of distinct colors from the SalesLT.Product table. Then use the table variable to filter a query that returns the product ID, name, and color from the SalesLT.Product table so that only products with a color listed in the table variable are returned. You’ll need to use NVARCHAR in your solution and make sure to use the aliases provided.
DECLARE @Colors AS TABLE (Color nvarchar(15));
INSERT INTO @Colors
SELECT DISTINCT Color FROM SalesLT.Product;
SELECT ProductID, Name, Color
FROM SalesLT.Product
WHERE Color IN (SELECT Color FROM @Colors);
Here’s a quick run-down on how to create Variables:
- There are two types of Variables: Scalar and Table. Scalar stores scalar values (1 value per variable) and Table stores… tables!
- To DECLARE a (Colors) variable, define it with @(variable_name), to create the table: AS TABLE(variable_name(number_of_characters))
- Table Variables:
- # Temporary Table (limited to session), automatically deleted when the session they are in ends
- ## Global temporary tables end when the last user referencing them is closed
CREATE TABLE #Colors (Color varchar(15));
INSERT INTO #Colors
SELECT DISTINCT Color FROM SalesLT.Product;
SELECT * FROM #Colors- @ Table Variable( needs to all run within one batch statement)
DECLARE @Clors AS TABLE (Color varchar(15));
INSERT INTO @Colors
SELECT DISTINCT Color FROM SalesLT.Product;
SELECT * FROM @Colors;
3. The AdventureWorksLT database includes a table-valued function named dbo.ufnGetAllCategories, which returns a table of product categories (e.g. ‘Road Bikes’) and parent categories (for example ‘Bikes’).
Write a query that uses this function to return a list of all products including their parent category and their own category. Make sure to use the aliases provided, and default column names elsewhere.
SELECT C.ParentProductCategoryName AS ParentCategory,
C.ProductCategoryName AS Category,
P.ProductID, P.Name AS ProductName
FROM SalesLT.Product AS P
JOIN dbo.ufnGetAllCategories() AS C
ON P.ProductCategoryID = C.ProductCategoryID
ORDER BY ParentCategory, Category, ProductName;
4. Retrieve a list of customers in the format Company (Contact Name) together with the total revenue for each customer. Use a derived table or a common table expression to retrieve the details for each sales order, and then query the derived table or CTE to aggregate and group the data. Make sure to use the aliases provided, and default column names elsewhere.
SELECT CompanyContact, SUM(SalesAmount) AS RevenueSELECT CompanyContact, SUM(SalesAmount) AS RevenueFROM (SELECT CONCAT(c.CompanyName, CONCAT(' (' + c.FirstName + ' ', c.LastName + ')')), SOH.TotalDue FROM SalesLT.SalesOrderHeader AS SOH JOIN SalesLT.Customer AS c ON SOH.CustomerID = c.CustomerID) AS CustomerSales(CompanyContact, SalesAmount)GROUP BY CompanyContactORDER BY CompanyContact;
June Cadenhead 