Corso di Informatica (parte DB) - ing. Gestionale

SVOLGIMENTO PROVA PRATICA DEL 2/3/2011.

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


Nota: se x è un attributo di tipo 'date', per ottenere l'anno di x si puo' fare date_part('year', x) , e per ottenere il mese   date_part('month', x). 
Per concatenare due stringhe si utilizza l'operatore || (es: 'esa' || 'me'); 
per ottenere dalla stringa s la sottostringa lunga y caratteri partendo dal carattere di indice x, si utilizza  substring( s  from x for y).

--------------------------------
1) Elencare (in ordine alfabetico per ragione sociale) le ditte la cui ragione sociale contenga tra le prime 10 lettere sia una 'A', sia una 'I' (non distinguendo tra maiuscole e minuscole).

CREATE OR REPLACE VIEW risposta_1 AS

SELECT d.rag_sociale
FROM ditte d
WHERE substring(d.rag_sociale from 1 for 10) ILIKE '%A%' 
  AND substring(d.rag_sociale from 1 for 10) ILIKE '%I%'
ORDER BY d.rag_sociale
;

--------------------------------
2) Riportare per ogni anno il numero di fatture  emesse da quelle ditte che nel 2007 hanno fatto almeno una fattura al cliente 7.

CREATE OR REPLACE VIEW risposta_2 AS

SELECT date_part('year',f.data) AS "anno", d.rag_sociale, COUNT(*)
FROM ditte d NATURAL JOIN fatture f 
WHERE d.id_ditta IN (
       SELECT DISTINCT d0.id_ditta
       FROM ditte d0 NATURAL JOIN fatture f
       WHERE date_part('year',f.data)=2008
             AND f.id_cliente=7
       )
GROUP BY date_part('year',f.data), d.id_ditta, d.rag_sociale
ORDER BY anno, d.rag_sociale
;

--------------------------------
3) Relativamente all'anno 2007, riportare per ogni mese il fatturato mensile delle ditte che in tale anno hanno fatto almeno una fattura al cliente 7.


CREATE OR REPLACE VIEW risposta_3 AS

SELECT date_part('month',f.data) AS "mese", d.rag_sociale, SUM(a.costo_unitario*r.quantita_articolo) AS fatturato_mensile
FROM ditte d NATURAL JOIN fatture f 
     INNER JOIN righe_in_fattura r ON (f.id_fattura=r.id_fattura AND f.id_ditta=r.id_ditta)
     INNER JOIN articoli a ON (r.id_ditta=a.id_ditta AND r.sku_art_venduto=a.sku)
WHERE date_part('year',f.data)=2007
AND d.id_ditta IN (
       SELECT DISTINCT d0.id_ditta
       FROM ditte d0 NATURAL JOIN fatture f
       WHERE date_part('year',f.data)=2007
             AND f.id_cliente=7
       )
GROUP BY d.id_ditta, d.rag_sociale, date_part('month',f.data)
ORDER BY mese, d.rag_sociale
;

--------------------------------
4) (opz.) -- Svolger la query precedente considerando le ditte che nel 2007 hanno fatto almeno DUE fatture al cliente 7.

SELECT date_part('month',f.data) AS "mese", d.rag_sociale, SUM(a.costo_unitario*r.quantita_articolo) AS fatturato_mensile
FROM ditte d NATURAL JOIN fatture f 
     INNER JOIN righe_in_fattura r ON (f.id_fattura=r.id_fattura AND f.id_ditta=r.id_ditta)
     INNER JOIN articoli a ON (r.id_ditta=a.id_ditta AND r.sku_art_venduto=a.sku)
WHERE date_part('year',f.data)=2007
AND d.id_ditta IN (
       SELECT DISTINCT d.id_ditta
       FROM ditte d NATURAL JOIN fatture f
       WHERE date_part('year',f.data)=2007
             AND f.id_cliente=7
       GROUP BY d.id_ditta
       HAVING COUNT(*)>=1
       )
GROUP BY d.id_ditta, d.rag_sociale, date_part('month',f.data)
ORDER BY mese, d.rag_sociale
;