Write a SQL code that returns the name of aircraft (MOD_NAME in MODEL table), number of trips each mode flied (based on the CHARTER table, name this Trip_Num), and Total distance of each mode (based on the CHARTER table, name this Total_Distance).

Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
icon
Related questions
Question

Write a SQL code that returns the name of aircraft (MOD_NAME in MODEL table), number of trips each mode flied (based on the CHARTER table, name this Trip_Num), and Total distance of each mode (based on the CHARTER table, name this Total_Distance). 

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 2289
10002 05-Feb-16 2778V
10003 05-Feb-16 4278Y
10004 06-Feb-16 1484P
10005 06-Feb-16 2289
10006 06-Feb-16 4278Y
10007
06-Feb-16 2778V
07-Feb-16 1484P
10008
10009
07-Feb-16 2289L
10010
07-Feb-16 4278Y
10011
07-Feb-16 1484P
10012 08-Feb-16 2778V
08-Feb-16 4278Y
10013
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
CHAR_TRIP EMP NUM CREW JOB
10001
104 P
10002
101 P
10003
105 P
10003
10004
10005
10006
10007
10007
10008
10009
10010
10011
10011
10012
10013
10014
10015
10015
10016
10016
10017
10018
10018
109 Copilot
106 Pilot
101 P
109 Pilot
104 P
105 Cop
106 Plot
105 Pilot
100 Plot
101 P
104 Cop
101 Po
105 P
106 Pilot
101 Cop
104 P
105 Cop
109 P
101 P
104 Copilot
105 Pilot
ATL
BNA
GNV
STL
ATL
STL
GNV
TYS
GNV
ATL
BNA
MOB
TYS
ATL
GNV
MOY
STL
TVS
10010 Ramas
10011 Dunne
10012 Smith
Table name: AIRCRAFT
AC_NUMBER MOD_CODE AC TTAF
1484P
PA23-250
2209L
C-90A
2778V
PA31-350
4278Y
PA31-350
10013 Clowski
10014 Orlando
10015 O'Brian
10016 Brown
10017 Williams
10018 Farriss
10019 Smith
106 Mrs.
107 Mr.
108 Mr.
109 Ms.
110 Mrs.
AC_TTEL AC_TTER
936
320
1574
472
1023
472
1574
644
1574
990
352
884
644
936
1645
312
508
644
1833.1 1833.1
4243.0
768.9
79929
2147.3
1513.1
622.1
Alfred
Leona
Kathy
Paul
Myron
Amy
James
George
Anne
Olette
101.8
1123.4
789.5
243.2
A
K
W
F
B
K
5.1
1.6
78
2.9
5.7
26
79
Table name: CUSTOMER
CUS_CODE CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE
844-2573
894-1238
894-2285
Table name: EMPLOYEE
EMP NUM EMP TITLE EMP LNAME EMP_FNAME EMP_INITIAL
100 Mr.
Kolmycz
Lewis
101 Ms.
102 Mr
Vandam
103 Ms.
Jones
104 Mr.
Lange
105 Mr.
Williams
Duzak
Diante
Wesenbach
Travis
Genkazi
George
Rhonda
Rhett
Anne
John
Robert
Jeanine
Jorge
Paul
Elizabeth
Leighia
6.6
6.2
1.9
3.9
6.1
6.7
15
3.1
3.8
615
713
615
615
615
713
615
615
713
615
D
0
M
P
D
K
D
R
K
W
22
0
0
Table name: PILOT
EMP_NUM PIL LICENSE
101 ATP
104 ATP
105 COM
106 COM
109 COM
4.9
3.5
5.2
0
0
23.4
3.2
5.3
42
4.5
2.1
0
0
0
4.5
894-2180
222-1672
442-3381
297-1228
290-2556
382-7185
297-3809
354.1
726
18-Jun-1961
19-May-1970
339.8
97.2
117.1
348.4
140.6
459.9
279.7
66.4
1
PL_RATINGS
ATP/SELMELAnstr/CFI
ATP/SELMELAnstr
COMM/SELMELA/CF
COMMISELMELAnstr
ATPISELMEL/SESAnstr/CF 1
1
2
2
215.1
174.3
302.6
459.5
672
105.5
167 A
0.00
0.00
896.54
1285.19
673.21
1014.56
0.00
0.00
0.00
453.98
EMP DOB
15-Jun-1942
19-Mar-1965
14-Nov-1958 20-Dec-1992
16-Oct-1974 28-Aug-2005
08-Nov-1971 20-Oct-1996
14-Mar-1975
12-Feb-1968
21-Aug-1974
14-Feb-1966
02-Ju-1996
18-Nov-1994
1
14-Apr-1991
01-Dec-1992
0
2
1
2
0
2
1
0
0
1
0
1
08-Jan-2006 PA31-350
05-Jan-1991
0
2
0
0
0
CFI
CFI
INSTR
MEL
SEL
SES
10011
10016 EMP_NUM RTG_CODE EARNRTO_DATE
10014
10019
10011
10017
10012
10014
10017
10016
10012
10010
10011
10017
10016
10011
10014
10017
EMP HIRE DATE
15-Mar-1987
25-Apr-1988 Table name: MODEL
Table name: RATING
RTG_CODE
RTO NAME
Certified Flight Instructor
Certified Flight Instructor, Instrument
Instrument
Mutiengine Land
PIL_MED_TYPE PIL_MED_DATE PL_PT135_DATE
20-Jan-16
18-Dec-15
05-Jan-16
10-Dec-15
22-Jan-16
101 CFI
101 CFI
101 INSTR
101 MEL
101 SEL
104 INSTR
104 MEL
104 SEL
105 CFI
105 INSTR
105 MEL
105 SEL
106 INSTR
106 MEL
106 SEL
109 CFI
109 CF
Single Engine, Land
Single Engine, Sea
109 INSTR
109 MEL
109 SEL
109 SES
11-Jan-16
17-Jan-16
02-Jan-16
02-Feb-16
15-Jan-16
MOD_CODE MOD_MANUFACTURER MOD_NAME MOD_SEATS MOD_CHO_MILE
C-90A Beechcraft
PA23-250 Piper
Piper
18-Feb-98
15-Dec-05
06-Nov-93
23-Jun-94
21-Apr-90
15-JM-96
29-Jan-97
12-Mar-95
18-Nov-97
17-Apr-95
12-Aug-95
23-Sep-94
KingAir
Aztec
Navajo Chieftain
20-Dec-95
02-Apr-96
10-Mar-94
05-Nov-90
21-Jun-03
23-4-96
15-Mar-97
05-Feb-96
12-May-96
8
6
10
2.67
1.93
2.35
Transcribed Image Text: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 2289 10002 05-Feb-16 2778V 10003 05-Feb-16 4278Y 10004 06-Feb-16 1484P 10005 06-Feb-16 2289 10006 06-Feb-16 4278Y 10007 06-Feb-16 2778V 07-Feb-16 1484P 10008 10009 07-Feb-16 2289L 10010 07-Feb-16 4278Y 10011 07-Feb-16 1484P 10012 08-Feb-16 2778V 08-Feb-16 4278Y 10013 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 CHAR_TRIP EMP NUM CREW JOB 10001 104 P 10002 101 P 10003 105 P 10003 10004 10005 10006 10007 10007 10008 10009 10010 10011 10011 10012 10013 10014 10015 10015 10016 10016 10017 10018 10018 109 Copilot 106 Pilot 101 P 109 Pilot 104 P 105 Cop 106 Plot 105 Pilot 100 Plot 101 P 104 Cop 101 Po 105 P 106 Pilot 101 Cop 104 P 105 Cop 109 P 101 P 104 Copilot 105 Pilot ATL BNA GNV STL ATL STL GNV TYS GNV ATL BNA MOB TYS ATL GNV MOY STL TVS 10010 Ramas 10011 Dunne 10012 Smith Table name: AIRCRAFT AC_NUMBER MOD_CODE AC TTAF 1484P PA23-250 2209L C-90A 2778V PA31-350 4278Y PA31-350 10013 Clowski 10014 Orlando 10015 O'Brian 10016 Brown 10017 Williams 10018 Farriss 10019 Smith 106 Mrs. 107 Mr. 108 Mr. 109 Ms. 110 Mrs. AC_TTEL AC_TTER 936 320 1574 472 1023 472 1574 644 1574 990 352 884 644 936 1645 312 508 644 1833.1 1833.1 4243.0 768.9 79929 2147.3 1513.1 622.1 Alfred Leona Kathy Paul Myron Amy James George Anne Olette 101.8 1123.4 789.5 243.2 A K W F B K 5.1 1.6 78 2.9 5.7 26 79 Table name: CUSTOMER CUS_CODE CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE 844-2573 894-1238 894-2285 Table name: EMPLOYEE EMP NUM EMP TITLE EMP LNAME EMP_FNAME EMP_INITIAL 100 Mr. Kolmycz Lewis 101 Ms. 102 Mr Vandam 103 Ms. Jones 104 Mr. Lange 105 Mr. Williams Duzak Diante Wesenbach Travis Genkazi George Rhonda Rhett Anne John Robert Jeanine Jorge Paul Elizabeth Leighia 6.6 6.2 1.9 3.9 6.1 6.7 15 3.1 3.8 615 713 615 615 615 713 615 615 713 615 D 0 M P D K D R K W 22 0 0 Table name: PILOT EMP_NUM PIL LICENSE 101 ATP 104 ATP 105 COM 106 COM 109 COM 4.9 3.5 5.2 0 0 23.4 3.2 5.3 42 4.5 2.1 0 0 0 4.5 894-2180 222-1672 442-3381 297-1228 290-2556 382-7185 297-3809 354.1 726 18-Jun-1961 19-May-1970 339.8 97.2 117.1 348.4 140.6 459.9 279.7 66.4 1 PL_RATINGS ATP/SELMELAnstr/CFI ATP/SELMELAnstr COMM/SELMELA/CF COMMISELMELAnstr ATPISELMEL/SESAnstr/CF 1 1 2 2 215.1 174.3 302.6 459.5 672 105.5 167 A 0.00 0.00 896.54 1285.19 673.21 1014.56 0.00 0.00 0.00 453.98 EMP DOB 15-Jun-1942 19-Mar-1965 14-Nov-1958 20-Dec-1992 16-Oct-1974 28-Aug-2005 08-Nov-1971 20-Oct-1996 14-Mar-1975 12-Feb-1968 21-Aug-1974 14-Feb-1966 02-Ju-1996 18-Nov-1994 1 14-Apr-1991 01-Dec-1992 0 2 1 2 0 2 1 0 0 1 0 1 08-Jan-2006 PA31-350 05-Jan-1991 0 2 0 0 0 CFI CFI INSTR MEL SEL SES 10011 10016 EMP_NUM RTG_CODE EARNRTO_DATE 10014 10019 10011 10017 10012 10014 10017 10016 10012 10010 10011 10017 10016 10011 10014 10017 EMP HIRE DATE 15-Mar-1987 25-Apr-1988 Table name: MODEL Table name: RATING RTG_CODE RTO NAME Certified Flight Instructor Certified Flight Instructor, Instrument Instrument Mutiengine Land PIL_MED_TYPE PIL_MED_DATE PL_PT135_DATE 20-Jan-16 18-Dec-15 05-Jan-16 10-Dec-15 22-Jan-16 101 CFI 101 CFI 101 INSTR 101 MEL 101 SEL 104 INSTR 104 MEL 104 SEL 105 CFI 105 INSTR 105 MEL 105 SEL 106 INSTR 106 MEL 106 SEL 109 CFI 109 CF Single Engine, Land Single Engine, Sea 109 INSTR 109 MEL 109 SEL 109 SES 11-Jan-16 17-Jan-16 02-Jan-16 02-Feb-16 15-Jan-16 MOD_CODE MOD_MANUFACTURER MOD_NAME MOD_SEATS MOD_CHO_MILE C-90A Beechcraft PA23-250 Piper Piper 18-Feb-98 15-Dec-05 06-Nov-93 23-Jun-94 21-Apr-90 15-JM-96 29-Jan-97 12-Mar-95 18-Nov-97 17-Apr-95 12-Aug-95 23-Sep-94 KingAir Aztec Navajo Chieftain 20-Dec-95 02-Apr-96 10-Mar-94 05-Nov-90 21-Jun-03 23-4-96 15-Mar-97 05-Feb-96 12-May-96 8 6 10 2.67 1.93 2.35
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 4 steps

Blurred answer
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY