RV3dBcP7

1----------- task 1:
2
3DROP TABLE IF EXISTS airport;
4
5CREATE TABLE airport (
6  id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
7  name varchar(128) NOT NULL,
8  code varchar(2) NOT NULL,
9  address varchar(512) NOT NULL,
10  
11  UNIQUE INDEX(code)
12) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
13
14DROP TABLE IF EXISTS airline;
15
16CREATE TABLE airline (
17  id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
18  name varchar(128) NOT NULL,
19  abbr varchar(3) NOT NULL,
20  
21  UNIQUE INDEX(abbr)
22) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
23
24DROP TABLE IF EXISTS flight;
25
26CREATE TABLE flight (
27  id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
28  number varchar(4) NOT NULL,
29  airport_origin_id int(11) NOT NULL,
30  airport_dest_id int(11) NOT NULL,
31  airline_id int(11) NOT NULL,
32  duration int(11) NOT NULL,
33  distance int(11) NOT NULL,
34  price int(11) NOT NULL,
35  
36  UNIQUE INDEX(number),
37  
38  INDEX(airline_id),
39  INDEX(airport_origin_id),
40  INDEX(airport_dest_id),
41  INDEX(airport_origin_id, airport_dest_id), -- this composite index will speed up query from task #2
42  
43  FOREIGN KEY (airline_id)
44      REFERENCES airline (id),
45  FOREIGN KEY (airport_origin_id)
46      REFERENCES airport (id),
47  FOREIGN KEY (airport_dest_id)
48      REFERENCES airport (id)  
49) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
50
51
52----------- task 2:
53
54
55SELECT airport_origin_id, airport_dest_id
56INTO @originId, @destId
57FROM flight ORDER BY RAND() LIMIT 1;
58
59-- if you need any of the possibly many fastest flights
60SELECT flight.* 
61FROM flight
62WHERE airport_origin_id = @originId AND airport_dest_id = @destId
63ORDER BY duration ASC
64LIMIT 1;
65
66-- if you want all fastest flights:
67SELECT flight.* 
68FROM flight
69WHERE 
70	airport_origin_id = @originId 
71    AND airport_dest_id = @destId
72    AND duration = (
73      	SELECT MIN(duration) FROM flight WHERE 
74      		airport_origin_id = @originId AND airport_dest_id = @destId
75      )
76ORDER BY id 
77
78
79
80
81----------- task 3:
82
83
84SELECT id
85INTO @originId
86FROM airport ORDER BY RAND() LIMIT 1;
87
88-- finds all cheapest flights between origin airport and each available destination airport
89SELECT airport.name, airport.address, airline.name, airline.abbr, flight.price
90FROM flight
91INNER JOIN airline ON airline.id = flight.airline_id
92INNER JOIN airport ON airport.id = flight.airport_dest_id
93WHERE flight.airport_origin_id = @originId
94AND flight.price = (
95  SELECT MIN(t.price) 
96  FROM flight t 
97  WHERE t.airport_origin_id = @originId 
98  	AND t.airport_dest_id = flight.airport_dest_id
99);
100
101
102
103
104
105----------- task 4: 
106
107SELECT airline.*, AVG(distance / duration) as avg
108FROM flight
109INNER JOIN airline ON airline.id = flight.airline_id
110GROUP BY airline_id
111ORDER BY avg
112LIMIT 1