Create a stored procedure named PRC_PIC_HOURS that takes an employee's EMP_NUM as input and updates the...

    

Transcribed Image Text:

Create a stored procedure named PRC_PIC_HOURS that takes an employee's EMP_NUM as input and updates the newly added PIL_PIC_HRS column in the PILOT table (from Q.4 above) with the total hours flown for all charters in which the employee has been part of the crew as a "Pilot". Hint: Use the CHARTER table's CHAR_HOURS_FLOWN to update the PILOT table's PIL_PIC_HRS only when the CREW table uses a 'Pilot' CREW JOB entry for that employee. Attribute name PIL_PIC_HRS Attribute Description Pilot in command (PIC) hours. Updated by adding the CHARTER table's CHAR_HOURS FLOWN to the PIL PIC_HRS when the CREW table shows the CREW JOB to be "Pilot". Default value is 0. Attribute type float(8) Table 2: New Attributes for the CHARTER Table Attribute name CHAR_WAIT_CHG CHAR_FLT_CHG_HR CHAR_FLT_CHG CHAR_TAX_CHG CHAR_TOT_CHG CHAR_PYMT CHAR_BALANCE Attribute Description Waiting charge for each model (copied from the MODEL table.) Flight charge per mile for each model (copied from the MODEL table using the MOD_CHG_MILE attribute.) Flight charge (calculated by CHAR_HOURS_FLOWN x CHAR_FLT_CHG_HR) CHAR_FLT_CHG * tax rate (15%) CHAR_FLT_CHG+ CHAR_TAX_CHG Amount paid by customer Balance remaining after payment Attribute type float(8) float(8) float(8) float(8) float(8) float(8) float(8) Table name: CHARTER CHAR_TRIP CHAR_DATE AC NUMBER CHAR_DESTINATION CHAR_DISTANCE CHAR_HOURS FLOWN CHAR_HOURS WAIT CHAR_FUEL GALLONS CHAR_OL_QTS CUS_CODE Table name: EARNEDRATING 10001 05-Feb-16 2289L 2.2 0 10002 05-Feb-16 2778V 10003 05-Feb-16 4278Y 0 4.9 3.5 06-Feb-16 4278Y 5.2 06-Feb-16 2778V 0 10004 06-Feb-16 1484P 10005 06-Feb-16 2289L 10006 10007 10008 07-Feb-16 1484P 10009 07-Feb-16 2289L 10010 07-Feb-16 4278Y 10011 07-Feb-16 1484P 10012 08-Feb-16 2778V 10013 08-Feb-16 4278Y 10014 09-Feb-16 4278Y 10015 09-Feb-16 2289L 10016 09-Feb-16 2778V 10017 10-Feb-16 1484P 10018 10-Feb-16 4278Y Table name: CREW 10007 10007 10008 10009 CHAR_TRIP EMP_NUM CREW JOB 10001 104 Pilot 10002 101 Pilot 10003 105 Pilot 10003 109 Copilot 10004 106 Pilot 10005 101 Pilot 10005 www 10006 10010 10011 10011 10012 10013 10014 10015 10015 10016 10016 10017 10018 10018 109 Pilot 104 Pilot 105 Copilot 106 Pilot 105 Pilot 108 Pilot 101 Pilot 104 Copilot 101 Pilot ATL BNA GNV STL ATL STL GNV TYS GNV ATL BNA MOB TYS ATL 105 Pilot 106 Pilot 101 Copilot 104 Pilot 105 Copilot 109 Pilot 101 Pilot 104 Copilot 105 Pilot GNV ΜΟΥ STL TYS Table name: AIRCRAFT AC NUMBER MOD CODE AC TTAF 1484P PA23-250 2289L 1833.1 C-90A 2778V PA31-350 4278Y PA31-350 10010 Ramas 10011 Dunne 10012 Smith 10013 Olowski 10014 Orlando 10015 O'Brian 10016 Brown 10017 Williams 10018 Farriss 10019 Smith 4243.8 7992.9 2147.3 100 Mr. 101 Ms. 102 Mr. 103 Ms. 104 Mr. 105 Mr. 106 Mrs. 107 Mr. 108 Mr. 109 Ms. 110 Mrs. 936 320 1574 472 1023 472 1574 644 1574 998 352 884 644 936 1645 312 508 644 AC_TTEL AC_TTER 1833.1 768.9 1513.1 622.1 Alfred Leona Kathy Paul Myron Amy James George Anne Olette Kolmycz Lewis Vandam Jones Lange Williams Duzak Diante Wesenbach Travis Genkazi 101.8 1123.4 789.5 243.2 B G G K 5.1 1.6 78 2.9 5.7 2.6 7.9 4.1 6.6 Table name: CUSTOMER CUS_CODE CUS_LNAME CUS_FNAME CUS INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE A 844-2573 K 894-1238 W 894-2285 F 894-2180 Table name: EMPLOYEE EMP_NUM EMP_TITLE EMP_LNAME EMP_FNAME EMP_INITIAL George Rhonda Rhett Anne John Robert Jeanine Jorge Paul Elizabeth Leighla 6.2 1.9 48 3.9 6.1 6.7 1.5 3.1 3.8 615 713 615 615 615 713 615 615 713 615 D G M P D K D R K W 0 Table name: PILOT EMP_NUM PIL LICENSE 101 ATP 104 ATP 105 COM 106 COM 109 COM 23.4 3.2 5.3 42 4.5 2.1 0 0 0 4.5 222-1672 442-3381 297-1228 290-2556 382-7185 297-3809 EMP_DOB 15-Jun-1942 19-Mar-1965 14-Nov-1958 16-Oct-1974 08-Nov-1971 14-Mar-1975 12-Feb-1968 21-Aug-1974 14-Feb-1966 18-Jun-1961 19-May-1970 354.1 72.6 339.8 97.2 397.7 117.1 348.4 140.6 459.9 279.7 66.4 215.1 174.3 302.6 459.5 67.2 105.5 167.4 1 PIL RATINGS ATP/SEL/MELAnstr/CFI ATP/SELMELAnstr COMMISELMELAnstr/CFI COMMISELMELAnstr 2 ATP/SELMEL/SES/Anstr/CFII 1 1 2 0.00 0.00 896.54 1285.19 673.21 1014.56 0.00 0.00 0.00 453.98 1 0 2 1 2 0 2 1 0 0 1 0 1 0 2 0 0 0 10011 10016 EMP NUM RTG CODE EARNRTO DATE 18-Feb-98 15-Dec-05 08-Nov-93 101 CFI 101 CFI 101 INSTR www 101 MEL FALCO 101 SEL Farma 104 INSTR 23-Jun-94 10014 10019 10011 10017 10012 10014 10017 10016 10012 10010 10011 10017 104 MEL 104 SEL 105 CFI TAPA 105 INSTR www.w 105 MEL 105 SEL 100 Mies 106 100 LAS 106 MEL 100 MEL 10016 10011 10014 10017 CFI CFII INSTR MEL SEL SES INSTR PIL_MED_TYPE PIL_MED_DATE PIL_PT135_DATE 20-Jan-16 18-Dec-15 05-Jan-16 10-Dec-15 22-Jan-16 106 SEL 100 SEL 109 CFI 109 CF 109 INSTR 109 MEL 109 SEL 109 SES Single Engine, Land Single Engine, Sea Table name: RATING RTG_CODE RTG NAME Certified Flight Instructor Certified Flight Instructor, Instrument Instrument Multiengine Land 11-Jan-16 17-Jan-16 02-Jan-16 02-Feb-16 15-Jan-16 21-Apr-93 *** 15-Jul-96 29-Jan-97 12-Mar-95 me EMPHIRE_DATE 15-Mar-1987 25-Apr-1988 Table name: MODEL 20-Dec-1992 MOD_CODE MOD MANUFACTURER MOD NAME MOD SEATS MOD CHG_MILE 28-Aug-2005 C-90A Beechcraft 20-Oct-1996 PA23-250 PA23-250 Piper 08-Jan-2006 PA31-350 Piper 05-Jan-1991 02-Jul-1996 18-Nov-1994 14-Apr-1991 01-Dec-1992 18-Nov-97 AT AWA 17-Apr-95 www.dess 12-Aug-95 23-Sep-94 20-Dec-95 CORVOON 02-Apr-96 dempriso 10-Mar-94 05-Nov-98 US-NOV-90 21-Jun-03 23-Jul-96 KingAir Aztec Navajo Chieftain 15-Mar-97 05-Feb-96 12-May-96 8 6 10 2.67 1.93 2.35 Table 2: New Attributes for the CHARTER Table Attribute name CHAR_WAIT_CHG CHAR_FLT_CHG_HR CHAR_FLT_CHG CHAR_TAX_CHG CHAR_TOT_CHG CHAR_PYMT CHAR_BALANCE Attribute Description Waiting charge for each model (copied from the MODEL table.) Flight charge per mile for each model (copied from the MODEL table using the MOD_CHG_MILE attribute.) Flight charge (calculated by CHAR_HOURS_FLOWN x CHAR_FLT_CHG_HR) CHAR_FLT_CHG * tax rate (15%) CHAR_FLT_CHG+ CHAR_TAX_CHG Amount paid by customer Balance remaining after payment Attribute type float(8) float(8) float(8) float(8) float(8) float(8) float(8) Create a stored procedure named PRC_PIC_HOURS that takes an employee's EMP_NUM as input and updates the newly added PIL_PIC_HRS column in the PILOT table (from Q.4 above) with the total hours flown for all charters in which the employee has been part of the crew as a "Pilot". Hint: Use the CHARTER table's CHAR_HOURS_FLOWN to update the PILOT table's PIL_PIC_HRS only when the CREW table uses a 'Pilot' CREW JOB entry for that employee. Attribute name PIL_PIC_HRS Attribute Description Pilot in command (PIC) hours. Updated by adding the CHARTER table's CHAR_HOURS FLOWN to the PIL PIC_HRS when the CREW table shows the CREW JOB to be "Pilot". Default value is 0. Attribute type float(8) Table 2: New Attributes for the CHARTER Table Attribute name CHAR_WAIT_CHG CHAR_FLT_CHG_HR CHAR_FLT_CHG CHAR_TAX_CHG CHAR_TOT_CHG CHAR_PYMT CHAR_BALANCE Attribute Description Waiting charge for each model (copied from the MODEL table.) Flight charge per mile for each model (copied from the MODEL table using the MOD_CHG_MILE attribute.) Flight charge (calculated by CHAR_HOURS_FLOWN x CHAR_FLT_CHG_HR) CHAR_FLT_CHG * tax rate (15%) CHAR_FLT_CHG+ CHAR_TAX_CHG Amount paid by customer Balance remaining after payment Attribute type float(8) float(8) float(8) float(8) float(8) float(8) float(8) Table name: CHARTER CHAR_TRIP CHAR_DATE AC NUMBER CHAR_DESTINATION CHAR_DISTANCE CHAR_HOURS FLOWN CHAR_HOURS WAIT CHAR_FUEL GALLONS CHAR_OL_QTS CUS_CODE Table name: EARNEDRATING 10001 05-Feb-16 2289L 2.2 0 10002 05-Feb-16 2778V 10003 05-Feb-16 4278Y 0 4.9 3.5 06-Feb-16 4278Y 5.2 06-Feb-16 2778V 0 10004 06-Feb-16 1484P 10005 06-Feb-16 2289L 10006 10007 10008 07-Feb-16 1484P 10009 07-Feb-16 2289L 10010 07-Feb-16 4278Y 10011 07-Feb-16 1484P 10012 08-Feb-16 2778V 10013 08-Feb-16 4278Y 10014 09-Feb-16 4278Y 10015 09-Feb-16 2289L 10016 09-Feb-16 2778V 10017 10-Feb-16 1484P 10018 10-Feb-16 4278Y Table name: CREW 10007 10007 10008 10009 CHAR_TRIP EMP_NUM CREW JOB 10001 104 Pilot 10002 101 Pilot 10003 105 Pilot 10003 109 Copilot 10004 106 Pilot 10005 101 Pilot 10005 www 10006 10010 10011 10011 10012 10013 10014 10015 10015 10016 10016 10017 10018 10018 109 Pilot 104 Pilot 105 Copilot 106 Pilot 105 Pilot 108 Pilot 101 Pilot 104 Copilot 101 Pilot ATL BNA GNV STL ATL STL GNV TYS GNV ATL BNA MOB TYS ATL 105 Pilot 106 Pilot 101 Copilot 104 Pilot 105 Copilot 109 Pilot 101 Pilot 104 Copilot 105 Pilot GNV ΜΟΥ STL TYS Table name: AIRCRAFT AC NUMBER MOD CODE AC TTAF 1484P PA23-250 2289L 1833.1 C-90A 2778V PA31-350 4278Y PA31-350 10010 Ramas 10011 Dunne 10012 Smith 10013 Olowski 10014 Orlando 10015 O'Brian 10016 Brown 10017 Williams 10018 Farriss 10019 Smith 4243.8 7992.9 2147.3 100 Mr. 101 Ms. 102 Mr. 103 Ms. 104 Mr. 105 Mr. 106 Mrs. 107 Mr. 108 Mr. 109 Ms. 110 Mrs. 936 320 1574 472 1023 472 1574 644 1574 998 352 884 644 936 1645 312 508 644 AC_TTEL AC_TTER 1833.1 768.9 1513.1 622.1 Alfred Leona Kathy Paul Myron Amy James George Anne Olette Kolmycz Lewis Vandam Jones Lange Williams Duzak Diante Wesenbach Travis Genkazi 101.8 1123.4 789.5 243.2 B G G K 5.1 1.6 78 2.9 5.7 2.6 7.9 4.1 6.6 Table name: CUSTOMER CUS_CODE CUS_LNAME CUS_FNAME CUS INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE A 844-2573 K 894-1238 W 894-2285 F 894-2180 Table name: EMPLOYEE EMP_NUM EMP_TITLE EMP_LNAME EMP_FNAME EMP_INITIAL George Rhonda Rhett Anne John Robert Jeanine Jorge Paul Elizabeth Leighla 6.2 1.9 48 3.9 6.1 6.7 1.5 3.1 3.8 615 713 615 615 615 713 615 615 713 615 D G M P D K D R K W 0 Table name: PILOT EMP_NUM PIL LICENSE 101 ATP 104 ATP 105 COM 106 COM 109 COM 23.4 3.2 5.3 42 4.5 2.1 0 0 0 4.5 222-1672 442-3381 297-1228 290-2556 382-7185 297-3809 EMP_DOB 15-Jun-1942 19-Mar-1965 14-Nov-1958 16-Oct-1974 08-Nov-1971 14-Mar-1975 12-Feb-1968 21-Aug-1974 14-Feb-1966 18-Jun-1961 19-May-1970 354.1 72.6 339.8 97.2 397.7 117.1 348.4 140.6 459.9 279.7 66.4 215.1 174.3 302.6 459.5 67.2 105.5 167.4 1 PIL RATINGS ATP/SEL/MELAnstr/CFI ATP/SELMELAnstr COMMISELMELAnstr/CFI COMMISELMELAnstr 2 ATP/SELMEL/SES/Anstr/CFII 1 1 2 0.00 0.00 896.54 1285.19 673.21 1014.56 0.00 0.00 0.00 453.98 1 0 2 1 2 0 2 1 0 0 1 0 1 0 2 0 0 0 10011 10016 EMP NUM RTG CODE EARNRTO DATE 18-Feb-98 15-Dec-05 08-Nov-93 101 CFI 101 CFI 101 INSTR www 101 MEL FALCO 101 SEL Farma 104 INSTR 23-Jun-94 10014 10019 10011 10017 10012 10014 10017 10016 10012 10010 10011 10017 104 MEL 104 SEL 105 CFI TAPA 105 INSTR www.w 105 MEL 105 SEL 100 Mies 106 100 LAS 106 MEL 100 MEL 10016 10011 10014 10017 CFI CFII INSTR MEL SEL SES INSTR PIL_MED_TYPE PIL_MED_DATE PIL_PT135_DATE 20-Jan-16 18-Dec-15 05-Jan-16 10-Dec-15 22-Jan-16 106 SEL 100 SEL 109 CFI 109 CF 109 INSTR 109 MEL 109 SEL 109 SES Single Engine, Land Single Engine, Sea Table name: RATING RTG_CODE RTG NAME Certified Flight Instructor Certified Flight Instructor, Instrument Instrument Multiengine Land 11-Jan-16 17-Jan-16 02-Jan-16 02-Feb-16 15-Jan-16 21-Apr-93 *** 15-Jul-96 29-Jan-97 12-Mar-95 me EMPHIRE_DATE 15-Mar-1987 25-Apr-1988 Table name: MODEL 20-Dec-1992 MOD_CODE MOD MANUFACTURER MOD NAME MOD SEATS MOD CHG_MILE 28-Aug-2005 C-90A Beechcraft 20-Oct-1996 PA23-250 PA23-250 Piper 08-Jan-2006 PA31-350 Piper 05-Jan-1991 02-Jul-1996 18-Nov-1994 14-Apr-1991 01-Dec-1992 18-Nov-97 AT AWA 17-Apr-95 www.dess 12-Aug-95 23-Sep-94 20-Dec-95 CORVOON 02-Apr-96 dempriso 10-Mar-94 05-Nov-98 US-NOV-90 21-Jun-03 23-Jul-96 KingAir Aztec Navajo Chieftain 15-Mar-97 05-Feb-96 12-May-96 8 6 10 2.67 1.93 2.35 Table 2: New Attributes for the CHARTER Table Attribute name CHAR_WAIT_CHG CHAR_FLT_CHG_HR CHAR_FLT_CHG CHAR_TAX_CHG CHAR_TOT_CHG CHAR_PYMT CHAR_BALANCE Attribute Description Waiting charge for each model (copied from the MODEL table.) Flight charge per mile for each model (copied from the MODEL table using the MOD_CHG_MILE attribute.) Flight charge (calculated by CHAR_HOURS_FLOWN x CHAR_FLT_CHG_HR) CHAR_FLT_CHG * tax rate (15%) CHAR_FLT_CHG+ CHAR_TAX_CHG Amount paid by customer Balance remaining after payment Attribute type float(8) float(8) float(8) float(8) float(8) float(8) float(8)