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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.