/* \i 'H:\\0-BasidiDati_2024-25\\Moodle_2-SoluzioniDiEsercizi\\Musiche-3-CanaA_Query.sql' */

\qecho '\nDati usati per il test:\n'

SELECT * FROM PERSONE;

SELECT * FROM ALBUM;

SELECT * FROM CANZONI;

SELECT * FROM ALBUM_CANZO;

\qecho '\n\nG. Elencate gli identificatori degli album che contengono canzoni che sono tutte di un solo cantante'
\qecho '     e che contengono almeno tre canzoni di anni precedenti all''anno dell''album.'
/* Risolto da: Gianmarco Zin e altri */
/* Ogni album cantato da un unico cantante */
SELECT AC.IdeAlbum
FROM ALBUM_CANZO AS AC JOIN CANTANTI_CANZO AS CC ON AC.IdeCanzone = CC.IdeCanzone
GROUP BY AC.IdeAlbum
HAVING COUNT(DISTINCT(CC.NomePersona)) = 1
  INTERSECT
/* Ogni album contenente almeno 3 canzoni di anni precedenti l''album */
SELECT AC.IdeAlbum 
FROM ALBUM_CANZO AS AC JOIN CANZONI AS C ON AC.IdeCanzone = C.IdeCanzone
  JOIN ALBUM AS A ON AC.IdeAlbum = A.IdeAlbum
WHERE C.AnnoCanzone < A.AnnoAlbum
GROUP BY AC.IdeAlbum
HAVING COUNT(*) >= 3;

\qecho '\n\nH. Elencate i nomi dei cantanti che non hanno mai cantato in un intero album da soli.'
/* Risolto da: Gianmarco Zin */
/* Ogni cantante */
SELECT DISTINCT NomePersona
FROM CANTANTI_CANZO
  EXCEPT
/* Ogni cantante che ha cantato in un intero album da solo */
SELECT DISTINCT CC.NomePersona
FROM ALBUM_CANZO AS AC JOIN CANTANTI_CANZO AS CC ON AC.IdeCanzone = CC.IdeCanzone 
WHERE AC.IdeAlbum IN ( /* Ogni album cantato da un unico cantante */
    SELECT AC.IdeAlbum
    FROM ALBUM_CANZO AS AC JOIN CANTANTI_CANZO AS CC ON AC.IdeCanzone = CC.IdeCanzone
    GROUP BY AC.IdeAlbum
    HAVING COUNT(DISTINCT(CC.NomePersona)) = 1
);

\qecho '\n\nI. Elencate i nomi dei cantanti che non hanno mai cantato una canzone come solisti.'
/* Risolto da: Marco Stradiotto e altri */
/* Ogni cantante. */
SELECT DISTINCT NomePersona 
FROM CANTANTI_CANZO
  EXCEPT
/* Ogni cantante che ha cantato da solista in almeno una canzone. */
SELECT NomePersona
FROM CANTANTI_CANZO 
WHERE IdeCanzone IN ( /* Identificativo di ogni canzone cantata da un unico cantante. */
    SELECT IdeCanzone
    FROM CANTANTI_CANZO
    GROUP BY IdeCanzone
    HAVING COUNT(NomePersona) = 1
);

\qecho '\n\nJ. Elencate i nomi dei cantanti che hanno cantato soltanto come solisti.'
/* Risolto da: Fioranzato Nicolò */
/* Ogni cantante. */
SELECT C_C.NomePersona
FROM CANTANTI_CANZO AS C_C
  EXCEPT
/* Ogni cantante di una canzone cantata da almeno due cantanti. */
SELECT C_C.NomePersona
FROM CANTANTI_CANZO AS C_C
WHERE C_C.IdeCanzone IN ( /* Canzoni cantate da almeno due cantanti. */
    SELECT C_C.IdeCanzone
    FROM CANTANTI_CANZO AS C_C
    GROUP BY C_C.IdeCanzone
    HAVING COUNT(*) > 1
);

\qecho '\n\nJ. K. Per ogni identificatore di canzone,
elencate l’identificatori di ogni album nel quale la canzone e` stata pubblicata per prima
e mostrate l’anno di questa pubblicazione.'
/* Risolto da: Fioranzato Nicolò */
SELECT AC.IdeCanzone, AC.IdeAlbum, A.AnnoAlbum
FROM ALBUM AS A JOIN ALBUM_CANZO AS AC ON A.IdeAlbum = AC.IdeAlbum
WHERE A.AnnoAlbum = ( /* Anno minimo tra gli album che contengono A.IdeCanzone. */
    SELECT MIN(A.AnnoAlbum)
    FROM ALBUM AS A JOIN ALBUM_CANZO AS AC2 ON A.IdeAlbum = AC2.IdeAlbum
    WHERE AC.IdeCanzone = AC2.IdeCanzone
);