Write a SELECT statement that displays each category of products and the surrounding data for each category.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

 Database Design and Modeling Assignment 2 Questions

Be sure to complete both questions.

DO NOT USE THESE STRUCTURE:

 category_id = x, where x is a PK value from the category table.

 Hard coding calculated values such as sums and averages. Instead, use appropriate functions and a “dynamic calculation” approach to calculate these values.

 

Make sure to run the create_my_guitar_shop.sql script to create the my_guitar_shop database.

Q1.) (50 points) Write a SELECT statement that displays each category of products and the surrounding data for each category. Include a final row which gives the subtotals of each row (i.e. number purchases of all categories, discount of all categories, etc.). Replicate the result set below, including results, sort, column headers, and all formatting aspects:

(5 rows returned)

 Q2.) (50 points) Replicate the sample output below which provides basic information about the guitar shop’s customers. For the destination, assume this is based on their shipping address. You must accomplish this with either a subquery or CTE structure, it is your choice.

(+2 points bonus): Provide a short explanation of why you used either option (+2 points bonus). Neither answer is more correct, we would just like you to explain your choice.

Your result set should look like this, including results, sort, column headers, and all formatting aspects:

(12 rows returned)

 

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of