Corso di Informatica (parte DB) - ing. Gestionale

SVOLGIMENTO PROVA PRATICA DEL 20/02/2010

Il db da utilizzare e' quello denominato "dbfatture" presente sul sito del corso.
Nota - Con x attributo di tipo SQL 'date', l'anno di x si puo' ottenere tramite la funzione: date_part('year',x)

---------------------------
Domanda_1: Per ogni cittą che contenga la lettera 'a' (o 'A') ma non la lettera 'l' (o 'L'), 
           riportare il numero di clienti. 
           Si supponga che non esistano due cittą con lo stesso nome.

CREATE OR REPLACE VIEW risposta_1 AS
   SELECT c.citta, COUNT(*) AS num_clienti
   FROM clienti c
   WHERE c.citta ILIKE '%a%' AND NOT c.citta ILIKE '%l%'
   GROUP BY c.citta
; 

---------------------------
Domanda_2: Trovare la ragione sociale della ditta che ha emesso il maggior numero di fatture nel 2007.

CREATE OR REPLACE VIEW risposta_2 AS
   WITH ditte_numfatture(id_ditta, rag_sociale, num_fatture) AS (
        SELECT d.id_ditta, d.rag_sociale, count(*) AS count
        FROM ditte d NATURAL JOIN fatture f
        WHERE date_part('year', f.data) = 2007
        GROUP BY d.id_ditta, d.rag_sociale
        )
   SELECT dn.id_ditta, dn.rag_sociale
   FROM ditte_numfatture dn
   WHERE dn.num_fatture = ( SELECT MAX(dn.num_fatture) AS max
                            FROM ditte_numfatture dn )
;
 
---------------------------
Domanda_3: Elencare le ditte della provincia di Pisa (sigla PI) con i rispettivi fatturati del 2007.

CREATE OR REPLACE VIEW risposta_3 AS
   SELECT d.id_ditta, d.rag_sociale, SUM(rif.quantita_articolo * a.costo_unitario) AS fatt_2007
   FROM ditte d NATURAL JOIN fatture f NATURAL JOIN righe_in_fattura rif
        INNER JOIN articoli a ON 
              (rif.id_ditta = a.id_ditta AND rif.sku_art_venduto = a.sku)
   WHERE date_part('year', f.data) = 2007 AND d.provincia = 'PI'
   GROUP BY d.id_ditta, d.rag_sociale
; 

---------------------------
Domanda_4: (opzionale) Elencare in ordine alfabetico decrescente i clienti della ditta 2.

CREATE OR REPLACE VIEW risposta_4 AS
   SELECT DISTINCT c.rag_sociale
   FROM clienti c NATURAL JOIN fatture f
   WHERE f.id_ditta = 2
   ORDER BY c.rag_sociale DESC
;