Below is the relevant text needed

/* Database Systems, 8th Ed., Rob/Coronel */ /* 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, 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 integer, CUS_CODE int ); INSERT INTO CHARTER VALUES('10001','2/5/2012','2289L','ATL','936','5.1','2.2','354.1','1','10011'); INSERT INTO CHARTER VALUES('10002','2/5/2013','2778V','BNA','320','1.6','0','72.6','0','10016'); INSERT INTO CHARTER VALUES('10003','2/5/2012','4278Y','GNV','1574','7.8','0','339.8','2','10014'); INSERT INTO CHARTER VALUES('10004','2/6/2012','1484P','STL','472','2.9','4.9','97.2','1','10019'); INSERT INTO CHARTER VALUES('10005','2/6/2012','2289L','ATL','1023','5.7','3.5','397.7','2','10011'); INSERT INTO CHARTER VALUES('10006','2/6/2012','4278Y','STL','472','2.6','5.2','117.1','0','10017'); INSERT INTO CHARTER VALUES('10007','2/6/2012','2778V','GNV','1574','7.9','0','348.4','2','10012'); INSERT INTO CHARTER VALUES('10008','2/7/2012','1484P','TYS','644','4.1','0','140.6','1','10014'); INSERT INTO CHARTER VALUES('10009','2/7/2012','2289L','GNV','1574','6.6','23.4','459.9','0','10017'); INSERT INTO CHARTER VALUES('10010','2/7/2012','4278Y','ATL','998','6.2','3.2','279.7','0','10016'); INSERT INTO CHARTER VALUES('10011','2/7/2012','1484P','BNA','352','1.9','5.3','66.4','1','10012'); INSERT INTO CHARTER VALUES('10012','2/8/2012','2778V','MOB','884','4.8','4.2','215.1','0','10010'); INSERT INTO CHARTER VALUES('10013','2/8/2012','4278Y','TYS','644','3.9','4.5','174.3','1','10011'); INSERT INTO CHARTER VALUES('10014','2/9/2012','4278Y','ATL','936','6.1','2.1','302.6','0','10017'); INSERT INTO CHARTER VALUES('10015','2/9/2012','2289L','GNV','1645','6.7','0','459.5','2','10016'); INSERT INTO CHARTER VALUES('10016','2/9/2012','2778V','MQY','312','1.5','0','67.2','0','10011'); INSERT INTO CHARTER VALUES('10017','2/10/2012','1484P','STL','508','3.1','0','105.5','0','10014'); INSERT INTO CHARTER VALUES('10018','2/10/2012','4278Y','TYS','644','3.8','4.5','167.4','0','10017');

/* -- */

CREATE TABLE CREW ( CHAR_TRIP int, EMP_NUM int, CREW_JOB varchar(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 varchar(15), CUS_FNAME varchar(15), CUS_INITIAL varchar(1), CUS_AREACODE varchar(3), CUS_PHONE varchar(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 int, RTG_CODE varchar(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 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/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 varchar(10), MOD_MANUFACTURER varchar(15), MOD_NAME varchar(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 int, PIL_LICENSE varchar(25), PIL_RATINGS varchar(25), PIL_MED_TYPE varchar(1), PIL_MED_DATE datetime, PIL_PT135_DATE datetime ); INSERT INTO PILOT VALUES('101','ATP','SEL/MEL/Instr/CFII','1','4/12/2012','6/15/2011'); INSERT INTO PILOT VALUES('104','ATP','SEL/MEL/Instr','1','6/10/2011','3/23/2012'); INSERT INTO PILOT VALUES('105','COM','SEL/MEL/Instr/CFI','2','2/25/2012','2/12/2012'); INSERT INTO PILOT VALUES('106','COM','SEL/MEL/Instr','2','4/2/2012','12/24/2011'); INSERT INTO PILOT VALUES('109','COM','SEL/MEL/SES/Instr/CFII','1','4/14/2012','4/21/2012');

/* -- */

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

1. Write the SQL code that will select the MOD_CODE and MOD_MANUFACTURER from the MODEL table where the MOD_CODE starts with " C ". 2. Write the SQL code that will select the EMP_NUM, EMP_LNAME and EMP_FNAME from the EMPLOYEE table where the EMP_FNAME is only five characters in length. 3. Using the data in the CHARTER table, write the SQL code that will yield the sum CHAR_DISTANCE grouped by CHAR_DESTINATION. The results of running this query are shown below. 4. Using the data in the CHARTER table, write a query that will list the CHAR_DATE and CHAR_DESTINATION and a computed column for the total changes that is calculated by CHAR_HOURS_FLOWN * 1.29. 5. Write the SQL code required to list the MOD_CODE, MOD_NAME from the MODEL table where the MOD_NAME contains the word "Air". 6. Write the SQL code required to list the sum of CHAR_DISTANCE from the CHARTER table where the CHAR_DESTINATION is "ATL". The results of running this query are shown below. 7. Write the SQL code required to list the CHAR_DATE, CHAR_DESTINATION, CHAR_DISTANCE from the CHARTER table where the CHAR_DISTANCE is greater than 500 order by the CHAR_DISTANCE in descending order. 8. Write the SQL code required to list the distinct CHAR_DESTINATION values (hint: research distinct) 9. Write the SQL code required to list the EMP_NUM and EARNRTG_DATE from the EARNEDRATING table where the RTG_CODE is equal to "CFI" and EMP_NUM is equal to 105. 0. Write the SQL code required to list the EMP_TITLE and RTG_CODE from joining the EARNEDRATING and EMPLOYEE tables