For this assignment you must use SI schema

THIS IS ONE QUESTION WITH 5 PARTS.

1. Print the total amount, the average dollar value of service visits (parts and labour costs) and the number of those visits for Acura, Mercedes and Jaguar car makes that are sold between September 2012 and November 2015 inclusive (5 marks).
2. SI schema contains customers that have bought one or more vehicles. They can be classified using the following criteria:
   1. Customers that have bought only one car (one-time buyer)
   2. Customer that have bought two cars (two-time buyer)
   3. Customers that have bought more than two cars (frequent buyer)
   Using a SINGLE SELECT statement, display a list of customers with their names and what type of buyer they are for all those customers that have bought Mercedes car makes. (5 marks)
3. Using SET operations, display a list of customers that are interested in a car (prospect table) of the same make and model which they already own. (5 marks)
4. Show a list of total amount of money spent on the labour cost of servicing Toyota cars. Show the subtotals for each model (5 marks)
5. Write a query using analytic functions that will show the serial number, the price of each JAGUAR car as well as the cumulative sale price totals. (5 marks)

Specialty Imports E-R Diagram CAR CUSTOMER custname VARCHAR2(20) custstreet VARCHAR2(20) NOT NULL custcity VARCHAR2(20) NOT NULL custprovince CHAR(2) NOT NULL DEFAULT ON custpostal CHAR(7) NOT NULL custhphone CHAR(13) NOT NULL Ho- custbphone CHAR(13) NOT NULL SALEINV saleinvnoCHAR(6) saledate DATE NOT NULL salesman VARCHAR2(20) NOT NULL custname VARCHAR2(20) NOT NULL carserial CHAR(8) NOT NULL insfire CHAR(1) inscollision CHAR(1) insliability CHAR(1) carserial CHAR(8) custname VARCHAR2(20) carmake VARCHAR2(10) NOT NULL ( carmodel VARCHAR2(10) NOT NULL caryear CHAR(4) NOT NULL extcolour VARCHAR2(12) NOT NULL cartrim VARCHAR2(16) NOT NULL enginetype VARCHAR2(10) NOT NULL enginetype VARCHAR2(10) NOT NULL purchinvno CHAR(6) purchcost NUMBER(9.2) freightcost NUMBER(92) carlistprice NUMBER(9.2) PROSPECT custame VARCHAR2(20) NOT NULL carmake VARCHAR2(10) NOT NULL carmodel VARCHAR2(10) caryear CHAR(4) carcolour VARCHAR2(12) cartrim VARCHAR2(16) SERVINV servinyno CHAR(5) 5 servdate DATE NOT NULL custname VARCHAR2(20) NOT NULL carserial CHAR(8) NOT NULL partscost NUMBER(7.2) laborcost NUMBER(7.2) taxrate NUMBER(4.2) DEFAULT 13.0 insproperty CHAR(1) taxrate NUMBER(4.2) DEFAULT 13.0 licfee NUMBER(6.2) discount NUMBER(82) commission NUMBER(8,2) carsaleprice NUMBER(9,2) optioncode CHAR2(4) Text EMPLOYEE SERVWORK PURCHINV purchinyno CHAR(6) purchfrom VARCHAR2(15) purchdate DATE TRADEINV saleinvno CHAR(6) carserial CHAR(8) tradeallow NUMBER(9,2) servinvno CHAR(5) workdesc VARCHAR2(80) empname VARCHAR2(20) startdate DATE NOT NULL commissionrate NUMBER(3.1) title VARCHAR2(26) manager VARCHAR2(20) ( BASEOPTION optioncode CHAR(4) OPTIONS optioncode CHAR(4) optiondesc VARCHAR2(30) optioncost NUMBER(7.2) optionlistprice NUMBER(7.2) carserial CHAR(8) saleinvno INVOPTION CHAR(6) 0 optioncode CHAR(4) optionprice NUMBER(7,2) Specialty Imports E-R Diagram CAR CUSTOMER custname VARCHAR2(20) custstreet VARCHAR2(20) NOT NULL custcity VARCHAR2(20) NOT NULL custprovince CHAR(2) NOT NULL DEFAULT ON custpostal CHAR(7) NOT NULL custhphone CHAR(13) NOT NULL Ho- custbphone CHAR(13) NOT NULL SALEINV saleinvnoCHAR(6) saledate DATE NOT NULL salesman VARCHAR2(20) NOT NULL custname VARCHAR2(20) NOT NULL carserial CHAR(8) NOT NULL insfire CHAR(1) inscollision CHAR(1) insliability CHAR(1) carserial CHAR(8) custname VARCHAR2(20) carmake VARCHAR2(10) NOT NULL ( carmodel VARCHAR2(10) NOT NULL caryear CHAR(4) NOT NULL extcolour VARCHAR2(12) NOT NULL cartrim VARCHAR2(16) NOT NULL enginetype VARCHAR2(10) NOT NULL enginetype VARCHAR2(10) NOT NULL purchinvno CHAR(6) purchcost NUMBER(9.2) freightcost NUMBER(92) carlistprice NUMBER(9.2) PROSPECT custame VARCHAR2(20) NOT NULL carmake VARCHAR2(10) NOT NULL carmodel VARCHAR2(10) caryear CHAR(4) carcolour VARCHAR2(12) cartrim VARCHAR2(16) SERVINV servinyno CHAR(5) 5 servdate DATE NOT NULL custname VARCHAR2(20) NOT NULL carserial CHAR(8) NOT NULL partscost NUMBER(7.2) laborcost NUMBER(7.2) taxrate NUMBER(4.2) DEFAULT 13.0 insproperty CHAR(1) taxrate NUMBER(4.2) DEFAULT 13.0 licfee NUMBER(6.2) discount NUMBER(82) commission NUMBER(8,2) carsaleprice NUMBER(9,2) optioncode CHAR2(4) Text EMPLOYEE SERVWORK PURCHINV purchinyno CHAR(6) purchfrom VARCHAR2(15) purchdate DATE TRADEINV saleinvno CHAR(6) carserial CHAR(8) tradeallow NUMBER(9,2) servinvno CHAR(5) workdesc VARCHAR2(80) empname VARCHAR2(20) startdate DATE NOT NULL commissionrate NUMBER(3.1) title VARCHAR2(26) manager VARCHAR2(20) ( BASEOPTION optioncode CHAR(4) OPTIONS optioncode CHAR(4) optiondesc VARCHAR2(30) optioncost NUMBER(7.2) optionlistprice NUMBER(7.2) carserial CHAR(8) saleinvno INVOPTION CHAR(6) 0 optioncode CHAR(4) optionprice NUMBER(7,2)