. -- Question 3 (3 Pfiints) f* Using the content of...

Image transcription text

-- Question 3 (3 Pfiints) f* Using the content of AdventureworksZBBSRZ, write a query to retrieve the dates in which there was at least one product sold but no product in red was sold. Return the "date" and "total product quantity sold For the date" coluuns. The order quantity can be found in SalesUrderDetail. Sort the returned data by the "total product quantity sold for the date" column in desc. *{ -- Question 4 (3 points) !* Using the content of AdventureworksZEBSRZ, write a query to retrieve the least popular product color For each year. The least popular product color has the lowest total sold quantity of the products in that color. Use Ordeery in SalesUrderDetail to calculate the total sold Quantity. Exclude the products which don't have a specified color. Include the Year, product color and total sold Quantity of the least popular product color in the returned data. If there is a tie, it needs to be retrieved. Sort the returned data by the year. *f

... Show more

Image transcription text

Your NUID: Your Name: Question 1 (6 points) The Wang Medical Research Institute conducts medical research. Please design a relational database to support its recruiting operations based on the business requirements listed below. Normalize your design to the 3rd Normal Form. 1) Applicants need to be tracked. 2) An applicant's skills must be tracked. Skills are all coded. A skill has a standard code, but details about it is also stored in the database. 3) There are many interviewers available for screening applicants. 4) An applicant's interview history must be tracked. To be more specific, management wants to know what interviewer(s) have met with an applicant. 5) If an applicant has passed the screening, an offer will be extended to the applicant. The offer must be tracked. 6) There are some standard offer packages. A successful applicant will be extended one of the packages. Create the ERD in your design tool. Submit the ERD.

... Show more

Image transcription text

—— Question 3 {4 points) f* Using Adventurework52668R2, write a query to retrieve a product's overall sold Quantity and highest annual sold quantity. Use the order quantity column in SalesDrderDetail for calculating total sold quantity. Include the "Product ID", "Name", "A product's overall sold quantity" and "Highest annual sold Quantity" columns in the returned data. Sort the returned data by a product's total sold quantity in descending. Exclude products that have been included in more than 5 small orders. A small order has a value less than $166. *I __ Question 4 {3 points) /* Using the content of Adventurework52668R2, write a query to retrieve the product which had the largest total—quantity-sold decrease from July 266? to August 266?. Include the product id and decrease columns in the returned data. If there is a tie, it must be picked up. *1

... Show more