/*-------------------------------------------------------
Nomi impiegati responsabili di almeno un altro impiegato
*/
CREATE OR REPLACE VIEW Responsabili AS
SELECT	C.Nome, C.Cognome, C.Matricola
FROM	Impiegato C
WHERE 	EXISTS (SELECT	*
		FROM	Dipartimento D
		WHERE   C.Matricola = D.Impiegato
               );
/*-------- COME FUNZIONA? */
SELECT  C.Matricola FROM Impiegato C;
SELECT  D.Impiegato FROM Dipartimento D;
	
SELECT	*			-- NON utile
FROM	Dipartimento D
WHERE   '101' = D.Impiegato;

SELECT	*			-- NON utile
FROM	Dipartimento D
WHERE   '103' = D.Impiegato;
REM .............
SELECT	*			-- UTILE 
FROM	Dipartimento D
WHERE   '210' = D.Impiegato;
REM .............
REM si ottiene lo stesso risultato prodotto da:
SELECT  * FROM Responsabili;    -- interrogazione usuale su una vista
/* RISULTATO:
NOME		    |COGNOME		 |MATRIC
--------------------|--------------------|------
Marco		    |Celli		 |210
Siro		    |Bisi		 |231
Sergio		    |Rossi		 |301
Mario		    |Rossi		 |375
Silvia		    |Zanzi		 |010
Anna		    |Pilatone		 |020
Antonio 	    |Prevignano 	 |030

Statistics
----------------------------------------------------------
	  7  recursive calls
	 32  db block gets
	 35  consistent gets
	  0  physical reads
	  0  redo size
	847  bytes sent via SQL*Net to client
	425  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  7  rows processed
----------------------------------------------------------*/

/*------------------------------------------------
Nomi degli impiegati non responsabili 
*/
SELECT	S.Nome, S.Cognome, S.Matricola
FROM	Impiegato S
WHERE 	NOT EXISTS (SELECT  *
		    FROM    Dipartimento D
		    WHERE   S.Matricola = D.Impiegato
               )
ORDER BY S.Nome;		-- L'ordinamento serve per dopo
/*-------- COME FUNZIONA? */
SELECT  S.Matricola FROM Impiegato S;
SELECT  D.Impiegato FROM Dipartimento D;
	
SELECT	*			-- UTILE
FROM	Dipartimento D
WHERE   '101' = D.Impiegato;

SELECT	*			-- UTILE
FROM	Dipartimento D
WHERE   '103' = D.Impiegato;
REM .............
SELECT	*			-- NON utile
FROM	Dipartimento D
WHERE   '210' = D.Impiegato;
REM .............
/* .. che e` il RISULTATO della query iniziale:
NOME		    |COGNOME		 |MATRIC
--------------------|--------------------|------
Carla		    |Vercellotti	 |032
Carmelina	    |Savatteri		 |031
Fulvia		    |Donetti		 |021
Fulvio		    |Marini		 |022
Luigi		    |Neri		 |104
Mario		    |Rossi		 |101
Mario		    |Bianchi		 |103
Massimo 	    |Rosciano		 |033
Nico		    |Bini		 |105
Nico		    |Bini		 |252

Statistics
----------------------------------------------------------
	  0  recursive calls
	 32  db block gets
	 32  consistent gets
	  0  physical reads
	  0  redo size
       1021  bytes sent via SQL*Net to client
	425  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
	 10  rows processed
----------------------------------------------------------*/

/*------------------------------------------------
VERSIONE ALTERNATIVA della query precedente.
*/
SELECT	T.Nome, T.Cognome, T.Matricola FROM Impiegato T
MINUS
SELECT * FROM Responsabili;	-- USO DELLA VISTA!
/* RISULTATO:
NOME		    |COGNOME		 |MATRIC
--------------------|--------------------|------
Carla		    |Vercellotti	 |032
Carmelina	    |Savatteri		 |031
Fulvia		    |Donetti		 |021
Fulvio		    |Marini		 |022
Luigi		    |Neri		 |104
Mario		    |Rossi		 |101
Mario		    |Bianchi		 |103
Massimo 	    |Rosciano		 |033
Nico		    |Bini		 |105
Nico		    |Bini		 |252

Statistics
----------------------------------------------------------
	  7  recursive calls
	  8  db block gets
	 72  consistent gets
	  0  physical reads
	  0  redo size
       1021  bytes sent via SQL*Net to client
	425  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  2  sorts (memory)
	  0  sorts (disk)
	 10  rows processed
----------------------------------------------------------*/
DROP VIEW Responsabili;
/*------------------------------------------------
Nome e cognome degli impiegati non assegnati ad alcun progetto
*/
SELECT 	I.Nome, I.Cognome
FROM   	Impiegato I
WHERE 	I.Matricola != ALL (SELECT P.Impiegato
                            FROM   Partecipazione P);
/* RISULTATO:
Per controllarlo:
SELECT Nome, Cognome, Matricola FROM Impiegato;
SELECT DISTINCT Impiegato	FROM Partecipazione;

NOME		     COGNOME
-------------------- --------------------
Nico		     Bini
Sergio		     Rossi
Mario		     Rossi
Silvia		     Zanzi
Massimo 	     Rosciano

Statistics
----------------------------------------------------------
	  0  recursive calls
	 72  db block gets
	 19  consistent gets
	  0  physical reads
	  0  redo size
	630  bytes sent via SQL*Net to client
	425  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  5  rows processed
----------------------------------------------------------*/
/*------------------------------------------------
ALTERNATIVA SBAGLIATA a 
Nome e cognome degli impiegati non assegnati ad alcun progetto
*/
SELECT 	I.Nome, I.Cognome
FROM   	Impiegato I
WHERE 	I.Matricola != (SELECT P.Impiegato
                        FROM   Partecipazione P);
/* Oracle segnala errore perche` I.Matricola e` un singolo valore,
mentre il risultato della query annidata e` un insieme di valori.
Ci sono interpreti in cui questo type checking implicito non 
esiste: il risultato prodotto e` errato. Produce un sovrainsieme
del risultato corretto?
*/

/*------------------------------------------------
Nome e cognome dei responsabili tali per cui
ogni subalterno diretto guadagni almeno 40 milioni
*/
SELECT	C.Nome, C.Cognome
FROM 	Impiegato C 
WHERE 	EXISTS (SELECT * 
	      	FROM Dipartimento D 
	      	WHERE D.Impiegato = C.Matricola)
AND NOT EXISTS (SELECT 	*
		FROM 	Impiegato S, Dipartimento D 
		WHERE 	S.DipNome   = D.Nome
		  AND 	S.DipSede   = D.Sede
		  AND 	C.Matricola = D.Impiegato
 		  AND   S.Stipendio < 40
               );	

/* RISULTATO:
NOME		     COGNOME
-------------------- --------------------
Siro		     Bisi
Sergio		     Rossi
Silvia		     Zanzi


Statistics
----------------------------------------------------------
	  0  recursive calls
	 32  db block gets
	 40  consistent gets
	  0  physical reads
	  0  redo size
	531  bytes sent via SQL*Net to client
	425  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  3  rows processed
----------------------------------------------------------*/

/*------------------------------------------------
VERSIONE ALTERNATIVA
*/
SELECT  C.Nome, C.Cognome
FROM 	Impiegato C
WHERE 	(C.Matricola = ANY (SELECT D.Impiegato 
                            FROM   Dipartimento D)
        )
AND 	(C.Matricola !=ALL (SELECT D.Impiegato
                             FROM   Dipartimento D, Impiegato S
			     WHERE  S.DipNome   = D.Nome
                               AND  S.DipSede   = D.Sede
                               AND  S.Stipendio < 40 
                            )
        );
/* RISULTATO:
Statistics
----------------------------------------------------------
	  0  recursive calls
	 32  db block gets
	110  consistent gets
	  0  physical reads
	  0  redo size
	531  bytes sent via SQL*Net to client
	425  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  3  rows processed
----------------------------------------------------------*/

/*------------------------------------------------
VERSIONE ALTERNATIVA
*/
SELECT 	C.Nome, C.Cognome
FROM 	Impiegato C
WHERE 	EXISTS (SELECT 	* 
              	FROM 	Dipartimento D
              	WHERE 	C.Matricola = D.Impiegato)
  AND 	C.Matricola NOT IN (SELECT D.Impiegato
                            FROM   Dipartimento D, Impiegato S
 			    WHERE  D.Nome  = S.DipNome
                              AND  D.Sede  = S.DipSede
                              AND  S.Stipendio < 40
                           );
/* RISULTATO:
Statistics
----------------------------------------------------------
	  0  recursive calls
	 32  db block gets
	110  consistent gets
	  0  physical reads
	  0  redo size
	531  bytes sent via SQL*Net to client
	425  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  3  rows processed
----------------------------------------------------------*/