Soluzioni proposte 2
Soluzione esercizio a - b - c
- d - e - f - g
- h - i
a)
SELECT Codice
FROM DISCO
WHERE Codice NOT IN
(SELECT CodiceDisco
FROM VENDITA)
La query interna restituisce i codici di dischi di cui esistono dati
nella tabella VENDITA. La query esterna esclude questi codici dal risultato;
oppure
SELECT Codice
FROM DISCO
EXCEPT
(SELECT CodiceDisco
FROM VENDITA)
b)
SELECT DISTINCT D.Cantante
FROM DISCO D, PRODUTTORE P
WHERE D.CodiceProd = P.Sigla
AND EXISTS
(SELECT *
FROM DISCO D1, PRODUTTORE P1
WHERE D1.CodiceProd = P1.Sigla
AND D1.Cantante = D.Cantante
AND P1.Sede < > P.Sede)
La query interna viene eseguita per ogni riga della query esterna (passaggio
di binding delle variabili D e P). Per ogni cantante con un disco prodotto
in una certa sede (collegamento tra le tabelle DISCO che contiene il cantante
e PRODUTTORE che contiene la sede), controlliamo se esistono dischi dello
stesso cantante prodotti in sedi diverse. Nella query esterna prendiamo
solo le tuple per cui questa condizione eí soddisfatta. DISTINCT evita
di avere nel risultato ripetizioni del nome del cantante. Nel join infatti
il nome del cantante compare tante volte quanti sono i dischi, e se per
esempio ha prodotto solo due dischi ma con due produttori diversi, il nome
verraí estratto prima dalla riga di join relativa al primo disco e poi
da quella relativa al secondo.
c)
SELECT D.Cantante, P.Sede
FROM DISCO D, PRODUTTORE P
WHERE D.CodiceProd = P.Sigla
AND NOT EXISTS
(SELECT *
FROM DISCO D1, PRODUTTORE P1
WHERE D1.CodiceProd = P1.Sigla
AND D1.Cantante = D.Cantante
AND P1.Sede < > P.Sede)
GROUP BY D.CANTANTE, P.Sede
La query interna viene eseguita per ogni riga della query esterna (passaggio
di binding delle variabili D e P). Per ogni cantante con un disco prodotto
in una certa sede (collegamento tra le tabelle DISCO che contiene il cantante
e PRODUTTORE che contiene la sede), controlliamo se esistono dischi dello
stesso cantante prodotti in sedi diverse. Nella query esterna prendiamo
solo le tuple per cui questa condizione NON eí soddisfatta. Il GROUP BY
ci evita duplicati nel risultato. I duplicati sono dovuti a tutte le righe
del join in cui un cantante compare associato ad una certa sede.
d)
SELECT D.Cantante, P.Sede
FROM DISCO D, PRODUTTORE P
WHERE D.CodiceProd = P.Sigla
AND EXISTS
(SELECT *
FROM DISCO D1, PRODUTTORE P1
WHERE D1.CodiceProd = P1.Sigla
AND D1.Cantante = D.Cantante
AND P1.Sede = P.Sede
AND D1.Codice < > D.Codice)
La query interna restituisce tuple relative a dischi dello stesso cantante
prodotti nella stessa sede di quello della tupla esaminata nella query
esterna. Quindi se il risultato non eí vuoto significa che il cantante
ha inciso almeno due dischi nella stessa sede. Questo peroí non esclude
la possibilitaí che ne abbia incisi altri in sedi diverse.
SELECT D1.Cantante, P1.Nome, P2.Nome
FROM DISCO D1, DISCO D2, PRODUTTORE P1, PRODUTTORE P2
WHERE D1.CodiceProd = P1.Sigla
AND D2.CodiceProd = P2.Sigla
AND D1.Cantante = D2.Cantante
AND P1.Sigla < P2.Sigla
AND NOT EXISTS
(SELECT *
FROM DISCO D3
WHERE D3.Cantante = D1.Cantante
AND D3.CodiceProd < > D1.CodiceProd
AND D3.CodiceProd < > D2.CodiceProd)
Poicheí nel risultato sono richieste le coppie di nomi di produttori, operiamo un join tra due istanze della tabella DISCO e due istanze della tabella PRODUTTORE. Infatti il legame tra produttore e cantante eí giaí nella tabella DISCO, ma il nome del produttore eí nella tabella PRODUTTORE. Inoltre cerchiamo coppie di nomi di produttori, che saranno associati a coppie di dischi. Per evitare di avere i duplicati nelle coppie di nomi di produttori (Tizio, Caio e poi Caio,Tizio), rinforziamo la condizione di disuguaglianza tra le sigle con un < stretto che ci fa selezionare solo le coppie in cui la prima sigla precede il ordine alfabetico o numerico la seconda. Questa prima parte della query ci seleziona cantanti che hanno inciso dischi con ALMENO due produttori. Noi cerchiamo peroí cantati che abbiano inciso dischi con ESATTAMENTE due produttori. Per ogni cantante dobbiamo quindi escludere che esista un disco inciso con un produttore diverso dai primi due individuati (passaggio di binding delle variabili D1 e D2). A questo scopo nella query interna non cíeí bisogno di un ulteriore join percheí tutti i dati che ci servono per il confronto, e in particolare il codice del produttore, sono nella tabella DISCO.
È stato osservato da uno studente che la soluzione proposta elimina
dal join sia le tuple in cui compare due volte lo stesso produttore (generate
da due dischi diversi con lo stesso produttore o dallo stesso disco ripetuto
due volte) sia quelle in cui compare una semplice inversione dell'ordine
della coppia di sigle (entrambe le situazioni sono controllate con
la condizione P1.Sigla < P2.Sigla). Non vengono però eliminati
i duplicati delle tuple con una certa coppia di sigle che compare nello
stesso ordine (possiamo cioè avere nel risultato piu' tuple del
tipo per esempio <Tizio, EMI, WEA> ). Questo caso si verifica quando
il cantante ha inciso più dischi con uno e/o con l'altro dei produttori.
Avendo eliminato i duplicati per inversione delle sigle, è
corretto eliminare anche gli altri, utilizzando ad esempio GROUP BY.
Quindi una possibile soluzione è
SELECT D1.Cantante, P1.Nome, P2.Nome
FROM DISCO D1, DISCO D2, PRODUTTORE P1, PRODUTTORE P2
WHERE D1.CodiceProd = P1.Sigla
AND D2.CodiceProd = P2.Sigla
AND D1.Cantante = D2.Cantante
AND P1.Sigla < P2.Sigla
AND NOT EXISTS
(SELECT *
FROM DISCO D3
WHERE D3.Cantante = D1.Cantante
AND D3.CodiceProd < > D1.CodiceProd
AND D3.CodiceProd < > D2.CodiceProd)
GROUP BY D1.Cantante, P1.Nome, P2.Nome
f)
SELECT D1.Cantante, P1.Nome, P2.Nome
FROM DISCO D1, PRODUTTORE P1
WHERE D1.CodiceProd = P1.Sigla
AND EXISTS
(SELECT *
FROM DISCO D2, PRODUTTORE P2
WHERE D2.CodiceProd = P2.Sigla
AND D2.Cantante = D1.Cantante
AND P2.Sigla < P1.Sigla)
AND NOT EXISTS
(SELECT *
FROM DISCO D3
WHERE D3.Cantante = D1.Cantante
AND D3.CodiceProd < > D1.CodiceProd
AND D3.CodiceProd < > D2.CodiceProd)
Líerrore eí nelle variabili usate per il passaggio di binding. Una variabile
eí visibile nellíambito di una query o di quelle in essa annidate. In pratica
una variabile dichiarata in una query annidata non eí visibile alla query
esterna, neí a query dello stesso livello di annidamento. Quindi la query
esterna non vede la variabile P2, dichiarata nella prima query annidata,
mentre la seconda query annidata non vede la variabile D2 dichiarata sempre
nella prima query annidata.
g)
SELECT Titolo, Cantante, SUM(Copie)
FROM DISCO, VENDITA
WHERE DISCO.Codice = VENDITA.CodiceDisco
GROUP BY Titolo, Cantante
HAVING SUM(Copie) > 100.000
Il numero di copie venduto per ogni anno eí nella tabella VENDITA, mente
titolo e cantante del disco sono nella tabella DISCO. Col join quindi individuiamo
in righe diverse i dati di vendita per anno per una certa coppia (Titolo,
Cantante). Raggruppiamo quindi le righe in base a questi due attributi,
e calcoliamo la somma complessiva delle copie per ogni sottoinsieme cosií
individuato. Nel risultato includiamo solo i sottoinsiemi per i quali questa
somma eí > 100.000
h)
Poicheí non eí possibile annidare operatori aggregati (ad esempio líespressione
MAX(SUM x) eí sintatticamente scorretta), creiamo prima una opportuna vista
che ci registra per ogni disco la somma delle copie vendute.
CREATE VIEW VENDITATOT AS
SELECT CodiceDisco,
SUM(Copie) AS CopieTot
FROM VENDITA
GROUP BY CodiceDisco
La vista VENDITATOT ci fornisce per ogni disco identificato dal proprio codice la somma delle copie vendute nei vari anni.
Impostiamo ora la nostra query
SELECT Titolo, Cantante, SUM(Copie)
FROM DISCO, VENDITA
WHERE Codice = CodiceDisco
AND Codice IN
(SELECT CodiceDisco
FROM VENDITATOT
WHERE CopieTot =
(SELECT MAX(CopieTot)
FROM VENDITATOT))
GROUP BY Titolo, Cantante
La query piuí interna estrae il massimo di CopieTot dalla vista VENDITATOT. La query intermedia estrae dalla stessa vista i codici di dischi che hanno un numero complessivo di copie vendute uguale a questo massimo. Nella query esterna vengono selezionate dal join tra DISCO e VENDITA (Titolo e Cantante sono nella tabella DISCO) le righe con codici che rientrano nel risultato della query intermedia. Le righe vengono raggruppate per Titolo e Cantante per poter effettuare correttamente la somma delle copie vendute.
In alternativa si poteva creare una vista con piuí informazioni:
CREATE VIEW VENDITATOT AS
SELECT Codice, Titolo, Cantante, SUM(Copie) AS CopieTot
FROM DISCO, VENDITA
WHERE Codice = CodiceDisco
GROUP BY Codice, Titolo, Cantante
E usare la seguente query
SELECT Titolo, Cantante, CopieTot
FROM VENDITATOT
WHERE CopieTot =
(SELECT MAX(CopieTot)
FROM VENDITATOT)
i)
Creiamo una vista per avere disponibile per ogni anno la somma delle
copie di dischi vendute
CREATE VIEW SUMANNO AS
SELECT Anno, SUM(Copie) AS CopieAnno
FROM VENDITA
GROUP BY Anno
Impostiamo poi la nostra query
SELECT Titolo, Autore, V.Copie, V.Anno
FROM DISCO, VENDITA V
WHERE Codice = V.CodiceDisco
AND V.Anno IN
(SELECT Anno
FROM SUMANNO
WHERE CopieAnno =
(SELECT MAX(CopieAnno)
FROM SUMANNO))
AND V.Copie =
(SELECT MAX(Copie)
FROM VENDITA V1
WHERE V1.Anno = V.Anno)
ORDERED BY V.Anno
Nella prima query annidata, che puoí essere eseguita una sola volta, andiamo a selezionare gli anni (potrebbero essere piuí di uno) in cui eí stato venduto complessivamente il maggior numero di dischi. Nella seconda query annidata, per ogni anno che compare nella query esterna (passaggio di binding nella variabile V) selezioniamo selezioniamo il massimo di copie vendute (che potrebbe essere associato a piuí di un disco). In pratica per ogni riga del join esterno controlliamo che líanno rientri tra quelli col massimo delle vendite complessive, e che il numero di copie vendute del disco sia uguale al massimo per quellíanno.