6. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise6_MEID.sql ....

Transcribed Image Text:

6. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise6_MEID.sql . Save the output of this script as CIS276DA_FinalExercise6_MEID.csv Your results should display one row for each customer that does the following: Display the email address from the Customers table, a count of the number of orders for each customer, and the total amount for each order (HINT: Don't forget about the discounts when calculating the total amount for the order!) o Only show results for customers that have more than 1 order. Sort the results in descending order by the sum of the line item amounts. 7. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise 7 MEID.sql . Save the output of this script as CIS276DA_FinalExercise7_MEID.csv Use an aggregate window function to display the order id from the Order_Items table along with the total amount for each order item in the Order_Items table. (HINT: Don't forget the discount!) Also display the total amount for each order Use an aggregate window function to get the average item amount for each order and display that as a column in the results. Use a named window for the two aggregate functions. Sort the results in ascending order by the order_id column . 8. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise8_MEID.sql. Save the output of this script as CIS276DA_FinalExercise8_MEID.csv Use a correlated subquery to return one row per customer, representing the customer's oldest order (the one with the earliest date). Each row should include these three columns: email address, order_id, and order_date. Sort the results by the order date and order id columns. 9. Write an SQL script that does the following and save this script as CIS276DA_Final Exercise9_MEID.sql . Save the output of this script as CIS276DA_FinalExercise9_MEID.csv Write a SELECT statement that uses the analytic functions to get the highest and lowest sales by product within each category. Return these columns: The category_name column from the Categories table o The product_name column from the Products table A column named total_sales that shows the sum of the sales for each product with sales in the Order_Items table A column named highest_sales that uses the FIRST VALUE function to show the name of the product with the highest sales within each category A column named lowest_sales that uses the LAST_VALUE function to show the name of the product with the lowest sales within each category 10. Write an SQL script that does the following and save this script as CIS276DA_Final Exercise10_MEID.sql . Save the output of this script as CIS276DA_FinalExercise10_MEID.csv add three new columns to the Products table Add one column for the name of the product supplier Add one column for the product markup percentage that provides for three digits to the left of the decimal point and two to the right. This column should have a default value of 50.00 Add one column for the date and time that the product was added to the database Display the new structure of the table and save that to the csv file. 6. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise6_MEID.sql . Save the output of this script as CIS276DA_FinalExercise6_MEID.csv Your results should display one row for each customer that does the following: Display the email address from the Customers table, a count of the number of orders for each customer, and the total amount for each order (HINT: Don't forget about the discounts when calculating the total amount for the order!) o Only show results for customers that have more than 1 order. Sort the results in descending order by the sum of the line item amounts. 7. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise 7 MEID.sql . Save the output of this script as CIS276DA_FinalExercise7_MEID.csv Use an aggregate window function to display the order id from the Order_Items table along with the total amount for each order item in the Order_Items table. (HINT: Don't forget the discount!) Also display the total amount for each order Use an aggregate window function to get the average item amount for each order and display that as a column in the results. Use a named window for the two aggregate functions. Sort the results in ascending order by the order_id column . 8. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise8_MEID.sql. Save the output of this script as CIS276DA_FinalExercise8_MEID.csv Use a correlated subquery to return one row per customer, representing the customer's oldest order (the one with the earliest date). Each row should include these three columns: email address, order_id, and order_date. Sort the results by the order date and order id columns. 9. Write an SQL script that does the following and save this script as CIS276DA_Final Exercise9_MEID.sql . Save the output of this script as CIS276DA_FinalExercise9_MEID.csv Write a SELECT statement that uses the analytic functions to get the highest and lowest sales by product within each category. Return these columns: The category_name column from the Categories table o The product_name column from the Products table A column named total_sales that shows the sum of the sales for each product with sales in the Order_Items table A column named highest_sales that uses the FIRST VALUE function to show the name of the product with the highest sales within each category A column named lowest_sales that uses the LAST_VALUE function to show the name of the product with the lowest sales within each category 10. Write an SQL script that does the following and save this script as CIS276DA_Final Exercise10_MEID.sql . Save the output of this script as CIS276DA_FinalExercise10_MEID.csv add three new columns to the Products table Add one column for the name of the product supplier Add one column for the product markup percentage that provides for three digits to the left of the decimal point and two to the right. This column should have a default value of 50.00 Add one column for the date and time that the product was added to the database Display the new structure of the table and save that to the csv file.

Answer rating: 100% (QA)

It seems youre looking at a set of tasks requiring writing SQL scripts to accomplish specific data queries and modifications on a database Lets handle these one by one 6 Writing a script that displays ... View the full answer