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

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)
 

Torna all'inizio della pagina

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.
 

Torna all'inizio della pagina

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.
 

Torna all'inizio della pagina

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.
 

Torna all'inizio della pagina

e)        Vedi anche ERRATA CORRIGE

 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.

ERRATA CORRIGE

È 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
 
 

Torna all'inizio della pagina

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.
 

Torna all'inizio della pagina

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
 

Torna all'inizio della pagina

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)

Torna all'inizio della pagina

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.

Torna all'inizio della pagina