/* Database Systems, Coronel/Morris */
/* Type of SQL : Oracle */

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';

CREATE TABLE AIRCRAFT (
AC_NUMBER varchar2(5),
MOD_CODE varchar2(10),
AC_TTAF float(8),
AC_TTEL float(8),
AC_TTER float(8)
);
INSERT INTO AIRCRAFT VALUES('1484P','PA23-250','1833.1','1833.1','101.8');
INSERT INTO AIRCRAFT VALUES('2289L','C-90A','4243.8','768.9','1123.4');
INSERT INTO AIRCRAFT VALUES('2778V','PA31-350','7992.9','1513.1','789.5');
INSERT INTO AIRCRAFT VALUES('4278Y','PA31-350','2147.3','622.1','243.2');

/* -- */

CREATE TABLE CHARTER (
CHAR_TRIP integer,
CHAR_DATE date,
AC_NUMBER varchar2(5),
CHAR_DESTINATION varchar2(3),
CHAR_DISTANCE float(8),
CHAR_HOURS_FLOWN float(8),
CHAR_HOURS_WAIT float(8),
CHAR_FUEL_GALLONS float(8),
CHAR_OIL_QTS integer,
CUS_CODE integer
);
INSERT INTO CHARTER VALUES('10001','2/5/2016','2289L','ATL','936','5.1','2.2','354.1','1','10011');
INSERT INTO CHARTER VALUES('10002','2/5/2016','2778V','BNA','320','1.6','0','72.6','0','10016');
INSERT INTO CHARTER VALUES('10003','2/5/2016','4278Y','GNV','1574','7.8','0','339.8','2','10014');
INSERT INTO CHARTER VALUES('10004','2/6/2016','1484P','STL','472','2.9','4.9','97.2','1','10019');
INSERT INTO CHARTER VALUES('10005','2/6/2016','2289L','ATL','1023','5.7','3.5','397.7','2','10011');
INSERT INTO CHARTER VALUES('10006','2/6/2016','4278Y','STL','472','2.6','5.2','117.1','0','10017');
INSERT INTO CHARTER VALUES('10007','2/6/2016','2778V','GNV','1574','7.9','0','348.4','2','10012');
INSERT INTO CHARTER VALUES('10008','2/7/2016','1484P','TYS','644','4.1','0','140.6','1','10014');
INSERT INTO CHARTER VALUES('10009','2/7/2016','2289L','GNV','1574','6.6','23.4','459.9','0','10017');
INSERT INTO CHARTER VALUES('10010','2/7/2016','4278Y','ATL','998','6.2','3.2','279.7','0','10016');
INSERT INTO CHARTER VALUES('10011','2/7/2016','1484P','BNA','352','1.9','5.3','66.4','1','10012');
INSERT INTO CHARTER VALUES('10012','2/8/2016','2778V','MOB','884','4.8','4.2','215.1','0','10010');
INSERT INTO CHARTER VALUES('10013','2/8/2016','4278Y','TYS','644','3.9','4.5','174.3','1','10011');
INSERT INTO CHARTER VALUES('10014','2/9/2016','4278Y','ATL','936','6.1','2.1','302.6','0','10017');
INSERT INTO CHARTER VALUES('10015','2/9/2016','2289L','GNV','1645','6.7','0','459.5','2','10016');
INSERT INTO CHARTER VALUES('10016','2/9/2016','2778V','MQY','312','1.5','0','67.2','0','10011');
INSERT INTO CHARTER VALUES('10017','2/10/2016','1484P','STL','508','3.1','0','105.5','0','10014');
INSERT INTO CHARTER VALUES('10018','2/10/2016','4278Y','TYS','644','3.8','4.5','167.4','0','10017');

/* -- */

CREATE TABLE CREW (
CHAR_TRIP integer,
EMP_NUM integer,
CREW_JOB varchar2(20)
);
INSERT INTO CREW VALUES('10001','104','Pilot');
INSERT INTO CREW VALUES('10002','101','Pilot');
INSERT INTO CREW VALUES('10003','105','Pilot');
INSERT INTO CREW VALUES('10003','109','Copilot');
INSERT INTO CREW VALUES('10004','106','Pilot');
INSERT INTO CREW VALUES('10005','101','Pilot');
INSERT INTO CREW VALUES('10006','109','Pilot');
INSERT INTO CREW VALUES('10007','104','Pilot');
INSERT INTO CREW VALUES('10007','105','Copilot');
INSERT INTO CREW VALUES('10008','106','Pilot');
INSERT INTO CREW VALUES('10009','105','Pilot');
INSERT INTO CREW VALUES('10010','108','Pilot');
INSERT INTO CREW VALUES('10011','101','Pilot');
INSERT INTO CREW VALUES('10011','104','Copilot');
INSERT INTO CREW VALUES('10012','101','Pilot');
INSERT INTO CREW VALUES('10013','105','Pilot');
INSERT INTO CREW VALUES('10014','106','Pilot');
INSERT INTO CREW VALUES('10015','101','Copilot');
INSERT INTO CREW VALUES('10015','104','Pilot');
INSERT INTO CREW VALUES('10016','105','Copilot');
INSERT INTO CREW VALUES('10016','109','Pilot');
INSERT INTO CREW VALUES('10017','101','Pilot');
INSERT INTO CREW VALUES('10018','104','Copilot');
INSERT INTO CREW VALUES('10018','105','Pilot');

/* -- */

CREATE TABLE CUSTOMER (
CUS_CODE integer,
CUS_LNAME varchar2(15),
CUS_FNAME varchar2(15),
CUS_INITIAL varchar2(1),
CUS_AREACODE varchar2(3),
CUS_PHONE varchar2(8),
CUS_BALANCE float(8)
);
INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0');
INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-1238','0');
INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-2285','896.54');
INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','1285.19');
INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','673.21');
INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','1014.56');
INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-1228','0');
INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','0');
INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','0');
INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','453.98');

/* -- */

CREATE TABLE EARNEDRATING (
EMP_NUM integer,
RTG_CODE varchar2(5),
EARNRTG_DATE date
);
INSERT INTO EARNEDRATING VALUES('101','CFI','2/18/2004');
INSERT INTO EARNEDRATING VALUES('101','CFII','12/15/2011');
INSERT INTO EARNEDRATING VALUES('101','INSTR','11/8/1999');
INSERT INTO EARNEDRATING VALUES('101','MEL','6/23/2000');
INSERT INTO EARNEDRATING VALUES('101','SEL','4/21/1999');
INSERT INTO EARNEDRATING VALUES('104','INSTR','7/15/2002');
INSERT INTO EARNEDRATING VALUES('104','MEL','1/29/2003');
INSERT INTO EARNEDRATING VALUES('104','SEL','3/12/2001');
INSERT INTO EARNEDRATING VALUES('105','CFI','11/18/2003');
INSERT INTO EARNEDRATING VALUES('105','INSTR','4/17/2001');
INSERT INTO EARNEDRATING VALUES('105','MEL','8/12/2001');
INSERT INTO EARNEDRATING VALUES('105','SEL','9/23/2000');
INSERT INTO EARNEDRATING VALUES('106','INSTR','12/20/2001');
INSERT INTO EARNEDRATING VALUES('106','MEL','4/2/2002');
INSERT INTO EARNEDRATING VALUES('106','SEL','3/10/2000');
INSERT INTO EARNEDRATING VALUES('109','CFI','11/5/2004');
INSERT INTO EARNEDRATING VALUES('109','CFII','6/21/2009');
INSERT INTO EARNEDRATING VALUES('109','INSTR','7/23/2002');
INSERT INTO EARNEDRATING VALUES('109','MEL','3/15/2003');
INSERT INTO EARNEDRATING VALUES('109','SEL','2/5/2002');
INSERT INTO EARNEDRATING VALUES('109','SES','5/12/2002');

/* -- */

CREATE TABLE EMPLOYEE (
EMP_NUM integer,
EMP_TITLE varchar2(4),
EMP_LNAME varchar2(15),
EMP_FNAME varchar2(15),
EMP_INITIAL varchar2(1),
EMP_DOB date,
EMP_HIRE_DATE date
);
INSERT INTO EMPLOYEE VALUES('100','Mr.','Kolmycz','George','D','6/15/1952','3/15/2007');
INSERT INTO EMPLOYEE VALUES('101','Ms.','Lewis','Rhonda','G','3/19/1975','4/25/2008');
INSERT INTO EMPLOYEE VALUES('102','Mr.','VanDam','Rhett','','11/14/1968','12/20/2012');
INSERT INTO EMPLOYEE VALUES('103','Ms.','Jones','Anne','M','10/16/1984','8/28/2015');
INSERT INTO EMPLOYEE VALUES('104','Mr.','Lange','John','P','11/8/1981','10/20/2006');
INSERT INTO EMPLOYEE VALUES('105','Mr.','Williams','Robert','D','3/14/1985','1/8/2015');
INSERT INTO EMPLOYEE VALUES('106','Mrs.','Duzak','Jeanine','K','2/12/1978','1/5/2011');
INSERT INTO EMPLOYEE VALUES('107','Mr.','Diante','Jorge','D','8/21/1984','7/2/2006');
INSERT INTO EMPLOYEE VALUES('108','Mr.','Wiesenbach','Paul','R','2/14/1976','11/18/2014');
INSERT INTO EMPLOYEE VALUES('109','Ms.','Travis','Elizabeth','K','6/18/1971','4/14/2011');
INSERT INTO EMPLOYEE VALUES('110','Mrs.','Genkazi','Leighla','W','5/19/1980','12/1/2012');

/* -- */

CREATE TABLE MODEL (
MOD_CODE varchar2(10),
MOD_MANUFACTURER varchar2(15),
MOD_NAME varchar2(20),
MOD_SEATS float(8),
MOD_CHG_MILE float(8)
);
INSERT INTO MODEL VALUES('C-90A','Beechcraft','KingAir','8','2.67');
INSERT INTO MODEL VALUES('PA23-250','Piper','Aztec','6','1.93');
INSERT INTO MODEL VALUES('PA31-350','Piper','Navajo Chieftain','10','2.35');

/* -- */


CREATE TABLE PILOT (
EMP_NUM integer,
PIL_LICENSE varchar2(25),
PIL_RATINGS varchar2(25),
PIL_MED_TYPE varchar2(1),
PIL_MED_DATE date,
PIL_PT135_DATE date
);
INSERT INTO PILOT VALUES('101','ATP','SEL/MEL/Instr/CFII','1','4/12/2016','6/15/2015);
INSERT INTO PILOT VALUES('104','ATP','SEL/MEL/Instr','1','6/10/2015','3/23/2016');
INSERT INTO PILOT VALUES('105','COM','SEL/MEL/Instr/CFI','2','2/25/2016','2/12/2016');
INSERT INTO PILOT VALUES('106','COM','SEL/MEL/Instr','2','4/2/2016','12/24/2015');
INSERT INTO PILOT VALUES('109','COM','SEL/MEL/SES/Instr/CFII','1','4/14/2016','4/21/2016');

/* -- */

CREATE TABLE RATING (
RTG_CODE varchar2(5),
RTG_NAME varchar2(50)
);
INSERT INTO RATING VALUES('CFI','Certified Flight Instructor');
INSERT INTO RATING VALUES('CFII','Certified Flight Instructor, Instrument');
INSERT INTO RATING VALUES('INSTR','Instrument');
INSERT INTO RATING VALUES('MEL','Multiengine Land');
INSERT INTO RATING VALUES('SEL','Single Engine, Land');
INSERT INTO RATING VALUES('SES','Single Engine, Sea');



