Corso di Informatica (parte DB) - ing. Gestionale

SVOLGIMENTO PROVA PRATICA DEL 09/01/2010

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

---------------------------
Domanda_1: Elencare tutti gli articoli la cui descrizione contenga la lettara 'a' e che non sono mai stati venduti.

CREATE OR REPLACE VIEW risposta_1 AS 
   SELECT a.id_ditta, a.sku, a.descrizione
   FROM articoli a
   WHERE a.descrizione ILIKE '%a%' 
         AND NOT ( (a.id_ditta, a.sku) IN 
                     ( SELECT DISTINCT r.id_ditta, r.sku_art_venduto
                       FROM righe_in_fattura r
                     )
                 )
;

---------------------------
Domanda_2: Fornire l'elenco delle cittą in cui hanno sede le ditte da cui ha fatto acquisti (fatturati) il cliente 2.

CREATE OR REPLACE VIEW risposta_2 AS 
   SELECT DISTINCT d.citta
   FROM ditte d INNER JOIN fatture f ON d.id_ditta = f.id_ditta
   WHERE f.id_cliente = 2;

---------------------------
Domanda_3: Elencare id_ditta e rag_sociale delle ditte che hanno almeno un articolo che non e' mai stato venduto.

CREATE OR REPLACE VIEW risposta_3 AS 
   WITH id_ditte_cercate AS (
     SELECT DISTINCT a.id_ditta
     FROM articoli a
     WHERE NOT ( (a.id_ditta, a.sku) IN 
                   ( SELECT DISTINCT r.id_ditta, r.sku_art_venduto
                     FROM righe_in_fattura r
                   )
               )
        )

   SELECT d.id_ditta, d.rag_sociale
   FROM ditte d NATURAL JOIN id_ditte_cercate;

---------------------------
Domanda_4: (opzionale) Inserire un vincolo che impedisca di vendere articoli non presenti in listino (ovvero contenuti nella tabella articoli).

Si puo' inserire un ulteriore vincolo di foreign key nella tabella "righe_in_fattura" in modo che l'articolo presente in ciascuna riga (identificato univocamente dalla coppia di campi (id_ditta, sku_art_venduto) ) debba necessariamente essere presente nella tabella "articoli". In definitiva:

ALTER TABLE righe_in_fattura
  ADD CONSTRAINT risposta_4_art_esistenti 
      FOREIGN KEY (id_ditta, sku_art_venduto)
      REFERENCES articoli (id_ditta, sku)
      ON UPDATE RESTRICT ON DELETE RESTRICT;