The database designer included columns for Year and Month in the Sales_Totals table, but forgot to include a column for Quarter.  Write a CASE expression which can be used to return the quarter number (1, 2, 3, or 4) using other column values from the table.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

Demonstrate Your Knowledge of SQL

Prompt 1 Tables

A community college uses the following tables to track each student’s progress:

                     Class

class_id (p) class_name
101 Geometry
102 English
103 Physics

 

                          Student

student_id (p) first_name last_name
500 Robert Smith
762 Frank Carter
881 Joseph Evans
933 Anne Baker

 

                                   Enrollment

class_id (p)(f) student_id (p)(f) semester (p) grade
101 500 Fall 2019 A
102 500 Fall 2019 B
103 762 Fall 2019 F
101 881 Spring 2020 B
102 881 Fall 2020 B
103 762 Spring 2021

 

Prompt 1 Questions

Answer the following questions by constructing a single query without using subqueries, unless otherwise instructed.

1. Write a query to retrieve all columns from the Enrollment table where the grade of A or B were assigned.

2. Write a query to return the first and last names of each student who has taken Geometry.

3. Write a query to return all rows from the Enrollment table where the student has not been given a failing grade (F).  Include any rows where the grade has not yet been assigned.

4. Write a query to return the first and last name of every student, along with the grade received in English if the student has ever enrolled in that class.  You need only include the Enrollment and Student tables, and may specify the class_id value of 102 for the English class.

5. Write a query to return the total number of students who have ever been enrolled in each of the classes.

6. Write a statement to modify Robert Smith’s grade for the English class from a B to a B+.  Specify the student by his student ID, which is 500, and the English class by class ID 102.

7. Create an alternate statement to modify Robert Smith’s grade in English, but for this version specify the student by first/last name, not by student ID.  This will require the use of a subquery.

8. A new student name Michael Cronin enrolls in the Geometry class.  Construct a statement to add the new student to the Student table (you can pick any value for the student_id, as long as it doesn’t already exist in the table).

9. Add Michael Cronin’s enrollment in the Geometry class to the Enrollment table.  You may only specify names (e.g. “Michael”, “Cronin”, “Geometry”) and not numbers (e.g. student_id, class_num) in your statement.  You may use subqueries if desired, but the statement can also be written without the use of subqueries. Use ‘Spring 2020’ for the semester value.

10. Write a query to return the first and last name of all students who have not enrolled in any class.  Use a correlated subquery against the Enrollment table.

11. Return the same results as the previous question (first and last name of all students who have not enrolled in any class), but formulate your query using a non-correlated subquery against the Enrollment table.

12. Write a statement to remove any rows from the Student table where the person has not enrolled in any classes.  You may use either a correlated or non-correlated subquery against the Enrollment table.

 

Prompt 2 Tables

The Customer_Order table, which stores data about customer orders, contains the following data:

 

                  Customer_Order

order_num cust_id order_date
1 121 01-15-2019
2 234 07-24-2019
3 336 05-02-2020
4 121 01-15-2019
5 336 03-19-2020
6 234 07-24-2019
7 121 01-15-2019
8 336 06-12-2020

 

Prompt 2 Questions

1. Write a query to retrieve each unique customer ID (cust_id) from the Customer_Order table.  There are multiple ways to construct the query, but do not use a subquery.

2. Write a query to retrieve each unique customer ID (cust_id) along with the latest order date for each customer.  Do not use a subquery.

3. Write a query to retrieve all rows and columns from the Customer_Order table, with the results sorted by order date descending (latest date first) and then by customer ID.

4. Write a query to retrieve each unique customer (cust_id) whose lowest order number (order_num) is at least 3.  Do not use a subquery.

5. Write a query to retrieve only those customers who had 2 or more orders on the same day.  Retrieve the cust_id and order_date values, along with the total number of orders on that date.  Do not use a subquery.

6. Along with the Customer_Order table, there is another Customer table below. Write a query which returns the name of each customer who has placed exactly 3 orders.  Do not return the same customer name more than once, and use a correlated subquery against Customer_Order to determine the total number of orders for each customer:

                            Customer

cust_id cust_name
121 Acme Wholesalers
234 Griffin Electric
336 East Coast Marine Supplies
544 Sanford Automotive

 

7. Construct a different query to return the same data as the previous question (name of each customer who has placed exactly 3 orders), but use a non-correlated subquery against the Customer_Order table.

7. Write a query to return the name of each customer, along with the total number of orders for each customer.  Include all customers, regardless of whether or not they have orders. Use a scalar, correlated subquery to generate the number of orders.

Demonstrate Your Knowledge of Advanced SQL

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

                   

 

 

 

 

 

 

 

 

 

 

 

 

 

                    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. The database designer included columns for Year and Month in the Sales_Totals table, but forgot to include a column for Quarter.  Write a CASE expression which can be used to return the quarter number (1, 2, 3, or 4) using other column values from the table.

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_gears tot_sales_small_gears tot_sales_crankshafts tot_sales_sprockets
6200 5450 0 3050

 

3. Write a query which 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 which 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.  The statements should be executed as a single unit of work.

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).

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

HelpHub
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Choose HelpHub

HelpHub

Quality Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

HelpHub

Qualified Writers

We have hired a team of professional writers experienced in academic and business writing. Most of them are native speakers and PhD holders able to take care of any assignment you need help with.

StudyAcer

Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account.

StudyAcer

On Time Delivery

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. We will always strive to deliver on time.

StudyAcer

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text.

StudyAcer

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Help Hub

Place your order

Fill in the order form and provide all details of your assignment.

Help Hub

Proceed with the payment

Choose the payment system that suits you most.

Help Hub

Receive the final file

Once your paper is ready, we will email it to you.

HelpHub Writing Services

No need to work on essay at night. Sleep tight, we will cover your back. We offer all kinds of essay writing services.

HelpHub HelpHub

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

HelpHub HelpHub

Admissions

Admission Essays

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

HelpHub HelpHub

Editing

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

HelpHub HelpHub

Revision

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied.