Assume that you do not have access to the data. Hence, pls create a schema based on the representation below to work on the case study.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

SQL CASE STUDY- ADVANCE

 

Business Scenario: The database “Cellphones Information” contains details on cell phone sales or transactions.

Detailes stored are: Dim_manufacturer, Dim_model, Dim_customer, Dim_Location and Fact_Transactions.

The first four store entries for the respective elements and Fact_Transactions stores all the information about sales of specific cellphones.

Data Availability: Assume that you do not have access to the data. Hence, pls create a schema based on the representation below to work on the case study.

 

Questions:

Write queries to find out the following:

1. List all the states in which we have customers who have bought cellphones from 2005 till
2. What state in the US is buying more 'Samsung' cell phones?
3. Show the number of transactions for each model per zip code per
4. Show the cheapest cellphone
5. Find out the average price for each model in the top5 manufacturers in terms of sales quantity and order by average
6. List the names of the customers and the average amount spent in 2009, where the average is higher than 500
7. List if there is any model that was in the top 5 in terms of quantity, simultaneously in 2008, 2009 and 2010
8. Show the manufacturer with the 2nd top sales in the year of 2009 and the manufacturer with the 2nd top sales in the year of 2010
9. Show the manufacturers that sold cellphone in 2010 but didn’t in
10. Find top 100 customers and their average spend, average quantity by each Also find the percentage of change in their spend.

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