1. Display the age and name for the pilot who was the oldest when HIRED.

2. Using a SET OPERATOR, display the pilots and the number of miles flown as "Miles Flown", include pilots who have not yet flown (for those pilots, display “0” for miles flown”)

4. List the pilots that are paid above the average for their state and the state average pay.

5. Display the name of the pilot, pay and age of the pilot who has the maximum pay of pilots under the age of 50 (Hint, you must calculate age using pil_birthdate and sysdate).

 

Transcribed Image Text:

Flight Reservation Schema - Entity Relationship Diagram PAS ITINERARY NO PAS_NAME PAS FARE NUMBER PAS CONFIRM NO NUMBER TIC ITINERARY NO TIC FLIGHT NO TIC FLIGHT DATE TIC_SEAT pil pilot id pil pilotna me pil_ssn pil_street pil_city pil_state pil_zip pil fight pay pil_brthdate pil_hiredate Passenger Is for Pass M Ticket Pilots 1 1 Passenger reserves Is for departure Flys M M Reservation 1 Is for departure 1 Departures M Uses 1 DEP FUGHT NO DEP DEP DATE DEP_PILOT_ID DEP_EQUIP_NO RES CONFIRM NO RES_DATE RES_NAME RES_PHONE RES_FLIGHT_NO RES_FLIGHT_DATE Equip_type Fight M departures EQ EQUIP NO EQ_EQUIP TYPE EQ SEAT CAPACITY EQ FUEL CAPACITY EQ_mies_per_gal Airport Org Dest M Flight air code air_location air_elevation air_phone air_hub_airline fl flight no fl_orig fl_dest fl_orig_time fl_dest_time fl_meal fl_fare fl_time_zones Flight Reservation Schema - Entity Relationship Diagram PAS ITINERARY NO PAS_NAME PAS FARE NUMBER PAS CONFIRM NO NUMBER TIC ITINERARY NO TIC FLIGHT NO TIC FLIGHT DATE TIC_SEAT pil pilot id pil pilotna me pil_ssn pil_street pil_city pil_state pil_zip pil fight pay pil_brthdate pil_hiredate Passenger Is for Pass M Ticket Pilots 1 1 Passenger reserves Is for departure Flys M M Reservation 1 Is for departure 1 Departures M Uses 1 DEP FUGHT NO DEP DEP DATE DEP_PILOT_ID DEP_EQUIP_NO RES CONFIRM NO RES_DATE RES_NAME RES_PHONE RES_FLIGHT_NO RES_FLIGHT_DATE Equip_type Fight M departures EQ EQUIP NO EQ_EQUIP TYPE EQ SEAT CAPACITY EQ FUEL CAPACITY EQ_mies_per_gal Airport Org Dest M Flight air code air_location air_elevation air_phone air_hub_airline fl flight no fl_orig fl_dest fl_orig_time fl_dest_time fl_meal fl_fare fl_time_zones