DROP SEQUENCE saleinv_seq; DROP SEQUENCE servinv_seq; DROP TABLE prospect cascade constraints PURGE; DROP TABLE servwork cascade constraints
Question:
DROP SEQUENCE saleinv_seq; DROP SEQUENCE servinv_seq; DROP TABLE prospect cascade constraints PURGE; DROP TABLE servwork cascade constraints PURGE; DROP TABLE servinv cascade constraints PURGE; DROP TABLE invoption cascade constraints PURGE; DROP TABLE tradeinv cascade constraints PURGE; DROP TABLE saleinv cascade constraints PURGE; DROP TABLE baseoption cascade constraints PURGE; DROP TABLE purchinv cascade constraints PURGE; DROP TABLE car cascade constraints PURGE; DROP TABLE employee cascade constraints PURGE; DROP TABLE options cascade constraints PURGE; DROP TABLE customer cascade constraints PURGE; PURGE RECYCLEBIN; -- create tables in an order that does not violate referential integrity CREATE TABLE customer ( custname VARCHAR2(20) CONSTRAINT customer_cname_pk PRIMARY KEY ,custstreet VARCHAR2(20) NOT NULL ,custcity VARCHAR2(20) NOT NULL ,custprovince VARCHAR2(2) DEFAULT 'ON' NOT NULL ,custpostal CHAR(7) ,custhphone CHAR(13) ,custbphone CHAR(13) ); CREATE TABLE options ( optioncode CHAR(4) CONSTRAINT options_ocode_pk PRIMARY KEY ,optiondesc VARCHAR2(30) ,optioncost NUMBER(7,2) ,optionlistprice NUMBER(7,2) ); CREATE TABLE employee ( empname VARCHAR2(20) CONSTRAINT employee_empname_pk PRIMARY KEY ,startdate DATE NOT NULL ,commissionrate NUMBER(3,1) ,title VARCHAR2(26) ,manager VARCHAR2(20) CONSTRAINT employee_fk REFERENCES employee ( empname ) ); CREATE TABLE purchinv ( purchinvno CHAR(6) CONSTRAINT purchinv_purchinvno_pk PRIMARY KEY ,purchfrom VARCHAR2(15) ,purchdate DATE ); CREATE TABLE car ( carserial CHAR(8) CONSTRAINT car_serial_pk PRIMARY KEY ,custname VARCHAR2(20) CONSTRAINT car_cname_fk REFERENCES customer ( custname ) ,carmake VARCHAR2(10) NOT NULL ,carmodel VARCHAR2(10) NOT NULL ,caryear CHAR(4) NOT NULL ,extcolor VARCHAR2(12) NOT NULL ,cartrim VARCHAR(16) NOT NULL ,enginetype VARCHAR2(10) NOT NULL ,purchinvno CHAR(6) ,purchcost NUMBER(9,2) ,freightcost NUMBER(9,2) ,carlistprice NUMBER(9,2) ); CREATE TABLE baseoption ( optioncode CHAR(4) CONSTRAINT baseoption_ocode_fk REFERENCES options ( optioncode ) ,carserial CHAR(8) CONSTRAINT baseoption_serial_fk REFERENCES car ( carserial ) ,CONSTRAINT baseoption_pk PRIMARY KEY ( carserial ,optioncode ) ); CREATE TABLE saleinv ( saleinvno CHAR(6) CONSTRAINT saleinv_pk PRIMARY KEY ,saledate DATE NOT NULL CONSTRAINT saleinv_saledate_ck CHECK ( saledate > TO_DATE('01-JAN-1990','DD-MON-YYYY') ) ,salesman VARCHAR2(20) NOT NULL ,custname VARCHAR2(20) NOT NULL ,carserial CHAR(8) NOT NULL ,insfire CHAR(1) CONSTRAINT saleinv_fire_ck CHECK ( insfire IN ( 'Y' ,'N' ) ) ,inscollision CHAR(1) CONSTRAINT saleinv_collision_ck CHECK ( inscollision IN ( 'Y' ,'N' ) ) ,insliability CHAR(1) CONSTRAINT saleinv_liability_ck CHECK ( insliability IN ( 'Y' ,'N' ) ) ,insproperty CHAR(1) CONSTRAINT saleinv_property_ck CHECK ( insproperty IN ( 'Y' ,'N' ) ) ,taxrate NUMBER(4,2) DEFAULT 13 ,licfee NUMBER(6,2) ,discount NUMBER(8,2) ,commission NUMBER(8,2) ,carsaleprice NUMBER(9,2) ,CONSTRAINT saleinv_customer_fk FOREIGN KEY ( custname ) REFERENCES customer ( custname ) ,CONSTRAINT saleinv_car_fk FOREIGN KEY ( carserial ) REFERENCES car ( carserial ) ,CONSTRAINT saleinv_employee_fk FOREIGN KEY ( salesman ) REFERENCES employee ( empname ) ); CREATE TABLE tradeinv ( saleinvno CHAR(6) CONSTRAINT tradeinv_saleinv_fk REFERENCES saleinv ( saleinvno ) ,carserial CHAR(8) CONSTRAINT tradeinv_car_fk REFERENCES car ( carserial ) ,tradeallow NUMBER(9,2) ,CONSTRAINT tradeinv_pk PRIMARY KEY ( saleinvno ,carserial ) ); CREATE TABLE invoption ( saleinvno CHAR(6) CONSTRAINT invoption_saleinv_fk REFERENCES saleinv ( saleinvno ) ,optioncode CHAR(4) CONSTRAINT invoption_options_fk REFERENCES options ( optioncode ) ,optionprice NUMBER(7,2) ,CONSTRAINT invoption_pk PRIMARY KEY ( saleinvno ,optioncode ) ); CREATE TABLE servinv ( servinvno CHAR(5) CONSTRAINT servinv_pk PRIMARY KEY ,servdate DATE NOT NULL ,custname VARCHAR2(20) NOT NULL CONSTRAINT servinv_cname_fk REFERENCES customer ( custname ) ,carserial CHAR(8) NOT NULL CONSTRAINT servinv_car_fk REFERENCES car ( carserial ) ,partscost NUMBER(7,2) ,laborcost NUMBER(7,2) ,taxrate NUMBER(4,2) DEFAULT 13.0 ); CREATE TABLE servwork ( servinvno CHAR(5) CONSTRAINT servwork_servinv_fk REFERENCES servinv ( servinvno ) ,workdesc VARCHAR2(80) ,CONSTRAINT servwork_pk PRIMARY KEY ( servinvno ,workdesc ) ); CREATE TABLE prospect ( custname VARCHAR2(20) NOT NULL CONSTRAINT prospect_customer_fk REFERENCES customer ( custname ) ,carmake CHAR(10) NOT NULL CONSTRAINT prospect_make_ck CHECK ( carmake IN ( 'ACURA' ,'MERCEDES' ,'LAND ROVER' ,'JAGUAR' ) ) ,carmodel VARCHAR2(10) ,caryear CHAR(4) ,carcolor VARCHAR2(12) ,cartrim VARCHAR2(16) ,optioncode CHAR(4) CONSTRAINT porspect_options_fk REFERENCES options ( optioncode ) ,CONSTRAINT prospect_un UNIQUE ( custname ,carmake ,carmodel ,caryear ,carcolor ,cartrim ,optioncode ) ); -- create sequences that are going to be used for creating primary keys for saleinv and servinv CREATE SEQUENCE saleinv_seq; CREATE SEQUENCE servinv_seq; INSERT INTO customer SELECT * FROM si.customer; INSERT INTO options SELECT * FROM si.options; INSERT INTO employee SELECT * FROM si.employee; INSERT INTO purchinv SELECT * FROM si.purchinv; INSERT INTO car SELECT * FROM si.car; INSERT INTO baseoption SELECT * FROM si.baseoption; INSERT INTO saleinv SELECT * FROM si.saleinv; INSERT INTO invoption SELECT * FROM si.invoption; INSERT INTO tradeinv SELECT * FROM si.tradeinv; INSERT INTO servinv SELECT * FROM si.servinv; INSERT INTO servwork SELECT * FROM si.servwork; INSERT INTO prospect SELECT * FROM si.prospect; COMMIT;
- Using analytic functions return the name of only the customer who bought the fourth most expensive car. (5 marks)
Analysis Synthesis And Design Of Chemical Processes
ISBN: 9780134177403
5th Edition
Authors: Richard Turton, Joseph Shaeiwitz, Debangsu Bhattacharyya, Wallace Whiting