Question

The purpose of this assignment is to design and implement queries that will assist in developing answers to business problems. For this assignment, continue to operate in the role of a data analyst at Adventure Works Cycles company. Based upon "LL Road Frame-Black 60" scenario and questions formulated for the Topic 3 assignment, write queries for the "AdventureWorks 2016" database using SQL Server 2016 Developer Edition. Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy. Create a Word document that includes the SQL queries used to explore the database tables, and answer the following questions.
Find the product ID for the LL Road Frame – Black 60.

Find the listing price of the LL Road Frame – Black 60.

How would you rewrite the query used in question 2 to exclude NULL values?

How many orders have been placed for LL Road Frame – Black 60?

Rename the OrderQty to Quantity in your results.

Answer 1

Answer:

Below are the SQL queries on AdventureWorks database-

1) Production.Product table is used to get ProductID of LL Road Frame – Black 60.

SELECT pp.ProductID

FROM Production.Product AS pp

WHERE pp.Name = 'LL Road Frame - Black 60';

2) ListPrice from table Production.Product table is listed here.

SELECT pp.ListPrice

FROM Production.Product AS pp

WHERE pp.Name = 'LL Road Frame - Black 60';

3) Same as above except one condition is added to WHERE clause to filter those listPrice without null values.

SELECT pp.ListPrice

FROM Production.Product AS pp

WHERE pp.Name = 'LL Road Frame - Black 60'

AND pp.ListPrice IS NOT NULL;

4) Aggregate function COUNT is used here to get the total order quantity of product LL Road Frame – Black 60.

SELECT COUNT(od.SalesOrderDetailID) AS OrderQty

FROM Production.Product AS pp

INNER JOIN Sales.SalesOrderDetail AS od

ON od.ProductID = pp.ProductID

WHERE pp.Name = 'LL Road Frame - Black 60';