database

/* the following SPOOL command will capture the results of */ /* running this script in a SPOOL file name */ /* f:SLQ_Output_zero_for_Chap7.LST */ /* Note that the file is on a USB jumpdrive with a drive */ /* letter of f: */ /* The name assigned to this spool is not special in any way. */ /* You can assign any name to the file. The .LST file */ /* extension will be automatically added by the SPOOL process */ SPOOL f:SQL_Output_zero_for_Chap7; /* Step-0 used to create and load textbook Chapter 7 tables */ DROP TABLE TBL_PRJ_EMPLOYEE; DROP TABLE TBL_PRJ_CLIENT; DROP TABLE TBL_PRJ_CAR; DROP TABLE TBL_PRJ_MECHANIC; /* Step-1 CREATE EMPLOYEE TABLE*/ CREATE TABLE TBL_PRJ_EMPLOYEE ( EMP_ID CHAR(10) PRIMARY KEY, EMP_FNAME CHAR(15) NOT NULL, EMP_LNAME CHAR(15) NOT NULL, EMP_PHONE_NUMBER CHAR(10) NOT NULL, EMP_ZIPCODE CHAR(5) NOT NULL, CLIENT_ID CHAR(10) PRIMARY KEY, C_PRICE VARCHAR(70) NOT NULL, /* Step-2 CREATE ClIENT TABLE*/ CREATE TABLE TBL_PRJ_CLIENT ( CLIENT_ID CHAR(10) PRIMARY KEY, CLIENT_FNAME CHAR(15) NOT NULL, CLIENT_LNAME CHAR(15) NOT NULL, CLIENT_PHONE_NUMBER CHAR(10) NOT NULL, CLIENT_ZIPCODE CHAR(5) NOT NULL, FOREIGN KEY (CLIENT_ID) REFERENCES TBL_PRJ_EMPLOYEE(CLIENT_ID), /* Step-3 Create CAR table */ CREATE TABLE TBL_PRJ_CAR ( C_MAKE INTEGER PRIMARY KEY, C_MODEL INTEGER PRIMARY KEY, C_MAKE VARCHAR(70) NOT NULL, C_MODEL VARCHAR(70) NOT NULL, C_PRICE VARCHAR(70) NOT NULL, C_YEAR VARCHAR(4) NOT NULL, C_MPG VARCHAR(3) NOT NULL, FOREIGN KEY (C_PRICE) REFERENCES TBL_PRJ_EMPLOYEE(C_PRICE), /* Step-4 Create MECHANIC table */ CREATE TABLE TBL_PRJ_CLIENT ( MECHA_ID CHAR(10) PRIMARY KEY, MECHA_FNAME CHAR(15) NOT NULL, MECHA_LNAME CHAR(15) NOT NULL, MECHA_EXTENSION CHAR(10) NOT NULL, MECHA_COST CHAR(10) NOT NULL, /* Step-5 Create INDEX table */ CREATE INDEX CLIE_INLNAME ON TBL_PRJ_CLIENT(CLIENT_LNAME); CREATE INDEX CA_MODEL ON TBL_PRJ_CAR(CAR_MODEL); /* Step-6 INSERT MECHANIC table */ INSERT INTO TBL_PRJ_MECHANIC VALUES ('1001','FRANCK','YEBOUE','614424514','43221','14253'); INSERT INTO TBL_PRJ_MECHANIC VALUES ('1002','JEFF','LO','614412515','43231','24587'); INSERT INTO TBL_PRJ_MECHANIC VALUES ('1011','HADJI','SECK','614524516','43220','35689'); INSERT INTO TBL_PRJ_MECHANIC VALUES ('1451','DARKO','MILIC','61824517','43227','1425'); INSERT INTO TBL_PRJ_MECHANIC VALUES ('1003','ANTHONY','BABEN','614224518','43228','125'); INSERT INTO TBL_PRJ_MECHANIC VALUES ('1781','GORA','SECK','614624519','43320','7896'); INSERT INTO TBL_PRJ_MECHANIC VALUES ('1401','IBRA','MOVIC','614424510','43221','14256'); INSERT INTO TBL_PRJ_MECHANIC VALUES ('1511','EZIQUEL','LAVEZZI','614724511','43229','478'); INSERT INTO TBL_PRJ_MECHANIC VALUES ('1004','JEAN','CLAUDE','614824512','43226',9654'); /* Step-7 INSERT EMPLOYEE table */ INSERT INTO TBL_PRJ_EMPLOYEE VALUES ('2001','ousseyn','sagna','614424114','43221','3001','5000'); INSERT INTO TBL_PRJ_EMPLOYEE VALUES ('2011','marco','verati','614624316','43228','3003','2000'); INSERT INTO TBL_PRJ_EMPLOYEE VALUES ('2011','alice','Sy','614524316','43220','3011','17000'); INSERT INTO TBL_PRJ_EMPLOYEE VALUES ('2451','Daniel','ribery','61424417','43226','3781','27597'); INSERT INTO TBL_PRJ_EMPLOYEE VALUES ('2003','vick','robben','614224618','43227','3401','22000'); INSERT INTO TBL_PRJ_EMPLOYEE VALUES ('2781','jake','miller','614624719','43327'.'3511','20562'); INSERT INTO TBL_PRJ_EMPLOYEE VALUES ('2401','javier','pastore','614424810','43224','3004','31456'); INSERT INTO TBL_PRJ_EMPLOYEE VALUES ('2511','savatore','sirigu','614724911','43223','3451','25047'); INSERT INTO TBL_PRJ_EMPLOYEE VALUES ('2004','siaka','tiene','614827812','43222','3002','4789'); /* Step-8 INSERT CLIENT table */ INSERT INTO TBL_PRJ_CLIENT VALUES ('3001','MAMADOU','SAKHO','615424514','43221','14253'); INSERT INTO TBL_PRJ_CLIENT VALUES ('3002','THIAGO','SYLVA','494412515','46231','24587'); INSERT INTO TBL_PRJ_CLIENT VALUES ('3011','ALEX','MOTTA','617524516','43220','35689'); INSERT INTO TBL_PRJ_CLIENT VALUES ('3451','VICTOR','HUGO','62824517','43223','1425'); INSERT INTO TBL_PRJ_CLIENT VALUES ('3003','DEEQ','GOONER','914224518','43228','125'); INSERT INTO TBL_PRJ_CLIENT VALUES ('3781','LUCAS','MOURA','496624519','45320','7896'); INSERT INTO TBL_PRJ_CLIENT VALUES ('3401','CLEMENT','CHAMTOME','221424510','48221','14256'); INSERT INTO TBL_PRJ_CLIENT VALUES ('3511','LIONEL','MESSI','615724511','46229','478'); INSERT INTO TBL_PRJ_CLIENT VALUES ('3004','RONALDO','DEJUAN','617824512','44226',9654'); /* Step-9 INSERT CAR INFO */ INSERT INTO TBL_PRJ_CAR VALUES ('Mitsubishi', 'Galant', '5000','2004','159'); INSERT INTO TBL_PRJ_CAR VALUES ('Cadillac', 'CTS', '17000','2010', 57); INSERT INTO TBL_PRJ_CAR VALUES ('Ford', 'Ranger XL', '11000''2008', 112); INSERT INTO TBL_PRJ_CAR VALUES ('mecedez', 'Mclaren', '10000','2006', 98); INSERT INTO TBL_PRJ_CAR VALUES ('Honda', 'Insight', '12000','2005', 56); INSERT INTO TBL_PRJ_CAR VALUES ('MINI Cooper', 'S', '23267','2002', 31); INSERT INTO TBL_PRJ_CAR VALUES ('Mitsubishi', 'Eclipse GS', '14818','2013', 30); INSERT INTO TBL_PRJ_CAR VALUES ('Pontiac', 'G6 GT', '27597','2013', 24); INSERT INTO TBL_PRJ_CAR VALUES ('Toyota', 'Camry Hybrid', '31456','2013', 34); INSERT INTO TBL_PRJ_CAR VALUES ('CHEVY ', 'Camaro', '20562','2006', 25); /* STEP 10 SELECT CONTENT TBL*/ SELECT * FROM TBL_PRJ_CAR; SELECT * FROM TBL_PRJ_EMPLOYEE; SELECT * FROM TBL_PRJ_CLIENT; SELECT * FROM TBL_PRJ_MECHANIC; /* step 11 join queries*/ SELECT CLIENT_FNAME,CLIENT_LNAME,CLIENT.ZIPCODE, EMP_FNAME, EMP_ZIPCODE FROM TBL_PRJ_CLIENT, TBL_PRJ_EMPLOYEE WHERE CLIENT.ZIPCODE = EMP.ZIP_CODE; /* STEP 12 SELECT MAX CAR PRICE */ SELECT make, model FROM TBL_PRJ_CAR; WHERE C.price = (SELECT MAX (price) FROM TBL_PRJ_CAR); /* step 13 car price superior to 20000 */ SELECT C_MODEL,C_MAKE,C_PRICE, FROM TBL_PRJ_EMPLOYEE; TBL_PRJ_CAR; WHERE CAR.C_PRICE = EMPLOYEE.C_PRICE AND C_YEAR = '2013' AND C_PRICE > 20000 /*TOTAL CAR PRICE*/ SELECT SUM(C_PRICE) AS TOTAL_C_PRICE FROM TBL_PRJ_CAR