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)
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
Related Book For
Posted Date:
Students also viewed these programming questions
-
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...
-
How would you carry out the following reactions to introduce deuterium into organicmolecules? (a) CHH2C3CCH2C Hs 2H5 (b) C2H5 CHH2C3CCH2CH3 C=C Hs D (c) CH3CH2CH2C=CH CH3CH2CH2C=CD CD CD2 C=CH (d)
-
Richard Foust worked as a project manager at San Jose Construction, Inc. (SJC). During the first part of 2004, he was managing 18 different projects. Foust grew unhappy with his employment situation...
-
Construction on the Bonita Full-Service Car Wash is nearing completion. The owner is Dave Kear, a retired accounting professor. The car wash is strategically located on a busy street that separates...
-
QUESTION TWO a) State and explain five monetary policy instruments used by the central bank to regulate interest rate, inflation rate, exchange rate and the quantity of money in the economy [15...
-
Cardinal Company is considering a five-year project that would require a $2,975,000 investment in equipment with a useful life of five years and no salvage value . The company's discount rate is 14%....
-
Google has just released a new artificial intelligence (AI) surveillance tool. Google claims that the tool is identical to the human perceptual system. For example, even while doing a number of...
-
G.A.S. married his wife, S.I.S., on January 19, 1998. He began to have mental health problems in 2011; that year, he was hospitalized at the Delaware State Hospital for eight weeks. Similar illnesses...
-
On October 1, George Jones entered into a contract with Johnson Motor Company, a dealer in automobiles, to buy a car for \($10,600.\) He paid \($1,100\) down and agreed to make monthly payments...
-
Associated Builders, Inc., provided labor and materials to William M. Coggins and Benjamin W. Coggins, doing business as Ben & Bills Chocolate Emporium, to complete a structure on Main Street in Bar...
-
On May 7, Roy, a minor, a resident of Smithton, purchased an automobile from Royal Motors, Inc., for \($12,750\) in cash. On the same day, he bought a motor scooter from Marks, also a minor, for...
-
Huxley, the owner of the Missing Link Chain-Link Fence Company is trying to determine how many chain-link fabricating machines to buy for its factory. If we define a chain-link fence of some...
-
Which elementary particle is the heaviest? O electrons protons They are all identical Neutrons
-
1. Firms may hold financial assets to earn returns. How the firm would classify financial assets? What treatment will such financial assets get in the financial statements in accordance with US GAAP...
-
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...
-
In March 1998, a group of Trojan Technologies Inc. (Trojan) employees grappled with the issue of how to structure the business to effectively interact with their customers and to manage the companys...
-
Make up a list of criteria that defines a good downsizing effort.
-
Imagine that a company is converting from conventional mass technology to a highly flexible, computerized, integrated production system. List structural and behavioural problems that the company...
Study smarter with the SolutionInn App