Create a stored procedure named PRC_PIC_HOURS that takes an employee's EMP_NUM as input and updates the...
Fantastic news! We've Found the answer you've been seeking!
Question:
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)
Expert Answer:
Related Book For
Concepts of Database Management
ISBN: 978-111182591
7th edition
Authors: Philip J. Pratt, Joseph J. Adamski
Posted Date:
Students also viewed these databases questions
-
You have $7,000 excess cash to invest after you fully funded your emergency fund. You can choose to use the cash on a T-bill that can be purchased for $6,500 (maturing to value at $10,000 after one...
-
Create a stored procedure named prc_return_video enter data about the return of videos that had been rented. The procedure should satisfy the following requirements. a. The video number will be...
-
Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the...
-
The graph of g consists of two straight lines and a semicircle. Use it to evaluate each integral. (a) g(x) dr (b) g(x) dr (c) () di y=g(x)
-
Use superposition to find Io in the circuit shown 12V 2 2 2mA 2 2
-
The Skyline Motel opened for business on May 1, 2019. Its trial balance before adjustment on May 31 is as follows. In addition to those accounts listed on the trial balance, the chart of accounts for...
-
A trial weight is added to one end of a long rotor. The vibration level decreases on that end but increases on the other end, and the phase stays the same. What caused this result?
-
"Bell Company acquires 80% of Demers Company for $500,000 on January 1, 2009. Demers reported common stock of $300,000 and retained earnings of $200,000 on that date. Equipment was undervalued by...
-
How does heat transfer in porous media differ from conventional conduction and convection, and what are the key factors affecting effective thermal conductivity and dispersion in such media ?
-
1. Liam Richardson is the business manager for the Smith & Lyngate Insurance agencies in the state of Maryland. Liam is interested in increasing the number of agents in Baltimore and plans to buy...
-
The Gross Domestic Product (GDP) for the United States can be determined from totaling expenditures on goods and services produced in a year, or it can be determined by totaling the income payments...
-
How do organizations imbue their workforce with the requisite skills, mindset, and competencies to thrive in an agile environment, fostering a culture of continuous learning, experimentation, and...
-
If the demand for digital cameras increases when consumers' incomes rise, then digital cameras are what type of Good?
-
How does the ethos of organizational agility manifest within the intricate fabric of contemporary business paradigms, catalyzing a dynamic recalibration of strategic imperatives and operational...
-
A 0.240 m radius, the 570-turn coil is rotated one-fourth of a revolution in 3.45 ms, originally having its plane perpendicular to a uniform magnetic field. Find the magnetic field strength needed to...
-
What intricate mechanisms underpin the compliance infrastructure within the healthcare sector, synergizing the imperatives of patient confidentiality, data security, and regulatory adherence to...
-
we wrofit Required: 1. Using FIFO, calculate ending inventory, cost of goods sold, sales revenue, and gross profit. FIFO Beginning Inventory Purchases: Apr. 7 Jul 16 Oct 6 Total Sales revenue Gross...
-
A firm has the following balance sheet: Assets Cash Accounts receivable Inventory Plant and equipment $ 15,000 150,000 92,000 170,000 $427,000 Liabilities and Equity Accounts payable Long-term debt...
-
Create the following indexes. If it is necessary to name the index in your DBMS, use the indicated name. a. Create an index named PartIndex1 on the PartNum field in the OrderLine table. b. Create an...
-
What is XML? Why was it developed?
-
1. Create a view named PenguinBooks. It consists of the book code, book title, book type, and book price for every book published by Penguin USA. Display the data in the view. 2. Create a view named...
-
In Fig. 1130, there is no explicit invariant that states that a Transaction cannot be linked to movements that are linked to the same account, because the structure of the model already demands is....
-
Apply the Account/Transaction pattern to the parallel financial transactions of the example shown in Figs. 1131 to 1135. As the bills may be paid forward, it may be necessary to use memo entries....
-
Look at Fig. 1119 and create a new discount strategy that gives a percentage of discount for sales with more than a certain number of products. Order | < > < > +nr: Natural = < > +date: Date...
Study smarter with the SolutionInn App