ex01.sql

-- initialisation: creates tables
use omnibox;
drop table if exists dirige;
drop table if exists hobby;
create table if not exists dirige
    (azienda varchar(30) primary key,
     direttore varchar(20));
create table if not exists hobby
    (persona varchar(20) primary key,
     hobby varchar(15));

-- populates tables
insert into dirige values
    ('Alwil Software', 'White'),
    ('Piriform', 'Sanders'),
    ('Mozilla', 'Sanders'),
    ('COMODO', 'Greyhound'),
    ('Ikebana', 'Greyhound'),
    ('Zen', 'Powell'),
    ('Nintendo', 'Masuda'),
    ('Pokemon', 'Tajiri');

insert into hobby values
    ('Roger', null),
    ('Sanders', 'chess'),
    ('Tritan', 'swimming'),
    ('Greyhound', null),
    ('Tajiri', 'chess'),
    ('Powell', 'collections');

-- EXERCISE 1.1
-- shows CEOs without a hobby
select distinct d.direttore
    as persona
    from dirige d, hobby h
    where (direttore = h.persona
           and h.hobby is not null);
-- alternate solution: views
create view ceos
    as select direttore as individuo
    from dirige;
create view hobbies
    as select persona
    from hobby
    where hobby is not null;

select distinct persona
    from ceos c, hobbies h
    where c.individuo = h.persona;
drop view if exists ceos;
drop view if exists hobbies;

-- EXERCISE 1.2
-- shows pairs of CEOs that have at least one hobby in common
select distinct d1.direttore, d2.direttore
    from dirige d1, dirige d2, hobby h1, hobby h2
    where (d1.direttore = h1.persona and d2.direttore = h2.persona -- both have hobbies,
           and h1.hobby = h2.hobby                                 -- and said hobbies coincide,
           and d1.direttore <> d2.direttore);                      -- but they are not the same person

-- EXERCISE 1.3
-- shows CEOs that have less hobbies compared to the number of companies they are in charge of
create view ceos
    as select direttore as individuo
    from dirige;
create view hobbies
    as select persona
    from hobby; -- there can be no hobbies this time!
-- note: this part of the code does not give the expected results (Greyhound and Powell) and produces an empty set instead
-- it seems that "group by" constructs a set (aka a duplicate-less collection), therefore defeating the purpose of compare-selection
select individuo
    from ceos, hobbies
    where individuo = persona
    group by individuo
    having count(persona) < count(individuo);
drop view if exists ceos;
drop view if exists hobbies;

-- removes data
drop view if exists ceos;
drop view if exists hobbies;
drop table if exists dirige;
drop table if exists hobby;