Corsi -> Basi di Dati e Sistemi Informativi I (Canale1) ->  Soluzioni proposte 1

Soluzioni proposte 1        Soluzione esercizio a - b - c - d - e - f - g - h - i - j - k

a)
SELECT Cognome, Nazionalita
FROM AUTORE
WHERE Codice-A IN
      (SELECT Codice-A
       FROM SCRITTO
       WHERE Codice-L IN
             (SELECT Codice-L
              FROM LIBRO
             WHERE Editore = ?Mc Graw Hill?))

La query piu? interna restituisce il codice di libri editi dalla casa ?Mc Graw Hill?. La query intermedia restituisce i codici degli autori dei libri che compaiono nel risultato della query interna. La query esterna estrae cognome e nazionalita? degli autori in base ai codici che compaiono nel risultato della query intermedia.

Torna all'inizio della pagina

b)
 SELECT Cognome, Nazionalita
 FROM AUTORE, SCRITTO, LIBRO
 WHERE AUTORE.Codice-A = SCRITTO.Codice-A
 AND SCRITTO.Codice-L = LIBRO.Codice-L
 AND Editore = ?Mc Graw Hill?

Occorre effettuare il join delle tre tabelle perche? cognome e nazionalita? dell?autore si trovano nella tabella AUTORE, l?editore si trova nella tabella LIBRO, e il legame e? dato dalla tabella SCRITTO.

Torna all'inizio della pagina

c)
SELECT Nome, Cognome
FROM AUTORE
WHERE Sesso = ?M?
AND Codice-A IN
         (SELECT Codice-A
          FROM SCRITTO, LIBRO
          WHERE SCRITTO.Codice-L = LIBRO.Codice-L
           AND LIBRO.Pagine > 300)

La query piu? interna restituisce i codici di autori che hanno scritto libri di piu? di 300 pagine (il legame e? dato dalla tabella SCRITTO). La query esterna restituisce nome e cognome degli autori di sesso maschile i cui codici rientrano in quelli restituiti dalla query interna.

Torna all'inizio della pagina

d)
 SELECT Titolo, Editore
 FROM LIBRO
 WHERE Codice-L NOT IN
           (SELECT Codice-L
            FROM LIBRILIB, LIBRERIA
            WHERE LIBRILIB.Libreria = LIBRERIA.Partita-Iva
             AND LIBRERIA.Nome = ?Feltrinelli?
             AND LIBRERIA.Citta = ?Roma?
             AND ( LIBRILIB.CopieM  > 0
                         OR LIBRILIB.CopieO > 0))

La query piu? interna restituisce i codici di libri venduti, cioe? che sono in magazino o in ordine (legame nella tabella LIBRILIB) nelle librerie Feltrinelli di Roma (nome e citta? nella tabella LIBRERIA). La query piu? esterna restituisce titolo ed editore di libri che in base al codice NON appartengono all?insieme restituito dalla query interna.
Una alternativa e? usare l?operatore IN modificando l?ultima condizione della query interna in
     AND  LIBRILIB.CopieM  = 0
     AND LIBRILIB.CopieO  = 0

Torna all'inizio della pagina

e)
 SELECT Nome, Indirizzo, Citta
 FROM LIBRERIA
 WHERE Partita-Iva IN
           (SELECT Libreria
            FROM LIBRILIB, SCRITTO, AUTORE
            WHERE LIBRILIB.Codice-L = SCRITTO.Codice-L
             AND SCRITTO.Codice-A = AUTORE.Codice-A
             AND Nazionalita = ?Italiana?
             AND (CopieM  > 0 OR CopieO  > 0))
 

Il join di LIBRILIB, SCRITTO e AUTORE raggruppa in una sola tabella i dati a disposizione della libreria su un certo libro e i dati dell?autore del libro (legame dato dalla tabella SCRITTO). Verifichiamo cosi? che almeno uno degli autori sia italiano (il libro comparira? nel join tante volte quanti sono gli autori) e che ci siano copie in magazzino o in ordine. La query esterna estrae nome, indirizzo e citta? delle librerie in base alla partita iva, se questa rientra nell?insieme restituito dalla query interna (che comunque puo? essere eseguita una sola volta, perche? non c?e? dipendenza dai dati della query esterna).

Una soluzione alternativa puo? essere la seguente

 SELECT Nome, Indirizzo, Citta
 FROM LIBRERIA
 WHERE Partita-Iva IN
           (SELECT Libreria
            FROM LIBRILIB, SCRITTO
            WHERE LIBRILIB.Codice-L = SCRITTO.Codice-L
             AND Codice-A IN
                       (SELECT Codice-A
                        FROM AUTORE
                        WHERE Nazionalita = ?Italiana?)
             AND (CopieM > 0 OR CopieO > 0))

La query piu? interna restituisce i codici di autori italiani. La query intermedia seleziona librerie, identificate dalla partita iva, in cui sono disponibili (CopieM > 0) o saranno disponibili (CopieO > 0) libri con almeno un autore (legame nella tabella SCRITTO) tra quelli restituiti dalla query interna. La query esterna estrae nome, indirizzo e citta? delle librerie in base alla partita iva, se questa e? nell?insieme restituito dalla query intermedia.
 

Torna all'inizio della pagina

f)
 SELECT Nome, Indirizzo
 FROM LIBRERIA
 WHERE Citta = ?Roma?
 AND Partita-Iva IN
          (SELECT Libreria
           FROM LIBRILIB, SCRITTO
           WHERE LIBRILIB.Codice-L = SCRITTO.Codice-L
           AND Codice-A = (SELECT Codice-A
                                          FROM AUTORE
                                          WHERE Cognome = ?Eco?
                                           AND Nome = ?Umberto? )
           AND LIBRILIB.CopieM = 0
           AND LIBRILIB.CopieO > 0 )

La query piu? interna seleziona il codice dell?autore Umberto Eco. La query intermedia seleziona i libri di Eco in base al codice autore (il legame e? nella tabella SCRITTO) e le partite iva delle librerie che non li hanno disponibili ma in ordine (identificate dalla tabella LIBRILIB). La query esterna estrae, in base alla partita iva, nome e indirizzo delle librerie di Roma che ci interessano.

Torna all'inizio della pagina

g)
 SELECT Titolo, Nome, Cognome
 FROM LIBRO, AUTORE, SCRITTO
 WHERE LIBRO.Codice-L = SCRITTO.Codice-L
 AND SCRITTO.Codice-A = AUTORE.Codice-A
 AND Sesso = ?F?
 AND LIBRO.Codice-L NOT IN
              (SELECT Codice-L
               FROM SCRITTO, AUTORE
               WHERE SCRITTO.Codice-A = AUTORE.Codice-A
                AND AUTORE.Sesso = ?M?)
 AND LIBRO.Codice-L IN
               (SELECT Codice-L
                FROM LIBRILIB, LIBRERIA
                WHERE Libreria = Partita-Iva
                 AND Citta = ?Roma?
                 AND LIBRILIB.CopieM  > 0)
ORDERED BY Titolo
 

La prima query interna restituisce codici di libri con almeno un autore maschio, che vanno esclusi. La seconda query interna restituisce i libri disponibili in librerie di Roma (citta? nella tabella LIBRERIA, legame dato dalla tabella LIBRILIB). La query esterna restituisce titolo (tabella LIBRO), nome e cognome dell?autore (tabella AUTORE, legame dato dalla tabella SCRITTO) dei libri il cui codice soddisfa gli operatori applicati ai risultati delle query interne.
 

Torna all'inizio della pagina

h)
 SELECT Nome, Indirizzo, Citta
 FROM LIBRERIA
 WHERE Partita-Iva IN
              (SELECT Libreria
               FROM LIBRILIB, SCRITTO
               WHERE LIBRILIB.Codice-L = SCRITTO.Codice-L
                AND  SCRITTO.Codice-A IN
                           (SELECT Codice-A
                            FROM AUTORE
                            WHERE Nazionalita = ?Tedesca?)
               AND CopieV > 10)

La query piu? interna estrae i codici di autori di nazionalita? tedesca. La query intermedia estrae i libri scritti da questi autori (il legame e? dato dalla tabella SCRITTO) e le partite iva di librerie che ne hanno vendute piu? di 10 copie (legame dato dalla tabella LIBRILIB). La query piu? esterna estrae in base a queste partite iva nome, indirizzo e citta? delle librerie che ci interessano.

Torna all'inizio della pagina

i)
 SELECT Nome, Indirizzo, Citta
 FROM LIBRERIA
 WHERE Partita-Iva IN
              (SELECT Libreria
               FROM LIBRILIB L
               WHERE 50 <
                       (SELECT SUM(L1.CopieM)
                        FROM LIBRILIB L1
                        WHERE L1.Libreria = L.Libreria
                         AND L1.Codice-L IN
                                    (SELECT Codice-L
                                     FROM SCRITTO, AUTORE
                                     WHERE SCRITTO.Codice-A = AUTORE.Codice-A
                                      AND AUTORE.Nazionalita = ?Giapponese?)))
 

Per chiarezza numeriamo le query in base al livello di annidamento. La query piu? interna (la n. 4) restituisce i codici dei libri con almeno un autore di nazionalita? giapponese. Nella query n.3, PER OGNI ISTANZA DI LIBRERIA NELLA QUERY N.2 (notare il passaggio di binding della variabile L) identificata in base alla partita iva, si calcola la somma delle copie in magazzino (tabella LIBRILIB) di libri i cui codici sono restituiti dalla query n.4. Nella query n.2 si controlla che questo numero complessivo di copie sia > 50. Nella query piu? esterna (n. 1) si estrae nome, indirizzo e citta? dalla tabella LIBRERIA in base alla partita iva, se questa rientra nell?insieme  restituito dalla query n. 2.
Da notare che non ci saranno libri che contribuiranno piu? volte al conto delle copie a causa di piu? coautori giapponesi, in quanto nella tabella LIBRILIB ogni libro compare una sola volta, e lo consideriamo o meno per la somma in base al fatto che il suo codice compaia o meno tra quelli restituiti dalla query n. 4.
 
 
 
 

Torna all'inizio della pagina

j)
 SELECT Nome, Indirizzo, Citta
 FROM LIBRERIA
 WHERE Partita-Iva IN
              (SELECT Libreria
               FROM LIBRILIB L
               WHERE 50 <
                           (SELECT SUM(CopieM)
                            FROM LIBRILIB, SCRITTO
                            WHERE LIBRILIB.Libreria = L.Libreria
                            AND LIBRILIB.Codice-L = SCRITTO.Codice-L
                            AND SCRITTO.Codice-A IN
                                            (SELECT Codice-A
                                             FROM AUTORE
                                             WHERE Nazionalita = ?Giapponese?)))

Per chiarezza numeriamo le query in base al livello di annidamento. La query piu? interna (la n. 4) restituisce i codici degli autori di nazionalita? giapponese. Nella query n.3, PER OGNI ISTANZA DI LIBRERIA NELLA QUERY N.2  (notare il passaggio di binding della variabile L) identificata in base alla partita iva, si calcola la somma delle copie vendute (tabella LIBRILIB) di libri scritti da autori giapponesi (tabella SCRITTO) i cui codici sono restituiti dalla query n.4.  Il problema e? che nel join tra LIBRILIB e SCRITTO un libro con un certo codice compare in tante righe quanti sono gli autori. In particolare, data la condizione aggiuntiva sulla nazionalita? dell?autore contenuto nella singola riga, il libro comparira? tante volte quanti sono i suoi autori giapponesi. Di conseguenza, puo? succedere che venga contato piu? di una volta nella somma delle copie.

Torna all'inizio della pagina

k)
 SELECT Nome, Indirizzo, Citta
 FROM LIBRERIA
 WHERE Partita-Iva IN
              (SELECT Libreria
               FROM LIBRILIB L
               WHERE 50 <
                               (SELECT SUM(L1.CopieV)
                                FROM LIBRILIB L1
                                WHERE L1.Libreria = L.Libreria
                                 AND L1.Codice-L NOT IN
                                                (SELECT Codice-L
                                                 FROM SCRITTO, AUTORE
                                                 WHERE SCRITTO.Codice-A = AUTORE.Codice-A
                                                  AND (Sesso = ?F?
                                                             OR Nazionalita < > ?Americano?))))

Per chiarezza numeriamo le query in base al livello di annidamento. La query piu? interna (la n. 4) restituisce i codici dei libri con almeno un autore di sesso femminile o di nazionalita? non americana. Nella query n.3, PER OGNI ISTANZA DI LIBRERIA NELLA QUERY N.2  (notare il passaggio di binding della variabile L) identificata in base alla partita iva, si calcola la somma delle copie vendute (tabella LIBRILIB) di libri i cui codici NON compaiono nell?insieme restituito dalla query n.4. Nella query n.2 si controlla che questo numero complessivo di copie sia > 50. Nella query piu? esterna (n. 1) si estrae nome, indirizzo e citta? dalla tabella LIBRERIA in base alla partita iva restituita dalla query n. 2.

Torna all'inizio della pagina