--
-- Create schema azienda
--

CREATE DATABASE IF NOT EXISTS azienda;
USE azienda;

--
-- Definition of table dipendente
--

CREATE TABLE dipendente (
  cf char(16) NOT NULL,
  nome varchar(75) NOT NULL,
  citta varchar(30) NOT NULL,
  PRIMARY KEY (cf)
);


--
-- Definition of table progetto
--

CREATE TABLE progetto (
  cod_p char(5) NOT NULL,
  nome_progetto varchar(45) NOT NULL,
  anno int(4),
  durata int(2),
  PRIMARY KEY (cod_p)
);

--
-- Definition of table lavora
--

CREATE TABLE lavora (
  cod_p_l char(5) NOT NULL,
  cf_l char(16) NOT NULL,
  mesi int(2) NOT NULL,
  ruolo varchar (30) NOT NULL,
  PRIMARY KEY (cod_p_l, cf_l),
  FOREIGN KEY (cod_p_l) REFERENCES progetto(cod_p),
  FOREIGN KEY (cf_l) REFERENCES dipendente(cf)
);

-- nel progetto cod_p il dipendente cf lavora per un certo numero di mesi, svolgendo un certo ruolo

--
-- populating the DB
--
INSERT INTO dipendente (cf, nome, citta)
VALUES ('RSSMRA72R30G273X', 'Mario Rossi', 'Milano'),
('BNCPLA85A21T101R', 'Paolo Bianchi', 'Torino'),
('NREFNS83B11C341O', 'Francesco Neri', 'Genova'),
('GLLGSI76S26L011E', 'Giusto Gialli', 'Modena'),
('VREGNM54R11B244A', 'Gianmaria Verdi', 'Modena');

INSERT INTO progetto (cod_p, nome_progetto, anno, durata)
VALUES ('ABCDE', 'Prog1', 1995, 12),
('BCDEF', 'Prog2', 1996, 10),
('CDEFG', 'Prog3', 1995, 11),
('DEFGH', 'Prog4', 1997, 18),
('EFGHI', 'Prog5', 1994, 24);

INSERT INTO lavora (cod_p_l, cf_l, mesi, ruolo)
VALUES ('ABCDE', 'RSSMRA72R30G273X', 6, 'Assistente'),
('ABCDE', 'BNCPLA85A21T101R', 4, 'Amministratore'),
('BCDEF', 'BNCPLA85A21T101R', 6, 'Amministratore'),
('BCDEF', 'NREFNS83B11C341O', 5, 'Dirigente'),
('BCDEF', 'VREGNM54R11B244A', 1, 'Operaio'),
('CDEFG', 'VREGNM54R11B244A', 3, 'Autista'),
('CDEFG', 'NREFNS83B11C341O', 11, 'Supervisore'),
('DEFGH', 'VREGNM54R11B244A', 18, 'Dirigente'),
('EFGHI', 'VREGNM54R11B244A', 12, 'Presidente'),
('EFGHI', 'GLLGSI76S26L011E', 12, 'Amministratore'),
('EFGHI', 'BNCPLA85A21T101R', 12, 'Impiegato'),
('EFGHI', 'NREFNS83B11C341O', 12, 'Uomo dei Panini');

--
-- QUERY (ALGEBRA RELAZIONALE a), b))
--

-- a) selezionare i dati dei dipendenti di Modena che non hanno lavorato
-- in alcun progetto dell'anno 1995

SELECT d.*
FROM dipendente d
WHERE d.citta='Modena' AND NOT EXISTS (
                                       SELECT *
                                       FROM progetto p, lavora l
                                       WHERE p.cod_p=l.cod_p_l AND l.cf_l=d.cf AND p.anno=1995
                                       );

-- prof
SELECT
    *
FROM
    DIPENDENTE d
WHERE
    d.citta = 'Modena'
        AND d.cf NOT IN (SELECT
            l.cf_l
        FROM
            lavora l,
            progetto p
        WHERE
            l.cod_p_l = p.cod_p AND p.anno = 1995);

-- b) selezionare i dati dei dipendenti che non hanno mai lavorato insieme
-- ad un dipendente di Modena, cioè nello stesso progetto in cui lavorava
-- anche un dipendente di Modena

SELECT d.*
FROM dipendente d
WHERE NOT EXISTS (
                  SELECT *
                  FROM dipendente d2, lavora l, lavora l2
                  WHERE  d2.cf=l2.cf_l AND d.cf=l.cf_l AND l.cod_p_l=l2.cod_p_l AND d2.citta='Modena'
                  );

-- prof
SELECT
    *
FROM
    dipendente d
WHERE
    d.cf NOT IN (SELECT
            l1.cf_l
        FROM
            lavora l1,
            lavora l2,
            dipendente d
        WHERE
            l1.cod_p_l = l2.cod_p_l
                AND l2.cf_l = d.cf
                AND d.citta = 'Modena');

-- c) selezionare le coppie (cf1, cf2) tali che i dipendenti con codice fiscale
-- cf1 e cf2 hanno lavorato nello stesso progetto

SELECT l1.cf_l AS cf1, l2.cf_l AS cf2, l1.cod_p_l AS Progetto
FROM lavora l1, lavora l2
WHERE l1.cod_p_l=l2.cod_p_l AND l1.cf_l<l2.cf_l;

-- prof (non va bene il <> perchè così vengono selezionate anche le coppie 'simmetriche')
-- (Inoltre servirebbe un distinct o l'attributo cod_p_l per evitare la ripetizione di tuple)
SELECT
    l1.cf_l AS CF1, l2.cf_l AS CF2
FROM
    lavora l1,
    lavora l2
WHERE
    l1.cod_p_l = l2.cod_p_l
        AND l1.cf_l <> l2.cf_l;

-- d) selezionare i dipendenti che hanno lavorato in almeno 3 ruoli distinti

SELECT l.cf_l AS CodDipendente
FROM lavora l
GROUP BY l.cf_l
HAVING count(distinct l.ruolo)>=3;

-- prof
SELECT
    *
FROM
    dipendente d
WHERE
    3 <= (SELECT
            COUNT(DISTINCT l.ruolo)
        FROM
            lavora l
        WHERE
            l.cf_l = d.cf);

-- e) selezionare, per ogni dipendente, il progetto in cui esso ha lavorato
-- il maggior numero di mesi

-- Non valida se un dipendente occupa più ruoli in uno stesso progetto
SELECT l.cf_l AS Dipendente, l.cod_p_l AS Progetto
FROM lavora l
WHERE l.mesi = (
                SELECT max(l2.mesi)
                FROM lavora l2
                WHERE l.cf_l=l2.cf_l
                );

-- prof
SELECT
    l1.cf_l, l1_cod_p_l
FROM
    lavora l1
GROUP BY l1.cf_l , l1_cod_p_l
HAVING SUM(l1.mesi) >= ALL (SELECT
        SUM(l2.mesi)
    FROM
        lavora l2
    WHERE
        l1.cf_l = l2.cf_l
    GROUP BY l2.cod_p_l);