* BusIT 103

Assignment #6 DUE DATE : To do from Microsoft SQL server management studio.

You are to develop SQL statements for each task listed. You should type your SQL statements under each task.

You are required to use INNER JOINs to solve each problem. We are not using cross joins with WHERE clauses. -- To make the joins in the first 4 question more understandable, create a database diagram with the following tables: --Production.Product --Production.ProductReview --Production.ProductModel --Production.ProductSubcategory --Production.ProductCategory

USE AdventureWorks2012;

Question 1:

.a.) List any products that have product reviews. Show product ID, product name, and comments. -- Hint: Use the Production.Product and Production.ProductReview tables. (1 points) -- 4 Rows b). Modify 1.a.) to list product reviews for Product ID 798. Show product ID, product name, and comments. (6 points) -- 1 Rows

Question 2: .a.) List products with product model numbers. Show Product ID, product name,standard cost, model ID number, and model name. Order by model ID. (1points) -- Hint: Look for a table that contains "model" in its name. -- 295 Rows

b.) Modify 2.a.) to list products whose product model is 3 (Full-Finger Gloves). Show Product ID , product name, standard cost, ID number, and model name and order by model ID. (6points) -- 3 Rows

Question 3: a.) List Products, their subcategories and their categories. Show the category name, subcategory name, product ID, and product name, in this order. Sort in alphabetical order on category name, then subcategory name, and then by product name. (1 points) -- 295 Rows -- Hint: To understand the relationshships, refer to your database diagram and the following tables: -- Production.Product -- Production.ProductSubCategory -- Production.ProductCategory

b). Modify 3.a.) to list Products in category 1. Show the category name, subcategory name, product ID, and product name, in this order. Sort in alphabetical order on category name, then subcategory name, and then by product name. (6 points) -- Hint: Add the product category id field to the results set to check your results and then remove it or comment it out. -- 97 Rows

Question 4: a). List Products, their subcategories, their categories, and their model. Show the model name, category name, subcategory name, product ID, and product name in this order. Sort in alphabetical order by model name. (1 points) -- 295 Rows -- Hint: To understand the relationshships, refer to your database diagram and the following tables: -- Production.Product -- Production.ProductSubCategory -- Production.ProductCategory -- Production.ProductModel -- Choose a path from one table to the next and follow it in a logical order

b.) Modify 4.a.) to list those products in model ID 5 with silver in the product name. Change the sort to sort only on Product ID. Hint: Add the product model id field to the results set to check your results and then remove it or comment it out. (6 points) ---5 Rows

Question 5: List sales for customer id 18759. Show product names, sales order id, and OrderQty. (5 points) -- 8 Rows -- Hint: First create a database diagram with the following tables: -- Production.Product -- Sales.SalesOrderHeader -- Sales.SalesOrderDetail

Question 6: List all sales for Bikes that were ordered during 2008. Show product ID, product name, and LineTotal for each line item sale. Show the list in order alphabetically by product name. (7 points) -- Hint: Use the diagram you created in #5 -- 11378 Rows.

Question 7: In your own words, write a business question for AdventureWorks that you will try to answer with a SQL query. Then try to develop the SQL to answer the question. (10 points) -- You may find that the AdventureWorks database structure is highly normalized and therefore, difficult -- to work with. As a result, you may not run into difficulties when developing your SQL. For this task -- that is fine. Just show your question and as much SQL as you were able to figure out.