Project #31833 - IT (SQL Database)

 

For each query, give the problem number, copy the query from the query window into your Word Assignment document, then copy the results with headers into your Word document. If the result set has more than 10 rows, copy the first five rows with headers and state how many rows were returned.

 

1. Query the OrderDetails table to answer the following question: For each product, how many orders are there?

 

2a. Query the OrderDetails table to answer the following question: For each order, how many different products were ordered?

 

2b. Which orders had more than one product ordered?

 

3. Write the T-SQL code that will create a table that will track horses. The table will be in database Farm, in schema Animal. The desired attributes are HorseID (an auto-generated integer), Name, Birthdate, Height, and Weight. The Height and Weight are for an adult horse; they might not be known for a young horse. Height will always be in inches and Weight will always be in pounds, so the data will be numbers. [No result set is required.] (10 pts.)

 

4. Write the T-SQL code that will add a column to the table in query 3 to store the name of the horse's sire. [No result set is required.]

 

5a. Join Orders to OrderDetails to return orderid and the total amount charged for each product. TotalAmount is unit price multiplied by qty multiplied by (1.-discount). TotalAmount should be cast as decimal(10,2). Do not perform any grouping.

 

5b. Modify the query in 5a to calculate, for each order, the total amount charged for the entire order. Result set columns should be titled OrderID and TotalAmount. Hint: Use SUM and GROUP BY.

 

5c. Join Employees, Orders, and OrderDetails to return, for each employee, the total amount of sales. Return employee lastname, firstname, and total sales in descending order of total sales. Result set columns should be titled LastName, FirstName, and TotalSales.

 

Subject Computer
Due By (Pacific Time) 05/27/2014 05:00 pm
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy