Corso di Informatica (parte DB) - ing. Gestionale

SVOLGIMENTO PROVA PRATICA DEL 25/01/2010

Il db da utilizzare e' quello denominato "dbfatture" presente sul sito del corso.

---------------------------
Domanda_1: Per ogni ditta, riportare sia il numero di fatture emesse, sia il numero dei clienti a cui è stata fatta almeno una fattura.

CREATE OR REPLACE VIEW risposta_1 AS 
   SELECT d.id_ditta, 
          COUNT(f.id_cliente) AS numfatture, 
          COUNT(DISTINCT f.id_cliente) AS numclienti
   FROM ditte d LEFT OUTER JOIN fatture f ON d.id_ditta = f.id_ditta
   GROUP BY d.id_ditta;

Da notare: l'utilizzo del left outer join ci permette di avere nell'elenco anche le ditte che non hanno emesso fatture, con il relativo conteggio a 0.

---------------------------
Domanda_2: Fornire la ragione sociale dei clienti che hanno almeno due fatture di due ditte diverse.

CREATE OR REPLACE VIEW risposta_2 AS 
   SELECT c.rag_sociale
   FROM clienti c NATURAL JOIN fatture f
   GROUP BY c.id_cliente, c.rag_sociale
   HAVING COUNT(DISTINCT f.id_ditta) >= 2;

---------------------------
Domanda_3: Quando è stata emessa l'ultima fattura ad un cliente con residenza in provincia di Lucca (sigla LU)?

CREATE OR REPLACE VIEW risposta_3 AS 
   SELECT MAX(f.data) AS ultimadata
   FROM fatture f NATURAL JOIN clienti c
   WHERE c.provincia = 'LU';

---------------------------
Domanda_4: (opzionale) Svolgere in modo alternativo la query 2.

Per esempio, si puo' fare il join di "clienti" con due copie della tabella "fatture", e imporre che i due campi id_ditta del risultato (provenienti ciascuno da una copia di "fatture") siano diversi: in questo modo, rimangono soltanto tuple con i dati dei clienti che soddisfano la condizione richiesta. In pratica:

CREATE OR REPLACE VIEW risposta_4 AS 
   SELECT DISTINCT c.rag_sociale
   FROM clienti c
        JOIN fatture f1 ON c.id_cliente = f1.id_cliente
        JOIN fatture f2 ON c.id_cliente = f2.id_cliente
   WHERE f1.id_ditta <> f2.id_ditta;

Un altro modo puo' sfruttare, per esempio, una tabella temporanea che contenga una tupla con id_cliente e id_ditta se esiste almeno una fattura di tale ditta intestata a tale cliente:

CREATE OR REPLACE VIEW risposta_4_bis AS 
   WITH cliente_con_ditta(id_cliente, id_ditta) AS (
      SELECT DISTINCT f0.id_cliente, f0.id_ditta
      FROM fatture f0
   )
   SELECT c.rag_sociale
   FROM clienti c JOIN cliente_con_ditta cd ON c.id_cliente = cd.id_cliente
   GROUP BY c.id_cliente, c.rag_sociale
   HAVING count(*) >= 2;

Infine riportiamo anche una formulazione che fa ricorso a una sottoquery correlata:

CREATE OR REPLACE VIEW risposta_4_ter AS 
   SELECT c.rag_sociale
   FROM clienti c
   WHERE 2 <= ( SELECT count(*) AS count
                 FROM ( SELECT DISTINCT f.id_ditta
                        FROM fatture f
                        WHERE c.id_cliente = f.id_cliente) temp
                      )
              );