Using analytic functions (also known as windowing functions), display the highest and the lowest carsaleprice for each
Fantastic news! We've Found the answer you've been seeking!
Question:
- Using analytic functions (also known as windowing functions), display the highest and the lowest carsaleprice for each car make in SI database and show them in descending order based on the highest price.
- Using analytic functions return the name of only the customer who bought the fourth most expensive car.
- For each customer from London, return the price of the car they bought and the difference between that price and the average price of the cars sold in that city.
- Options table is frequently used in queries that require optiondes as part of the condition. For example:
SELECT * FROM options WHERE optiondesc LIKE 'SROOFS';
Create an index that will improve the performance of this query. You can copy the table and the data from si options table to test your code. Submit (a) the code used for copying the table and the data, (b) the code for creating the index, and (c) the query performance with and (d) without index as well as (e) the results of your code execution and testing. You will have to use command line statements for explaining the plan and displaying it to be able to print the results on the console for copying. - Place all the SQL code that would perform the above written steps (in 4) in one single file and run it as a single transaction.
Submit the results of the whole transaction.
The transaction output must do the following.
- Remove the table (this step will allow you to run your transaction multiple times without errors. Comment it out when you run the transaction the first time)
- Create the table and copy the data from si options
- Explain plan command for the query shown in the previous question.
- Display the plan
- Create index for optiondesc column
- Explain plan command for the query shown in the previous question.
- Display the plan
Related Book For
Management Accounting Information for Decision-Making and Strategy Execution
ISBN: 978-0137024971
6th Edition
Authors: Anthony A. Atkinson, Robert S. Kaplan, Ella Mae Matsumura, S. Mark Young
Posted Date: