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


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

/* -- */

CREATE TABLE CUSTOMER (
CUS_CODE int,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE numeric
);
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 EMPLOYEE (
EMP_NUM int,
EMP_TITLE varchar(4),
EMP_LNAME varchar(15),
EMP_FNAME varchar(15),
EMP_INITIAL varchar(1),
EMP_DOB datetime,
EMP_HIRE_DATE datetime
);
INSERT INTO EMPLOYEE VALUES('100','Mr.','Kolmycz','George','D','6/15/1942','3/15/1988');
INSERT INTO EMPLOYEE VALUES('101','Ms.','Lewis','Rhonda','G','3/19/1965','4/25/1986');
INSERT INTO EMPLOYEE VALUES('102','Mr.','VanDam','Rhett','','11/14/1958','5/18/1993');
INSERT INTO EMPLOYEE VALUES('103','Ms.','Jones','Anne','M','5/11/1974','7/26/1999');
INSERT INTO EMPLOYEE VALUES('104','Mr.','Lange','John','P','7/12/1971','8/20/1990');
INSERT INTO EMPLOYEE VALUES('105','Mr.','Williams','Robert','D','3/14/1975','6/19/2003');
INSERT INTO EMPLOYEE VALUES('106','Mrs.','Duzak','Jeanine','K','2/12/1968','3/13/1989');
INSERT INTO EMPLOYEE VALUES('107','Mr.','Diante','Jorge','D','5/1/1975','7/2/1997');
INSERT INTO EMPLOYEE VALUES('108','Mr.','Wiesenbach','Paul','R','2/14/1966','6/3/1993');
INSERT INTO EMPLOYEE VALUES('109','Ms.','Travis','Elizabeth','K','6/18/1961','2/14/2006');
INSERT INTO EMPLOYEE VALUES('110','Mrs.','Genkazi','Leighla','W','5/19/1970','6/29/1990');

/* -- */


CREATE TABLE MODEL (
MOD_CODE varchar(10),
MOD_MANUFACTURER varchar(15),
MOD_NAME varchar(20),
MOD_SEATS float(8),
MOD_CHG_MILE numeric
);
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');
INSERT INTO MODEL VALUES('B200', 'Beechcraft', 'Super KingAir', '10', '1.93');

/* -- */


CREATE TABLE PILOT (
EMP_NUM int,
PIL_LICENSE varchar(25),
PIL_RATINGS varchar(30),
PIL_MED_TYPE varchar(1),
PIL_MED_DATE datetime,
PIL_PT135_DATE datetime
);
INSERT INTO PILOT VALUES('101','ATP','ATP/SEL/MEL/Instr/CFII','1','1/20/2016','1/11/2016');
INSERT INTO PILOT VALUES('104','ATP','ATP/SEL/MEL/Instr','1','12/18/2015','1/17/2016');
INSERT INTO PILOT VALUES('105','COM','COMM/SEL/MEL/Instr/CFI','2','1/5/2016','1/2/2016');
INSERT INTO PILOT VALUES('106','COM','COMM/SEL/MEL/Instr','2','12/10/2015','2/2/2016');
INSERT INTO PILOT VALUES('109','COM','ATP/SEL/MEL/SES/Instr/CFII','1','1/22/2016','1/15/2016');

