Question 1
After familiarizing myself with the Adventure Works Schema, I located a table with Product IDs and names. I used the query below to find the product name “LL Road Frame-Black 60” with a wildcard since that was not the exact name. The product ID for the LL Road Frame-Black 60 is 723.
SELECT ProductID, Name FROM Production.Product where Name LIKE 'LL%60' ORDER BY Name;
Question 2
Further investigation of the schema, I found a table called the Product List Price History that included the list price history of the LL Road Frame-Black 60. On May 29, 2012 the ending list price was 297.6346, on May 29, 2013 the ending list price was 306.5636, and now it is 337.22 with no end date. SELECT ProductID, EndDate, ListPrice FROM Production.ProductListPriceHistory where ProductID = '723';
Question 3
Since the last query included Null values, the request is to have these values removed. Below is the amended query to remove the Null values. SELECT ProductID, EndDate, ListPrice FROM Production.ProductListPriceHistory where ProductID = '723' AND EndDate IS NOT NULL AND ListPrice IS NOT NULL;
Question 4
Searching through the schema diagram, there are 2 paths to find the number of bikes sold, but specifically the question is how many orders were placed. I found that getting the data from the
Sales Order Detail gave the most recent data, but pulling from the Production schema and combining the Transaction History and Transaction History Archive tables gave a different number of LL Road Frame-Black 60 products sold. The production query from the Transaction History table showed 27 sold out of 9 transactions, while the Archive table had 102 transactions
out of 43 transactions. The sales Order Detail table showed 52 transactions with 129 total bikes sold. Select * FROM Production.TransactionHistory WHERE ProductID = '723' AND TransactionType = 'S'
UNION ALL
