· 6 years ago · Mar 05, 2019, 06:14 PM
1DROP TABLE IF EXISTS AIRPORTS;
2CREATE TABLE AIRPORTS (
3AIRPORT_ID INT NOT NULL,
4City VARCHAR (355) NOT NULL,
5IATA_code VARCHAR (355) NOT NULL,
6Able_to_accomodate_A380 INTEGER NOT NULL -- 1 - yes, 0-no
7);
8INSERT INTO AIRPORTS
9VALUES
10(1, 'Novosibirsk', 'OVB', 1),
11(2, 'Dubai', 'DXB', 1),
12(3, 'London', 'LYX', 0),
13(4, 'Burnie', 'BWT', 0),
14(5, 'Beijing', 'PEK', 1),
15(6, 'Semarang', 'SRG', 0),
16(7, 'Punta_Cana', 'PUJ', 1),
17(8, 'Havana', 'HAV', 0),
18(9, 'Kigali', 'KGL', 0),
19(10, 'Houston', 'IAH', 1);
20
21DROP TABLE IF EXISTS AIRLINES;
22CREATE TABLE AIRLINES (
23AIRLINE_ID INTEGER NOT NULL,
24NAME VARCHAR (355) NOT NULL,
25FLEET_VOLUME INT NOT NULL,
26Airbus_A380 INT NOT NULL, -- 1 - yes, 0-no
27Dassault_Falcon_7X INT NOT NULL -- 1 - yes, 0-no
28);
29INSERT INTO AIRLINES
30VALUES
31(1, 'Air_France', 212, 1, 0),
32(2, 'Lufthansa', 287, 1, 0),
33(3, 'Qantas', 126, 1, 0),
34(4, 'JET_AVIATION', 37, 0, 1),
35(5, 'British_Airways', 279, 1, 0),
36(6, 'S7', 92, 0, 0),
37(7, 'CAT_AVIATION', 7, 0, 1),
38(8, 'Emirates', 272, 1, 0),
39(9, 'RwandAir', 12, 0, 0),
40(10, 'Cubana', 24, 0, 0),
41
42Drop table if exists AC_Brands;
43Create table AC_Brands (
44BRAND_ID integer,
45Manufacturer VARCHAR (50),
46Country_of_manufacture VARCHAR (50));
47
48Insert into AC_Brands values
49(1, 'Bombardier', 'Canada'),
50(2, 'Airbus', 'France'),
51(3, 'Boeing', 'USA'),
52(4, 'Cessna', 'USA'),
53(5, 'IL', 'Russia'),
54(6, 'Embraer', 'Brazil'),
55(7, 'Dassault', 'France');
56
57-- Bombardier, Boeing, Tupolev, Sukhoy, Embraer, Cessna, Airbus
58
59
60Drop table if exists Planes;
61Create Table Planes (
62PLANE_ID integer,
63Manufacturer VARCHAR (350),
64Model VARCHAR (350),
65Pas_Capacity Numeric (30,0),
66Range Numeric (30,0),
67Year_of_Maiden_Flight integer
68);
69
70Insert into Planes values
71 (1, 'Embraer', '110 Bandeirante', 18, 1964, 1968),
72 (2, 'Bombardier', 'Phenom 300', 6, 3650, 2008),
73 (3, 'Bombardier', 'CRJ700', 66, 2553, 1999),
74 (4, 'Bombardier', 'Learjet 70/75', 9, 3778, 2013),
75 (5, 'Airbus', 'A220', 133, 5460, 2013),
76 (6, 'Cessna', 'Citationjet/M@', 10, 4010, 1991),
77 (7, 'Airbus', 'A380-800', 853, 14800, 2005),
78 (8, 'IL', '96-400', 436, 12000, 1988),
79 (9, 'Boeing', '777', 414, 13936, 1994),
80 (10, 'Dassault', 'Falcon 7X', 16, 11945, 2005);
81
82Create Table FLEETS(
83AIRLINE_ID INTEGER (30),
84PLANE_ID INTEGER (30),
85PLANES_NUMBER INTEGER (30)
86);
87
88(2, 5, 46),
89(2, 6, 19),
90(2, 7, 11),
91(2, 8, 4),
92(2, 9, 44),
93(2, 10, 0),
94(3, 1, 11),
95(3, 2, 14),
96(3, 3, 12),
97(3, 4, 11),
98(3, 5, 21),
99(3, 6, 1),
100(3, 7, 4),
101(3, 8, 22),
102(3, 9, 30),
103(3, 10, 0),
104(4, 1, 0),
105(4, 2, 12),
106(4, 3, 0),
107(4, 4, 1),
108(4, 5, 0),
109(4, 6, 13),
110(4, 7, 0),
111(4, 8, 0),
112(4, 9, 0),
113(4, 10, 11),
114(5, 1, 12),
115(5, 2, 13),
116(5, 3, 76),
117(5, 4, 34),
118(5, 5, 44),
119(5, 6, 12),
120(5, 7, 18),
121(5, 8, 11),
122(5, 9, 59),
123(5, 10, 0),
124(6, 1, 0),
125(6, 2, 0),
126(6, 3, 31),
127(6, 4, 0),
128(6, 5, 0),
129(6, 6, 1),
130(6, 7, 0),
131(6, 8, 38),
132(6, 9, 22),
133(6, 10, 0),
134(7, 1, 1),
135(7, 2, 1),
136(7, 3, 0),
137(7, 4, 1),
138(7, 5, 0),
139(7, 6, 2),
140(7, 7, 0),
141(7, 8, 0),
142(7, 9, 0),
143(7, 10, 9),
144(8, 1, 21),
145(8, 2, 25),
146(8, 3, 38),
147(8, 4, 33),
148(8, 5, 54),
149(8, 6, 12),
150(8, 7, 27),
151(8, 8, 1),
152(8, 9, 61),
153(8, 10, 0),
154(9, 1, 2),
155(9, 2, 2),
156(9, 3, 0),
157(9, 4, 1),
158(9, 5, 0),
159(9, 6, 7),
160(9, 7, 0),
161(9, 8, 0),
162(9, 9, 0),
163(9, 10, 0),
164(10, 1, 5),
165(10, 2, 1),
166(10, 3, 0),
167(10, 4, 4),
168(10, 5, 0),
169(10, 6, 8),
170(10, 7, 0),
171(10, 8, 5),
172(10, 9, 1),
173(10, 10, 0);