This assignment will test your understanding of conditional logic, views, ranking and windowing functions, and transactions, as shown in the videos. The prompt includes 10 questions. You will need to

[ad_1]

This assignment will test your understanding of conditional logic, views, ranking and windowing functions, and transactions, as shown in the videos. The prompt includes 10 questions.

Don't use plagiarized sources. Get Your Custom Essay on
This assignment will test your understanding of conditional logic, views, ranking and windowing functions, and transactions, as shown in the videos. The prompt includes 10 questions. You will need to
Just from $13/Page
Order Essay

You will need to create your own test database and tables using the criteria below. Please submit your answers using only one file. The preferable format is a text file with a .sql extension. You can easily edit the file using a text editor such as Notepad , which is available online for free.

Prompt: A manufacturing company’s data warehouse contains the following tables.

Region

region_id (p)

region_name

super_region_id (f)

101

North America

102

USA

101

103

Canada

101

104

USA-Northeast

102

105

USA-Southeast

102

106

USA-West

102

107

Mexico

101

Note: (p) = “primary key” and (f) = “foreign key”. They are not part of the column names.

Product

product_id (p)

product_name

1256

Gear – Large

4437

Gear – Small

5567

Crankshaft

7684

Sprocket

Sales_Totals

product_id (p)(f)

region_id (p)(f)

year (p)

month (p)

sales

1256

104

2020

1

1000

4437

105

2020

2

1200

7684

106

2020

3

800

1256

103

2020

4

2200

4437

107

2020

5

1700

7684

104

2020

6

750

1256

104

2020

7

1100

4437

105

2020

8

1050

7684

106

2020

9

600

1256

103

2020

10

1900

4437

107

2020

11

1500

7684

104

2020

12

900

Answer the following questions using the above tables/data:

  1. Write a CASE expression that can be used to return the quarter number (1, 2, 3, or 4) only based on the month.
  2. Write a query which will pivot the Sales_Totals data so that there is a column for each of the 4 products containing the total sales across all months of 2020. It is OK to include the product_id values in your query, and the results should look as follows:

tot_sales_large_gearstot_sales_small_gearstot_sales_crankshaftstot_sales_sprockets6200545003050

3. Write a query that retrieves all columns from the Sales_Totals table, along with a column called sales_rank which assigns a ranking to each row based on the value of the Sales column in descending order.

4. Write a query that retrieves all columns from the Sales_Totals table, along with a column called product_sales_rank which assigns a ranking to each row based on the value of the Sales column in descending order, with a separate set of rankings for each product.

5. Expand on the query from question #4 by adding logic to return only those rows with a product_sales_rank of 1 or 2.

6. Write a set of SQL statements which will add a row to the Region table for Europe, and then add a row to the Sales_Total table for the Europe region and the Sprocket product (product_id = 7684) for October 2020, with a sales total of $1,500. You can assign any value to the region_id column, as long as it is unique to the Region table. The statements should be executed as a single unit of work. Please note that since the statements are executed as a single unit of work, additional code is needed.

7. Write a statement to create a view called Product_Sales_Totals which will group sales data by product and year. Columns should include product_id, year, product_sales, and gear_sales, which will contain the total sales for the “Gear – Large” and “Gear Small” products (should be generated by an expression, and it is OK to use the product_id values in the expression). To accomplish this, you need a CASE statement. The product_sales column should be a sum of sales for the particular product_id and year, regardless of what kind of product it is. The gear_sales column should be a sum of sales only in the case where the product is either “Gear – Large” or “Gear Small”. Else in the case that the product is neither “Gear – Large” or “Gear Small”, the value for gear_sales should be 0.

8. Write a query to return all sales data for 2020, along with a column showing the percentage of sales for each product. Columns should include product_id, region_id, month, sales, and pct_product_sales.

9. Write a query to return the year, month, and sales columns, along with a 4th column named prior_month_sales showing the sales from the prior month. There are only 12 rows in the sales_totals table, one for each month of 2020, so you will not need to group data or filter/partition on region_id or product_id.

10. If the tables used in this prompt are in the ‘sales’ database, write a query to retrieve the name and type of each of the columns in the Product table. Please specify the ‘sales’ database in your answer.

Previous



[ad_2]

Source link

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Order your essay today and save 15% with the discount code BANANA