ex04.sql

-- initialisation: creates tables
use omnibox;
drop table if exists company;
drop table if exists flight;
create table if not exists company
  (code int(10) unsigned not null AUTO_INCREMENT,
   nation varchar(45) not null,
   primary key (code));
create table if not exists flight
  (code int(10) unsigned not null AUTO_INCREMENT,
   departure varchar(45) not null,
   arrival varchar(45) not null,
   passengers int(10) unsigned not null,
   company varchar(45) not null,
   primary key (code));

-- populates tables
insert into company values
 (1, 'Italy'),
 (2, 'Belgium'),
 (3, 'France'),
 (4, 'Spain'),
 (5, 'France'),
 (6, 'Italy'),
 (7, 'Italy');
insert into flight values
 (11, 'Belize', 'Baden', 110, '01'),
 (13, 'Baden', 'Keflovik', 110, '01'),
 (15, 'Bombay', 'Dublin', 100, '02'),
 (17, 'Dublin', 'Baden', 180, '02'),
 (19, 'Belize', 'Dublin', 180, '03'),
 (21, 'Moscow', 'Singapore', 210, '04'),
 (27, 'CapeTown', 'Johannesburg', 180, '04'),
 (30, 'Basel', 'Taipei', 100, '03'),
 (31, 'Belize', 'Dublin', 100, '05'),
 (32, 'Firenze', 'London', 200, '06'),
 (34, 'Firenze', 'Belfast', 110, '06'),
 (35, 'Belfast', 'Dublin', 110, '06'),
 (37, 'Moscow', 'Taipei', 110, '05'),
 (41, 'Firenze', 'Taipei', 110, '06'),
 (45, 'Dublin', 'Moscow', 120, '06'),
 (46, 'Dublin', 'Belize', 120, '06'),
 (56, 'Moscow', 'Dublin', 180, '04'),
 (57, 'Rome', 'London', 210, '04'),
 (58, 'Palermo', 'Milano', 100, '04'),
 (59, 'Singapore', 'London', 110, '07'),
 (75, 'Parigi', 'London', 140, '07'),
 (76, 'Belize', 'Taipei', 200, '07'),
 (91, 'London', 'Rome', 150, '07'),
 (96, 'Keflowik', 'Moscow', 145, '07'),
 (97, 'Dublin', 'Rome', 150, '03'),
 (102, 'Bombay', 'Singapore', 120, '03'),
 (121, 'Belize', 'Dublin', 150, '03'),
 (126, 'Tapei', 'Baden', 180, '03'),
 (178, 'Napoli', 'Roma', 150, '05');

-- EXERCISE 4.1
-- show the flight data for flights of Italian companies
select f.*
    from flight f, company c
    where f.company = c.code
          and c.nation = 'Italy';

-- EXERCISE 4.2
-- show airports from which no French company flights take off
select departure as airport
    from flight
    where departure <> all
        (select f.departure
            from flight f, company c
            where f.company = c.code
                  and c.nation = 'France');

-- EXERCISE 4.3
-- for each airport, show the overall number of passengers, but only if the number of flights is greater than 4
select t.airport, sum(t.passengers)
    from
    (select f1.departure as airport, f1.passengers, count(f1.arrival) as flights
        from flight f1, flight f2
        where f1.departure = f2.departure
        group by f1.departure, f1.arrival, f1.passengers
        having count(f1.arrival) > 4) t;

-- removes data
drop table if exists company;
drop table if exists flight;