- Using analytic functions display the highest and the lowest carsaleprice for each make in the database and rank them based on the highest price in descending order.

- Using analytic functions return the name of the customer who bought the third 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 often require optionsedc as part of the condition. For example:

SELECT * FROM options WHERE optiondesc LIKE '%ROOF%';

Create an index that will improve the performance of this query.

- Place all the SQL code that would perform the above written steps in one single file and run it as a single transaction. The transaction output must contain:

1. remove the table, create the table and insert data, 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.

SCHEMA:

CAR CUSTONER cusname VARCHAR220) cusstreet VARCHAR2(20) NOT NULL Cuscity VARCHAR220) NOT NULL cusprovince CHAR(2) NOT NULL DEFAULT ON Cusposal CHAR(7) NOT NULL cushphone CHAR(13) NOT NULL Cusbphone CHAR(13) NOT NULL carse al CHAR 8) to infine SALEINV seleinino CHAR(6) saledate DATE NOT NULL salesman VARCHAR2(20) NOT NULL Custreme VARCHAR2(20) NOT NULL carserial CHAR(8) NOT NULL CHAR(1) inscollision CHAR(11 insliability CHAR(11 Insproperty CHAR(1) facrate NUMBER(4.2) DEFAULT 13.0 licee NUMBER(6.21 discount NUMBER 8 21 commission NUMBER (8.2) Cersalaprice NUMBER 9.21 PROSPECT cuszneme VARCHAR2(28) carmale VARCHAR2(10) NOT NULL camodel VARCHAR2(10) NOT NULL CHAR4) NOT NULL eccolour VARCHAR2(12) NOT NULL catrim VARCHAR2(16) NOT NULL engineype VARCHAR2(10) NOT NULL engineype VARCHAR2(10) NOT NULL purchinon CHAP(6) pu chcast NUMBER19.2) treghtcost NUMBER19.2) carlistprice NUMEER 9.2) SERVINV cusmame VARCHAR220) NOT NULL carmalog VARCHAR2110) NOT NULL carrode VARCHAR2110) canear CHARIA carcolour VARCHAR2112) cartrm VARCHAR216) optioncode CHAR24) Sevine CHARI) sevdale DATE NOT NULL custeme VARCHAR2/20) NOT NULL carserial CHAR(B) NOT NULL parts cool NUMBER(72) latorcost NUNBER(7.2) tarate NUMBER(42) DEFAULT 13.0 TRADEINV saleming CHARO SERVWORK servinno CHARIS) werkdesc VARCHAR/80 PURCHINV Durchino CHARLE) purchfrom VARCHAR215) purchdate DATE EMPLOYEE empname VARCHAR2/20 Startdate DATE NOT NULL carserial CHAR(8) tradellow NUMBER(9,2) commissionate NUMBER13 1) VARCHAR2(26) VARCHAR2(20) manajer OPTIONS optiorcode CHARL) optiondesc VARCHAR2:30) optiocos: NUMBERT 2) ++ option listrice NUMBER (12) BASEOPTION opticncode CHAR14 Carserial CHAR(@ INVOPTION saleinvno CHAR(6) optioncode CHAR(4) optionprice NUMBER(7.2) CAR CUSTONER cusname VARCHAR220) cusstreet VARCHAR2(20) NOT NULL Cuscity VARCHAR220) NOT NULL cusprovince CHAR(2) NOT NULL DEFAULT ON Cusposal CHAR(7) NOT NULL cushphone CHAR(13) NOT NULL Cusbphone CHAR(13) NOT NULL carse al CHAR 8) to infine SALEINV seleinino CHAR(6) saledate DATE NOT NULL salesman VARCHAR2(20) NOT NULL Custreme VARCHAR2(20) NOT NULL carserial CHAR(8) NOT NULL CHAR(1) inscollision CHAR(11 insliability CHAR(11 Insproperty CHAR(1) facrate NUMBER(4.2) DEFAULT 13.0 licee NUMBER(6.21 discount NUMBER 8 21 commission NUMBER (8.2) Cersalaprice NUMBER 9.21 PROSPECT cuszneme VARCHAR2(28) carmale VARCHAR2(10) NOT NULL camodel VARCHAR2(10) NOT NULL CHAR4) NOT NULL eccolour VARCHAR2(12) NOT NULL catrim VARCHAR2(16) NOT NULL engineype VARCHAR2(10) NOT NULL engineype VARCHAR2(10) NOT NULL purchinon CHAP(6) pu chcast NUMBER19.2) treghtcost NUMBER19.2) carlistprice NUMEER 9.2) SERVINV cusmame VARCHAR220) NOT NULL carmalog VARCHAR2110) NOT NULL carrode VARCHAR2110) canear CHARIA carcolour VARCHAR2112) cartrm VARCHAR216) optioncode CHAR24) Sevine CHARI) sevdale DATE NOT NULL custeme VARCHAR2/20) NOT NULL carserial CHAR(B) NOT NULL parts cool NUMBER(72) latorcost NUNBER(7.2) tarate NUMBER(42) DEFAULT 13.0 TRADEINV saleming CHARO SERVWORK servinno CHARIS) werkdesc VARCHAR/80 PURCHINV Durchino CHARLE) purchfrom VARCHAR215) purchdate DATE EMPLOYEE empname VARCHAR2/20 Startdate DATE NOT NULL carserial CHAR(8) tradellow NUMBER(9,2) commissionate NUMBER13 1) VARCHAR2(26) VARCHAR2(20) manajer OPTIONS optiorcode CHARL) optiondesc VARCHAR2:30) optiocos: NUMBERT 2) ++ option listrice NUMBER (12) BASEOPTION opticncode CHAR14 Carserial CHAR(@ INVOPTION saleinvno CHAR(6) optioncode CHAR(4) optionprice NUMBER(7.2)