Complex Queries Use the Unit 4 Assignment Template, linked in the Resources, to document and submit your assignment. Your completed assignment will be evaluated according to the following criteria:...

1 answer below »

Complex Queries


Use the Unit 4 Assignment Template, linked in the Resources, to document and submit your assignment. Your completed assignment will be evaluated according to the following criteria:



  • Design complex queries that meet the organization's needs.

  • Design complex queries that join multiple tables optimally.

  • Design complex queries that use subqueries effectively.

  • Design complex queries that use group functions optimally.

  • Use subqueries and set operators to control database access.

  • Demonstrate an ability to use scripts in SQL statements and generate successful results.


Assignment Overview


For this assignment, you will design and develop complex queries that join multiple tables and use group functions. Continue to use the database you created in Unit 2. Use external research and the suggested resources to design your infrastructure.


Expanding on the reports from Unit 3, there are a few more reports that require joins and group functions. Given the greater complexity, you need to add at least 10 more orders with 1–3 products for each to help with testing purposes. These queries will be used for reports directed at upper management.


In preparation for this assignment, it is important to understand the fundamentals of SQL joins. You should also be familiar with SQL data retrieval constructs and statements to support the creation of complex nested queries. The following resources (linked in the Resources) will help you with this assignment:



  • Samson, A. (2018).Aggregating data in SQL Server 2016[Tutorial]. Skillsoft Ireland.

    • 2 hours, 20 minutes.



  • Microsoft. (2018). Subqueries (SQL Server). Retrieved from https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-2017


Assignment Instructions


For this assignment, complete the following queries:



  1. A list of customer names with the number of orders that have been placed.

  2. A list of all customers that have ordered a product within the last 30 days.

  3. A list of customer name, order date, and product name ordered by the customer name.

  4. A list of product name and the category name that they belong to.

  5. A list of each product and the total amount that they have sold for along with their average number of units sold. Include products that have been sold.

  6. A list of states with the number of units that have been sold to that state.

  7. A list of month, year, and average order total for each month.

  8. A list of mailing addresses that have at least five orders.

  9. A list of the most popular products sold (consider quantity) with the number of items they have sold.

  10. A list of product names that belongs to the same category as the most popular product (use a subquery).


Provide screenshots of your reports using the template format.


Refer to the Complex Queries scoring guide to ensure that you meet the grading criteria for this assignment.


Submityour assignment by 11:59 p.m. Sunday this week.

Answered 3 days AfterMay 02, 2022

Answer To: Complex Queries Use the Unit 4 Assignment Template, linked in the Resources, to document and submit...

Shweta answered on May 06 2022
90 Votes
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here