Plane_new table
PNUM FEATURE PTYPE
--- -------------------- ---------------
p01 Regular Normal
p02 Multiple_en Commuter
p03 Propeller Commuter
p04 Single_en Very_light
p05 Singel_en Very_light
p06 Multiple_en Commuter
p07 Regular Normal
p08 Wildlife Restricted
p09 Multiple_en Commuter
p10 Regular Normal
Employee_new table
EID NAME SALARY MID
--- ------------- ------- -----
e01 Wilson 53000
e02 Smith 48000 e01
e03 Jones 38000 e01
e04 Loftus 41000
e05 Wang 54000 e04
e06 Smith 45000 e04
e07 Green 48000
e08 Fox 49000 e04
e09 Wolf 41000 e04
e10 Fox 32000 e01
e11 Phillips 33000 e07
e12 Liu 27000 e07
Pilot_new table
EID LICENCE
--- ---------
e01 123456789
e02 234567891
e03 345678912
e04 456789012
e05 567891234
e06 678912345
e08 890123456
e09 901223456
e10 102345678
FlightI_new table
FNUM FDATE DURATION PID PNU
---- ---------- ---------- --- ---
f01 09-SEP-11 2 e01 p01
f02 30-Sep-11 3 e02 p02
f03 15-OCT-11 4 e02 p03
f04 15-OCT-11 3 e03 p01
f05 25-OCT-11 2 e04 p04
f06 26-OCT-11 3 e05 p04
f07 26-OCT-11 4 e06 p05
f08 30-OCT-11 3 e06 p07
f09 31-OCT-11 2 e08 p09
f10 31-OCT-11 1 e02 p01
f11 01-NOV-11 4 e09 p10
f12 02-NOV-11 3 e01 p03
f13 02-NOV-11 4 e02 p04
f14 15-NOV-11 2 e03 p02
f15 15-NOV-11 2 e02 p03
Use SELECT command to display the contents of your tables.
Develop SQL statements for the given queries:
Question: List the names of pilots implementing flights on the planes of Normal type.
Question: List out plane registration numbers and features for planes which have been employed to implement more than one flight after October 15, 2011
Question: Determine and display all pilots (names only) who didnt implement a flight after October 31, 2011.
Question: For each and every plane display its plane number and the total number of hours spent in the air. Comprise planes which were not used at all. For such planes, leave the total number of hours blank.
Question: Find the name(s) of pilot(s) who implemented the longest flight.
Question: Find out the names of senior pilots (managers) whose pilots implemented flights on the planes of type Very_light.
Question: Find and exhibit all pilots (employee ID and name) who implemented flights on the planes of Commuter type and Normal type.
Question: For each and every flight, display its number, the name of the pilot who implemented the flight and the words Longer than average if the flight duration was longer than average or the words Shorter than average if the flight duration was shorter than or equivalent to the average. For column holding the words Longer than average or Shorter than average make a header Length.