NEED HELP WITH 4, 5, AND 6. ALSO I HAVE PROVIDED SQL COMMAND IN THE END FOR THE DATA.

create database assignment5;

use assignment5;

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

/* -- */

CREATE TABLE CREW ( CHAR_TRIP int, EMP_NUM int, CREW_JOB varchar(20), primary key (CHAR_TRIP, EMP_NUM) ); 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 primary key, CUS_LNAME varchar(15), CUS_FNAME varchar(15), CUS_INITIAL varchar(1), CUS_AREACODE varchar(3), CUS_PHONE varchar(8), CUS_BALANCE double ); 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.53); INSERT INTO CUSTOMER VALUES(10013,'Olowski','Paul','F','615','894-2180',1285.18); INSERT INTO CUSTOMER VALUES(10015,'O''Brian','Amy','B','713','442-3381',1014.55); 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.9);

/* -- */

CREATE TABLE EARNEDRATING ( EMP_NUM int, RTG_CODE varchar(5), EARNRTG_DATE date, primary key (emp_num, rtg_code) ); INSERT INTO EARNEDRATING VALUES(101,'CFI','1998-02-18'); INSERT INTO EARNEDRATING VALUES(101,'CFII','2005-12-15'); INSERT INTO EARNEDRATING VALUES(101,'INSTR','1993-11-08'); INSERT INTO EARNEDRATING VALUES(101,'MEL','1994-06-23'); INSERT INTO EARNEDRATING VALUES(101,'SEL','1993-04-21'); INSERT INTO EARNEDRATING VALUES(104,'INSTR','1996-07-15'); INSERT INTO EARNEDRATING VALUES(104,'MEL','1997-01-09'); INSERT INTO EARNEDRATING VALUES(104,'SEL','1995-03-12'); INSERT INTO EARNEDRATING VALUES(105,'CFI','1997-11-18'); INSERT INTO EARNEDRATING VALUES(105,'INSTR','1995-04-17'); INSERT INTO EARNEDRATING VALUES(105,'MEL','1995-08-12'); INSERT INTO EARNEDRATING VALUES(105,'SEL','1994-09-23'); INSERT INTO EARNEDRATING VALUES(106,'INSTR','1995-12-20'); INSERT INTO EARNEDRATING VALUES(106,'MEL','1996-04-02'); INSERT INTO EARNEDRATING VALUES(106,'SEL','1994-03-10'); INSERT INTO EARNEDRATING VALUES(109,'CFI','1998-11-05'); INSERT INTO EARNEDRATING VALUES(109,'CFII','2003-06-21'); INSERT INTO EARNEDRATING VALUES(109,'INSTR','1996-07-23'); INSERT INTO EARNEDRATING VALUES(109,'MEL','1997-03-15'); INSERT INTO EARNEDRATING VALUES(109,'SEL','1996-02-05'); INSERT INTO EARNEDRATING VALUES(109,'SES','1996-05-12');

/* -- */

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

/* -- */

CREATE TABLE MODEL ( MOD_CODE varchar(10) primary key, MOD_MANUFACTURER varchar(15), MOD_NAME varchar(20), MOD_SEATS double, MOD_CHG_MILE double ); INSERT INTO MODEL VALUES('C-90A','Beechcraft','KingAir',8,2.67); INSERT INTO MODEL VALUES('PA23-250','Piper','Aztec',6,1.92); INSERT INTO MODEL VALUES('PA31-350','Piper','Navajo Chieftain',10,2.34);

/* -- */

CREATE TABLE PILOT ( EMP_NUM int primary key, PIL_LICENSE varchar(25), PIL_RATINGS varchar(25), PIL_MED_TYPE varchar(1), PIL_MED_DATE date, PIL_PT135_DATE date ); INSERT INTO PILOT VALUES(101,'ATP','SEL/MEL/Instr/CFII','1','2008-04-12','2007-05-16'); INSERT INTO PILOT VALUES(104,'ATP','SEL/MEL/Instr','1','2007-06-10','2008-03-23'); INSERT INTO PILOT VALUES(105,'COM','SEL/MEL/Instr/CFI','2','2008-02-25','2008-02-12'); INSERT INTO PILOT VALUES(106,'COM','SEL/MEL/Instr','2','2008-04-02','2007-12-24'); INSERT INTO PILOT VALUES(109,'COM','SEL/MEL/SES/Instr/CFII','1','2008-04-14','2008-04-21');

/* -- */

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

Database name: Ch08 AviaCo Table name: CHARTER CHAR TRIP CHAR DATE AC NUMBER CHAR DESTNATION CHAR DISTANCE CHAR HOURS FLOWN CHAR HOURS WAT CHAR-TOT-CHC HAROL OTS CUS CODE 10011 able na me: EARNEDRATING 1000105-Feb-08 2289L 10002 05-Feb-08 2778V 10003 0S Feb 08 4278Y 10004 06-Feb-08 1484P 10005 06-Feb-08 2289L 10006 06-Feb-0 4278Y 10007 06-Feb-08 2778V 10008 07.Feb-08 1484P 10009 07-Feb-08 2289L 10010 07-Feb-08 4278Y 0011 07-Feb-08 1484P 10012 08-Feb-08 2778V 10013 08-Feb-08 4278 10014 09-Feb.08 4278Y 10015 09Feb-08 2289L 10016 09-Feb-08 2778 10017 10-Feb-08 1484P 10018 10-Feb-08 4278Y : 10016EMP 10014 10019 10011 RTG CODE EARNRTG DATE 18-Feb-98 1574 21-Apr-93 TYS 104 STR 105 EINA MOB 18-Nov-97 17-Apr.95 105 SEL 106 INSTR ATL 1055 TYS 10017 109 CF 05-Nov 98 Table name: CREW Table name: CUSTOMER SAIN-97 05-Feb-96 CHAR TRIPEMP NUM CREWCUS CODE CUS LNAME CUS FNAME CUS NITIAL CUS AREACODE CUS PHONE CUS BALANCE 844-2573 894-1238 10010 Ranas 10011 Dunne 10012 Smith 10013 oowski Pout 0014 OrlandoMyron 10015 O'EriaAmy 10016 Brown 894-2180 222-1672 896.54 128519Table name: RATING 67321 10004 RTG CODE 10006 10007 Certified Fight Instructor Certified Fight Instructor, Instrument 104 Plot 7liamsGeorge NSTRInstrument 10008 10009 10018 Fariss 10019 Smith 332-7185 297-3809 Single Engine, Land Single Engine, Sea Table name: EMPLOYEE 1104 CoplotEMP NUM EMP TITLE EMP LNAMEEMFNAME EMP NITIALEMP DOB EMP HIRE DATE 15-Mar-1987 15 Jun-1942 19-Mar-196525-Apr-1988 Table name: MODEL 14-Nov-19 e 20-Dec-1992 [MO COOET MOO MANUFACTURER IMO O NAMENCO SEATS NOO CHO ME Rhonds 102 Mr Jones 16-Oct-197428-Aug-2005 90ABeechcrat 20-0cdt-1996 PA23-250 Piper 08-Jan-2005 PA31-350 Piper WilisnsRobert 06 Mrs 107 Mr 108 Mr 109 Ms. 110 Mrs 14-Mar-1975 12-Feb-196805-Jan-1991 21-Ag,1974 02-ALI 996 14-Feb-196618-Nov-1994 18-un-1961. 1Apr-1991 10018 kziLeighls Table name: PILOT 19-May-1970 01-Dec-1992 Table name: AIRCRAFT ACJUME ERI MOO-COEL AC-TTAFT AC TTEL-C-TTER.IEMP-NUM PLtcENSE I PL -PLJED-TYF E PLM DJATE RATINGS PL.PT135-DATE 2778V PA31-350 PA31-350 7992.91513.1 2147 3 105 COM 106 COM 109 COM COMM/SELMELAnstr/CF2 COMM/SELMELAnstr 05-an-0802-Jan-00 22-Jen-08 Database name: Ch08 AviaCo Table name: CHARTER CHAR TRIP CHAR DATE AC NUMBER CHAR DESTNATION CHAR DISTANCE CHAR HOURS FLOWN CHAR HOURS WAT CHAR-TOT-CHC HAROL OTS CUS CODE 10011 able na me: EARNEDRATING 1000105-Feb-08 2289L 10002 05-Feb-08 2778V 10003 0S Feb 08 4278Y 10004 06-Feb-08 1484P 10005 06-Feb-08 2289L 10006 06-Feb-0 4278Y 10007 06-Feb-08 2778V 10008 07.Feb-08 1484P 10009 07-Feb-08 2289L 10010 07-Feb-08 4278Y 0011 07-Feb-08 1484P 10012 08-Feb-08 2778V 10013 08-Feb-08 4278 10014 09-Feb.08 4278Y 10015 09Feb-08 2289L 10016 09-Feb-08 2778 10017 10-Feb-08 1484P 10018 10-Feb-08 4278Y : 10016EMP 10014 10019 10011 RTG CODE EARNRTG DATE 18-Feb-98 1574 21-Apr-93 TYS 104 STR 105 EINA MOB 18-Nov-97 17-Apr.95 105 SEL 106 INSTR ATL 1055 TYS 10017 109 CF 05-Nov 98 Table name: CREW Table name: CUSTOMER SAIN-97 05-Feb-96 CHAR TRIPEMP NUM CREWCUS CODE CUS LNAME CUS FNAME CUS NITIAL CUS AREACODE CUS PHONE CUS BALANCE 844-2573 894-1238 10010 Ranas 10011 Dunne 10012 Smith 10013 oowski Pout 0014 OrlandoMyron 10015 O'EriaAmy 10016 Brown 894-2180 222-1672 896.54 128519Table name: RATING 67321 10004 RTG CODE 10006 10007 Certified Fight Instructor Certified Fight Instructor, Instrument 104 Plot 7liamsGeorge NSTRInstrument 10008 10009 10018 Fariss 10019 Smith 332-7185 297-3809 Single Engine, Land Single Engine, Sea Table name: EMPLOYEE 1104 CoplotEMP NUM EMP TITLE EMP LNAMEEMFNAME EMP NITIALEMP DOB EMP HIRE DATE 15-Mar-1987 15 Jun-1942 19-Mar-196525-Apr-1988 Table name: MODEL 14-Nov-19 e 20-Dec-1992 [MO COOET MOO MANUFACTURER IMO O NAMENCO SEATS NOO CHO ME Rhonds 102 Mr Jones 16-Oct-197428-Aug-2005 90ABeechcrat 20-0cdt-1996 PA23-250 Piper 08-Jan-2005 PA31-350 Piper WilisnsRobert 06 Mrs 107 Mr 108 Mr 109 Ms. 110 Mrs 14-Mar-1975 12-Feb-196805-Jan-1991 21-Ag,1974 02-ALI 996 14-Feb-196618-Nov-1994 18-un-1961. 1Apr-1991 10018 kziLeighls Table name: PILOT 19-May-1970 01-Dec-1992 Table name: AIRCRAFT ACJUME ERI MOO-COEL AC-TTAFT AC TTEL-C-TTER.IEMP-NUM PLtcENSE I PL -PLJED-TYF E PLM DJATE RATINGS PL.PT135-DATE 2778V PA31-350 PA31-350 7992.91513.1 2147 3 105 COM 106 COM 109 COM COMM/SELMELAnstr/CF2 COMM/SELMELAnstr 05-an-0802-Jan-00 22-Jen-08