Assignment: 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.
8. 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.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.