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

/* -- */

CREATE TABLE CREW (
CHAR_TRIP int,
EMP_NUM int,
CREW_JOB nvarchar(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 int,
CUS_LNAME nvarchar(15),
CUS_FNAME nvarchar(15),
CUS_INITIAL nvarchar(1),
CUS_AREACODE nvarchar(3),
CUS_PHONE nvarchar(8),
CUS_BALANCE float
);
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.539978027344');
INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','1285.18994140625');
INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','673.210021972656');
INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','1014.55999755859');
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.980010986328');

/* -- */

CREATE TABLE EARNEDRATING (
EMP_NUM int,
RTG_CODE nvarchar(5),
EARNRTG_DATE datetime
);
INSERT INTO EARNEDRATING VALUES('101','CFI','2/18/1998');
INSERT INTO EARNEDRATING VALUES('101','CFII','12/15/2005');
INSERT INTO EARNEDRATING VALUES('101','INSTR','11/8/1993');
INSERT INTO EARNEDRATING VALUES('101','MEL','6/23/1994');
INSERT INTO EARNEDRATING VALUES('101','SEL','4/21/1993');
INSERT INTO EARNEDRATING VALUES('104','INSTR','7/15/1996');
INSERT INTO EARNEDRATING VALUES('104','MEL','1/29/1997');
INSERT INTO EARNEDRATING VALUES('104','SEL','3/12/1995');
INSERT INTO EARNEDRATING VALUES('105','CFI','11/18/1997');
INSERT INTO EARNEDRATING VALUES('105','INSTR','4/17/1995');
INSERT INTO EARNEDRATING VALUES('105','MEL','8/12/1995');
INSERT INTO EARNEDRATING VALUES('105','SEL','9/23/1994');
INSERT INTO EARNEDRATING VALUES('106','INSTR','12/20/1995');
INSERT INTO EARNEDRATING VALUES('106','MEL','4/2/1996');
INSERT INTO EARNEDRATING VALUES('106','SEL','3/10/1994');
INSERT INTO EARNEDRATING VALUES('109','CFI','11/5/1998');
INSERT INTO EARNEDRATING VALUES('109','CFII','6/21/2003');
INSERT INTO EARNEDRATING VALUES('109','INSTR','7/23/1996');
INSERT INTO EARNEDRATING VALUES('109','MEL','3/15/1997');
INSERT INTO EARNEDRATING VALUES('109','SEL','2/5/1996');
INSERT INTO EARNEDRATING VALUES('109','SES','5/12/1996');

/* -- */

CREATE TABLE EMPLOYEE (
EMP_NUM int,
EMP_TITLE nvarchar(4),
EMP_LNAME nvarchar(15),
EMP_FNAME nvarchar(15),
EMP_INITIAL nvarchar(1),
EMP_DOB datetime,
EMP_HIRE_DATE datetime
);
INSERT INTO EMPLOYEE VALUES('100','Mr.','Kolmycz','George','D','6/15/1942','3/15/1987');
INSERT INTO EMPLOYEE VALUES('101','Ms.','Lewis','Rhonda','G','3/19/1965','4/25/1988');
INSERT INTO EMPLOYEE VALUES('102','Mr.','VanDam','Rhett','','11/14/1958','12/20/1992');
INSERT INTO EMPLOYEE VALUES('103','Ms.','Jones','Anne','M','10/16/1974','8/28/2005');
INSERT INTO EMPLOYEE VALUES('104','Mr.','Lange','John','P','11/8/1971','10/20/1996');
INSERT INTO EMPLOYEE VALUES('105','Mr.','Williams','Robert','D','3/14/1975','1/8/2006');
INSERT INTO EMPLOYEE VALUES('106','Mrs.','Duzak','Jeanine','K','2/12/1968','1/5/1991');
INSERT INTO EMPLOYEE VALUES('107','Mr.','Diante','Jorge','D','8/21/1974','7/2/1996');
INSERT INTO EMPLOYEE VALUES('108','Mr.','Wiesenbach','Paul','R','2/14/1966','11/18/1994');
INSERT INTO EMPLOYEE VALUES('109','Ms.','Travis','Elizabeth','K','6/18/1961','4/14/1991');
INSERT INTO EMPLOYEE VALUES('110','Mrs.','Genkazi','Leighla','W','5/19/1970','12/1/1992');

/* -- */

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

/* -- */

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

/* -- */

CREATE TABLE RATING (
RTG_CODE nvarchar(5),
RTG_NAME nvarchar(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');