0RgbCcuD

1--
2-- Creare schema esercitazione:
3-- Volo(cod,partenza,arrivo,numpass,comp)
4-- Compagnia(codice,nazione)
5-- v. integrità referenziale tra Compagnia.codice e Volo.comp
6 
7
8CREATE DATABASE IF NOT EXISTS EsercitazioneVoli;
9USE EsercitazioneVoli;
10
11DROP TABLE IF EXISTS compagnia;
12CREATE TABLE compagnia (
13	codice int NOT NULL,
14	nazione varchar(255),
15	PRIMARY KEY(codice)
16);
17
18
19DROP TABLE IF EXISTS volo;
20CREATE TABLE volo (
21  cod int NOT NULL,
22  partenza varchar(255),
23  arrivo varchar(255),
24  numpass int,
25  comp int,
26  PRIMARY KEY(cod),
27  FOREIGN KEY(comp) REFERENCES compagnia(codice)
28);
29
30INSERT INTO compagnia (codice,nazione) VALUES 
31 (1,'italia'),
32 (2,'belgio'),
33 (3,'francia'),
34 (4,'spagna'),
35 (5,'francia'),
36 (6,'italia'),
37 (7,'italia');
38
39INSERT INTO volo (cod,partenza,arrivo,numpass,comp) VALUES 
40 (11,'Belize','Baden',110,1),
41 (13,'Baden','Keflovik',110,1),
42 (15,'Bombay','Dublin',100,2),
43 (17,'Dublin','Baden',180,2),
44 (19,'Belize','Dublin',180,3),
45 (21,'Moscow','Singapore',210,4),
46 (27,'CapeTown','Johannesburg',180,4),
47 (30,'Basel','Taipei',100,3),
48 (31,'Belize','Dublin',100,5),
49 (32,'Firenze','London',200,6),
50 (34,'Firenze','Belfast',110,6),
51 (35,'Belfast','Dublin',110,6),
52 (37,'Moscow','Taipei',110,5),
53 (41,'Firenze','Taipei',110,6),
54 (45,'Dublin','Moscow',120,6),
55 (46,'Dublin','Belize',120,6),
56 (56,'Moscow','Dublin',180,4),
57 (57,'Rome','London',210,4),
58 (58,'Palermo','Milano',100,4),
59 (59,'Singapore','London',110,7),
60 (75,'Parigi','London',140,7),
61 (76,'Belize','Taipei',200,7),
62 (91,'London','Rome',150,7),
63 (96,'Keflowik','Moscow',145,7),
64 (97,'Dublin','Rome',150,3),
65 (102,'Bombay','Singapore',120,3),
66 (121,'Belize','Dublin',150,3),
67 (126,'Tapei','Baden',180,3),
68 (178,'Napoli','Rome',150,5);
69 
70 SELECT *
71 FROM compagnia INNER JOIN volo ON compagnia.codice = volo.comp
72 WHERE nazione = 'italia';
73 
74 SELECT DISTINCT partenza
75 FROM volo V
76 WHERE NOT EXISTS( SELECT *
77 FROM compagnia INNER JOIN volo ON compagnia.codice = volo.comp
78 WHERE nazione = 'francia' AND partenza = V.partenza);
79 
80 SELECT SUM(numpass)
81 FROM volo
82 WHERE arrivo = 'Rome';