6. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise6_MEID.sql ....
Fantastic news! We've Found the answer you've been seeking!
Question:
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.
Expert Answer:
Answer rating: 100% (QA)
Here are the SQL scripts for each exercise Exercise 6 SELECT cemailaddress COUNToorderid AS numorder... View the full answer
Related Book For
Posted Date:
Students also viewed these programming questions
-
1- A critique of the quality of the testing description presented in the report. 2- A critique of the quality of the quality assurance plan presented in the report. 3- A master test plan. Try your...
-
Make an SQL queries for displaying the following information. Please remember each query requires using only one table. No JOINs are required. 1. Provide the number of Vendors hailing from each...
-
Mr Ha is interested in setting up a coffee shop in Canberra, Australia. After consulting with his brother who owns a caf in other town of the territory, he has decided to commence the business in...
-
Write using only positive exponents and then evaluate. Assume that all variables represent nonzero real numbers. 5 -2
-
View the YouTube video: https://www.youtube.com/watch?v=PHJ8eybXJdw Answer the Question, How to Manage Team Conflict. Briefly describe a team conflict you observed or were involved in that had...
-
A runner, jogging along a straight line path, starts at a position 60 m east of a milestone marker and heads west. After a short time interval he is20 m west of the mile marker. Choose east to be the...
-
During 2016, the exploration department of Black Gold Oil Corporation incurred the following costs in exploring the Oklahoma Anadarko Basin. Give the entries. Shooting rights.. Bottom-hole...
-
The FCPA has too many exceptions to be an effective deterrent to unethical business practices. Do you agree or disagree with this statement? Explain your answer.
-
23 0.2 points Exercise 13-7 (Algo) Sell or Process Further Decisions [LO13-7] Dorsey Company manufactures three products from a common input in a joint processing operation. Joint processing costs up...
-
INCOME Everyday Flowers and Workshops Sales of Product Income Special Occasions Total Income COST OF GOODS SOLD Flower Purchases and Supplies Supplies and materials - COS Total Cost of Goods Sold...
-
The system to be controlled is a mechanical mass-spring-damper system. The input is a force, u and the output is the displacement of the first mass, y. The open-loop system is represented by the...
-
For its fall semester, Lee University, a private, nonprofit university had the following transactions: 1. Lee University receives $1,600,000 prior to the start of the semester for housing and dining...
-
Computing and Recording Interest Capitalization Weld Corporation is constructing a plant for its own use. Weld capitalizes interest on an annual basis. The following expenditures are made during the...
-
Arrow Hospitality prepares adjustments monthly and showed the following at September 30, 2023: Cash ARROW HOSPITALITY Trial Balances September 30, 2823 Unadjusted Trial Balance Account Dr. 6,908 Cr....
-
The following are the list the tables with primary keys, attributes, and foreign keys that implement the following UML Class diagram given the accompanying list of attributes. Attributes: ...
-
This assignment is about designing Finite State Machines (FSM) for a two-player game. The game is administered by a referee who, using a random number, decides which one of the players makes the...
-
Zen began a new consulting firm on January 5. The accounting equation showed the following financial summary for each of the company's first five transactions. Analyze the accounting equation for...
-
Write a function that reads a Float24_t value: Float24_t float24_read(void) A legitimate float24 value string is of the form: "mantissabexponent" where the mantissa (m) and the exponent (e) may have...
-
Pick two critical thinking questions and describe why they are the most important questions to ask.
-
The major ways to utilize, capture, or store the suns energy are solar thermal solar voltaic, biomass conversion, solar water splitting and solar chemical. Solar chemical refers to processes that...
-
QBR Questions Before Reading. Research has shown (J. Exp. Psychol. Learn. Mem. Cogn., 40, 106114 (2014)) that if you ask a question of the material before reading the material you will have greater...
-
Suppose a company has five-year, semiannual-coupon bonds carrying an 8 percent coupon rate that are selling for $1,200. What would be the aftertax cost of debt on these bonds if the appropriate tax...
-
Suppose a company has five-year, semiannual-coupon bonds carrying an 8 percent coupon rate that are selling for $1,200. What would be the beforetax cost of debt on these bonds if the appropriate tax...
-
Suppose a firm has 19 million shares of common stock outstanding with a par value of \($1.00\) per share. The current market price per share is \($18.35\). The firm has outstanding debt with a par...
Study smarter with the SolutionInn App