· 6 years ago · Jun 11, 2019, 10:40 AM
1import pprint
2import sqlite3
3conn = sqlite3.connect('BD_AERO.sqlite')
4c = conn.cursor()
5pp = pprint.PrettyPrinter(width=120)
6c.execute('''PRAGMA foreign_keys = off''')
7c.execute('''drop table if exists Pass_in_trip ''')
8c.execute('''drop table if exists Passenger''')
9c.execute('''drop table if exists Trip''')
10c.execute('''drop table if exists Company''')
11c.execute('''pragma foreign_keys=on ''')
12
13c.execute('''CREATE TABLE if not exists Company (
14 ID_comp integer primary key,
15 name char (10) NOT NULL);
16''')
17c.execute('''
18CREATE TABLE if not exists Passenger (
19 ID_psg integer primary key,
20 name char (20) NOT NULL);
21''')
22c.execute('''
23CREATE TABLE if not exists Trip (
24 trip_no integer primary key,
25 ID_comp int NOT NULL ,
26 plane char (10) NOT NULL ,
27 town_from char (25) NOT NULL ,
28 town_to char (25) NOT NULL ,
29 time_out datetime NOT NULL ,
30 time_in datetime NOT NULL,
31 FOREIGN KEY (ID_comp) REFERENCES Company (ID_comp));
32''')
33
34c.execute('''
35CREATE TABLE if not exists Pass_in_trip (
36 trip_no int NOT NULL ,
37 date datetime NOT NULL ,
38 ID_psg int NOT NULL ,
39 place char (10) NOT NULL,
40 PRIMARY KEY (trip_no,date,ID_psg),
41 FOREIGN KEY (ID_psg) REFERENCES Passenger (ID_psg),
42 FOREIGN KEY (trip_no) REFERENCES Trip (trip_no));
43''')
44
45#Company
46c.execute('''insert into Company values(1,'Don_avia ');''')
47conn.commit()
48c.execute('''insert into Company values(2,'Aeroflot ');''')
49conn.commit()
50c.execute('''insert into Company values(3,'Dale_avia ');''')
51conn.commit()
52c.execute('''insert into Company values(4,'air_France');''')
53conn.commit()
54c.execute('''insert into Company values(5,'British_AW');''')
55conn.commit()
56
57
58#Passenger
59
60c.execute('''insert into Passenger values(1,'Bruce Willis ');''')
61conn.commit()
62c.execute('''insert into Passenger values(2,'George Clooney ');''')
63conn.commit()
64c.execute('''insert into Passenger values(3,'Kevin Costner ');''')
65conn.commit()
66c.execute('''insert into Passenger values(4,'Donald Sutherland ');''')
67conn.commit()
68c.execute('''insert into Passenger values(5,'Jennifer Lopez ');''')
69conn.commit()
70c.execute('''insert into Passenger values(6,'Ray Liotta ');''')
71conn.commit()
72c.execute('''insert into Passenger values(7,'Samuel L. Jackson ');''')
73conn.commit()
74c.execute('''insert into Passenger values(8,'Nikole Kidman ');''')
75conn.commit()
76c.execute('''insert into Passenger values(9,'Alan Rickman ');''')
77conn.commit()
78c.execute('''insert into Passenger values(10,'Kurt Russell ');''')
79conn.commit()
80c.execute('''insert into Passenger values(11,'Harrison Ford ');''')
81conn.commit()
82c.execute('''insert into Passenger values(12,'Russell Crowe ');''')
83conn.commit()
84c.execute('''insert into Passenger values(13,'Steve Martin ');''')
85conn.commit()
86c.execute('''insert into Passenger values(14,'Michael Caine ');''')
87conn.commit()
88c.execute('''insert into Passenger values(15,'Angelina Jolie ');''')
89conn.commit()
90c.execute('''insert into Passenger values(16,'Mel Gibson ');''')
91conn.commit()
92c.execute('''insert into Passenger values(17,'Michael Douglas ');''')
93conn.commit()
94c.execute('''insert into Passenger values(18,'John Travolta ');''')
95conn.commit()
96c.execute('''insert into Passenger values(19,'Sylvester Stallone ');''')
97conn.commit()
98c.execute('''insert into Passenger values(20,'Tommy Lee Jones ');''')
99conn.commit()
100c.execute('''insert into Passenger values(21,'Catherine Zeta-Jones');''')
101conn.commit()
102c.execute('''insert into Passenger values(22,'Antonio Banderas ');''')
103conn.commit()
104c.execute('''insert into Passenger values(23,'Kim Basinger ');''')
105conn.commit()
106c.execute('''insert into Passenger values(24,'Sam Neill ');''')
107conn.commit()
108c.execute('''insert into Passenger values(25,'Gary Oldman ');''')
109conn.commit()
110c.execute('''insert into Passenger values(26,'Clint Eastwood ');''')
111conn.commit()
112c.execute('''insert into Passenger values(27,'Brad Pitt ');''')
113conn.commit()
114c.execute('''insert into Passenger values(28,'Johnny Depp ');''')
115conn.commit()
116c.execute('''insert into Passenger values(29,'Pierce Brosnan ');''')
117conn.commit()
118c.execute('''insert into Passenger values(30,'Sean Connery ');''')
119conn.commit()
120c.execute('''insert into Passenger values(31,'Bruce Willis ');''')
121conn.commit()
122c.execute('''insert into Passenger values(37,'Mullah Omar ');''')
123conn.commit()
124
125
126
127#Trip
128c.execute('''insert into Trip values(1100,4,'Boeing','Rostov','Paris','1900-01-01 14:30:00','1900-01-01 17:50:00');''')
129conn.commit()
130c.execute('''insert into Trip values(1101,4,'Boeing','Paris','Rostov','1900-01-01 08:12:00','1900-01-01 11:45:00');''')
131conn.commit()
132c.execute('''insert into Trip values(1123,3,'TU-154','Rostov','Vladivostok','1900-01-01 16:20:00','1900-01-01 03:40:00');''')
133conn.commit()
134c.execute('''insert into Trip values(1124,3,'TU-154','Vladivostok','Rostov','1900-01-01 09:00:00','1900-01-01 19:50:00');''')
135conn.commit()
136c.execute('''insert into Trip values(1145,2,'IL-86','Moscow','Rostov','1900-01-01 09:35:00','1900-01-01 11:23:00');''')
137conn.commit()
138c.execute('''insert into Trip values(1146,2,'IL-86','Rostov','Moscow','1900-01-01 17:55:00','1900-01-01 20:01:00');''')
139conn.commit()
140c.execute('''insert into Trip values(1181,1,'TU-134','Rostov','Moscow','1900-01-01 06:12:00','1900-01-01 08:01:00');''')
141conn.commit()
142c.execute('''insert into Trip values(1182,1,'TU-134','Moscow','Rostov','1900-01-01 12:35:00','1900-01-01 14:30:00');''')
143conn.commit()
144c.execute('''insert into Trip values(1187,1,'TU-134','Rostov','Moscow','1900-01-01 15:42:00','1900-01-01 17:39:00');''')
145conn.commit()
146c.execute('''insert into Trip values(1188,1,'TU-134','Moscow','Rostov','1900-01-01 22:50:00','1900-01-01 00:48:00');''')
147conn.commit()
148c.execute('''insert into Trip values(1195,1,'TU-154','Rostov','Moscow','1900-01-01 23:30:00','1900-01-01 01:11:00');''')
149conn.commit()
150c.execute('''insert into Trip values(1196,1,'TU-154','Moscow','Rostov','1900-01-01 04:00:00','1900-01-01 05:45:00');''')
151conn.commit()
152c.execute('''insert into Trip values(7771,5,'Boeing','London','Singapore','1900-01-01 01:00:00','1900-01-01 11:00:00');''')
153conn.commit()
154c.execute('''insert into Trip values(7772,5,'Boeing','Singapore','London','1900-01-01 12:00:00','1900-01-01 02:00:00');''')
155conn.commit()
156c.execute('''insert into Trip values(7773,5,'Boeing','London','Singapore','1900-01-01 03:00:00','1900-01-01 13:00:00');''')
157conn.commit()
158c.execute('''insert into Trip values(7774,5,'Boeing','Singapore','London','1900-01-01 14:00:00','1900-01-01 06:00:00');''')
159conn.commit()
160c.execute('''insert into Trip values(7775,5,'Boeing','London','Singapore','1900-01-01 09:00:00','1900-01-01 20:00:00');''')
161conn.commit()
162c.execute('''insert into Trip values(7776,5,'Boeing','Singapore','London','1900-01-01 18:00:00','1900-01-01 08:00:00');''')
163conn.commit()
164c.execute('''insert into Trip values(7777,5,'Boeing','London','Singapore','1900-01-01 18:00:00','1900-01-01 06:00:00');''')
165conn.commit()
166c.execute('''insert into Trip values(7778,5,'Boeing','Singapore','London','1900-01-01 22:00:00','1900-01-01 12:00:00');''')
167conn.commit()
168c.execute('''insert into Trip values(8881,5,'Boeing','London','Paris','1900-01-01 03:00:00','1900-01-01 04:00:00');''')
169conn.commit()
170c.execute('''insert into Trip values(8882,5,'Boeing','Paris','London','1900-01-01 22:00:00','1900-01-01 23:00:00');''')
171conn.commit()
172
173
174
175#Pass_in_trip
176c.execute('''insert into Pass_in_trip values(1100,'2003-04-29 00:00:00',1,'1a ');''')
177conn.commit()
178c.execute('''insert into Pass_in_trip values(1123,'2003-04-05 00:00:00',3,'2a ');''')
179conn.commit()
180c.execute('''insert into Pass_in_trip values(1123,'2003-04-08 00:00:00',1,'4c ');''')
181conn.commit()
182c.execute('''insert into Pass_in_trip values(1123,'2003-04-08 00:00:00',6,'4b ');''')
183conn.commit()
184c.execute('''insert into Pass_in_trip values(1124,'2003-04-02 00:00:00',2,'2d ');''')
185conn.commit()
186c.execute('''insert into Pass_in_trip values(1145,'2003-04-05 00:00:00',3,'2c ');''')
187conn.commit()
188c.execute('''insert into Pass_in_trip values(1181,'2003-04-01 00:00:00',1,'1a ');''')
189conn.commit()
190c.execute('''insert into Pass_in_trip values(1181,'2003-04-01 00:00:00',6,'1b ');''')
191conn.commit()
192c.execute('''insert into Pass_in_trip values(1181,'2003-04-01 00:00:00',8,'3c ');''')
193conn.commit()
194c.execute('''insert into Pass_in_trip values(1181,'2003-04-13 00:00:00',5,'1b ');''')
195conn.commit()
196c.execute('''insert into Pass_in_trip values(1182,'2003-04-13 00:00:00',5,'4b ');''')
197conn.commit()
198c.execute('''insert into Pass_in_trip values(1187,'2003-04-14 00:00:00',8,'3a ');''')
199conn.commit()
200c.execute('''insert into Pass_in_trip values(1188,'2003-04-01 00:00:00',8,'3a ');''')
201conn.commit()
202c.execute('''insert into Pass_in_trip values(1182,'2003-04-13 00:00:00',9,'6d ');''')
203conn.commit()
204c.execute('''insert into Pass_in_trip values(1145,'2003-04-25 00:00:00',5,'1d ');''')
205conn.commit()
206c.execute('''insert into Pass_in_trip values(1187,'2003-04-14 00:00:00',10,'3d ');''')
207conn.commit()
208c.execute('''insert into Pass_in_trip values(8882,'2005-11-06 00:00:00',37,'1a ');''')
209conn.commit()
210c.execute('''insert into Pass_in_trip values(7771,'2005-11-07 00:00:00',37,'1c ');''')
211conn.commit()
212c.execute('''insert into Pass_in_trip values(7772,'2005-11-07 00:00:00',37,'1a ');''')
213conn.commit()
214c.execute('''insert into Pass_in_trip values(8881,'2005-11-08 00:00:00',37,'1d ');''')
215conn.commit()
216c.execute('''insert into Pass_in_trip values(7778,'2005-11-05 00:00:00',10,'2a ');''')
217conn.commit()
218c.execute('''insert into Pass_in_trip values(7772,'2005-11-29 00:00:00',10,'3a ');''')
219conn.commit()
220c.execute('''insert into Pass_in_trip values(7771,'2005-11-04 00:00:00',11,'4a ');''')
221conn.commit()
222c.execute('''insert into Pass_in_trip values(7771,'2005-11-07 00:00:00',11,'1b ');''')
223conn.commit()
224c.execute('''insert into Pass_in_trip values(7771,'2005-11-09 00:00:00',11,'5a ');''')
225conn.commit()
226c.execute('''insert into Pass_in_trip values(7772,'2005-11-07 00:00:00',12,'1d ');''')
227conn.commit()
228c.execute('''insert into Pass_in_trip values(7773,'2005-11-07 00:00:00',13,'2d ');''')
229conn.commit()
230c.execute('''insert into Pass_in_trip values(7772,'2005-11-29 00:00:00',13,'1b ');''')
231conn.commit()
232c.execute('''insert into Pass_in_trip values(8882,'2005-11-13 00:00:00',14,'3d ');''')
233conn.commit()
234c.execute('''insert into Pass_in_trip values(7771,'2005-11-14 00:00:00',14,'4d ');''')
235conn.commit()
236c.execute('''insert into Pass_in_trip values(7771,'2005-11-16 00:00:00',14,'5d ');''')
237conn.commit()
238c.execute('''insert into Pass_in_trip values(7772,'2005-11-29 00:00:00',14,'1c ');''')
239conn.commit()
240#103, 63, 124,142,67,68,72, 77, 88, 114.
241
242#103
243c.execute('''
244select min(A.trip_no) AS min_1, min(B.trip_no) AS min_2,min(C.trip_no) AS min_3, max(A.trip_no) AS max_1, max(B.trip_no) AS max_2,max(C.trip_no) AS max_3
245from trip as A, trip as B, trip as C
246where A.trip_no<B.trip_no and B.trip_no<C.trip_no
247''')
248pprint.pprint(c.fetchall(),indent=5,width=60,compact=False)
249#63
250c.execute('''
251select name
252from Passenger
253where ID_psg in (
254select ID_psg
255from Pass_in_trip
256group by place, ID_psg
257having count(*)>1
258)
259''')
260pprint.pprint(c.fetchall(),indent=5,width=60,compact=False)
261#124
262c.execute('''
263select name
264from passenger
265where id_psg in(select id_psg
266from (select id_psg, cnt, count(*) otv
267from
268(select pit.id_psg, count(*) cnt
269from pass_in_trip pit join trip t ON
270pit.trip_no=t.trip_no
271group by pit.id_psg, id_comp
272) res
273group by id_psg,cnt
274)ttt
275where otv>1 and id_psg not in(
276select id_psg
277from (select id_psg,count(*) eee
278from(
279select id_psg
280from
281(select pit.id_psg, count(*) cnt
282from pass_in_trip pit join trip t ON
283pit.trip_no=t.trip_no
284group by pit.id_psg, id_comp
285) res
286group by id_psg,cnt
287)OO
288group by id_psg)EE
289where eee>1
290)
291)
292''')
293pprint.pprint(c.fetchall(),indent=5,width=60,compact=False)
294#142
295c.execute('''
296select name
297from passenger
298where id_psg in(select res.id_psg
299from (select count(*) ct,count(distinct t.town_to) ctt, p.id_psg
300from trip t left join pass_in_trip pit on t.trip_no=pit.trip_no left join passenger p on pit.id_psg=p.id_psg
301where p.id_psg in(select res.id_psg
302from (select p.id_psg, count(distinct t.plane) num
303from trip t left join pass_in_trip pit on t.trip_no=pit.trip_no left join passenger p on pit.id_psg=p.id_psg
304group by p.id_psg
305)res
306where res.num=1
307)
308group by p.id_psg
309)res
310where res.ct!=res.ctt
311)
312''')
313pprint.pprint(c.fetchall(),indent=5,width=60,compact=False)
314#67
315c.execute('''
316drop view if exists attawa
317''')
318c.execute('''create view if not exists attawa as
319select town_from,town_to,count(*) as qqq
320from trip
321group by town_to,town_from
322''')
323conn.commit()
324c.execute('''
325select count(*)
326from attawa
327where qqq>=(select max(qqq) from attawa)
328''')
329pprint.pprint(c.fetchall(),indent=5,width=60,compact=False)
330#68
331c.execute('''
332drop view if exists rc
333''')
334c.execute('''create view if not exists rc as
335select
336count(*) as route_trips
337from trip
338group by
339case when town_from> town_to
340then town_from else town_to
341end
342,case when town_from<town_to
343then town_from else town_to
344end
345''')
346conn.commit()
347c.execute('''
348select count(*) as route_count from rc
349where route_trips=(select max(route_trips) from rc)
350''')
351pprint.pprint(c.fetchall(),indent=5,width=60,compact=False)
352#72
353c.execute('''
354drop view if exists anna
355''')
356c.execute('''create view if not exists anna as
357select id_psg, count(distinct id_comp) as comp, count(date) as col
358from pass_in_trip left JOIN trip T ON T.trip_no=pass_in_trip.trip_no
359where id_psg NOT in
360(
361select id_psg
362from trip JOIN pass_in_trip ON trip.trip_no=pass_in_trip.trip_no
363where trip.id_comp <> T.id_comp
364)
365group by id_psg
366''')
367conn.commit()
368c.execute('''
369select passenger.name, anna.col
370from anna left JOIN passenger ON anna.id_psg=passenger.id_psg
371where anna.col>=(select max(col) from anna)
372group by anna.col, passenger.name
373having anna.col=max(anna.col)
374''')
375pprint.pprint(c.fetchall(),indent=5,width=60,compact=False)
376#77
377c.execute('''
378SELECT superden.qty a, superden.date b
379FROM
380(SELECT COUNT(den.trip_no) AS qty, den.date
381FROM
382(SELECT DISTINCT trip_no, date FROM Pass_in_trip) AS den,
383Trip WHERE trip.trip_no=den.trip_no AND
384trip.town_from='Rostov'
385GROUP BY den.date) AS superden
386where superden.qty in(
387select max(res.a)
388from (SELECT superden.qty a, superden.date b
389FROM
390(SELECT COUNT(den.trip_no) AS qty, den.date
391FROM
392(SELECT DISTINCT trip_no, date FROM Pass_in_trip) AS den,
393Trip WHERE trip.trip_no=den.trip_no AND
394trip.town_from='Rostov'
395GROUP BY den.date) AS superden
396)res
397)
398''')
399pprint.pprint(c.fetchall(),indent=5,width=60,compact=False)
400#88
401c.execute('''
402drop view if exists table1
403''')
404c.execute('''create view if not exists table1 as
405SELECT max(p.name) name, p.id_psg, count(t.id_comp) as trip_Qty, c.name as Company
406FROM trip t JOIN Pass_in_trip pip ON t.trip_no = pip.trip_no JOIN Passenger p ON p.id_psg =pip.id_psg JOIN Company c ON c.id_comp = t.id_comp
407where t.id_comp = (SELECT max(id_comp) FROM trip t2 JOIN Pass_in_trip pip2 ON t2.trip_no = pip2.trip_no JOIN Passenger p2 ON p2.id_psg =pip2.id_psg WHERE p2.id_psg =p.id_psg) AND
408t.id_comp = (SELECT min(id_comp) FROM trip t2 JOIN Pass_in_trip pip2 ON t2.trip_no = pip2.trip_no JOIN Passenger p2 ON p2.id_psg =pip2.id_psg WHERE p2.id_psg =p.id_psg)
409GROUP by p.id_psg, c.name
410''')
411conn.commit()
412c.execute('''
413SELECT name, trip_Qty, Company
414FROM table1
415WHERE trip_qty = (select max(trip_qty) from table1)
416''')
417pprint.pprint(c.fetchall(),indent=5,width=60,compact=False)
418
419#114
420c.execute('''
421drop view if exists b
422''')
423c.execute('''
424drop view if exists b1
425''')
426c.execute('''create view if not exists b as
427SELECT ID_psg, COUNT(*) as cnt FROM Pass_In_Trip GROUP BY ID_psg, place
428''')
429conn.commit()
430c.execute('''create view if not exists b1 as
431SELECT DISTINCT ID_psg, cnt FROM b WHERE cnt =(SELECT MAX(cnt) FROM b)
432''')
433conn.commit()
434c.execute('''
435SELECT name, cnt FROM b1 JOIN Passenger p ON (b1.ID_psg = p.ID_psg)
436''')
437pprint.pprint(c.fetchall(),indent=5,width=60,compact=False)
438conn.close()