Composers, pieces and concerts¶

SQL exercises · Part I

Problem¶

A database of composers, the pieces they wrote and the concerts in which pieces are played (Programmazione says which piece is played in which concert, and in which position). Write four queries on it.

Schema and test data¶

CREATE TABLE Compositori ( codice integer NOT NULL PRIMARY KEY,
                           cognome text ,
                           nome text );
CREATE TABLE Pezzi ( codice integer NOT NULL PRIMARY KEY,
                     titolo text,
                     autore integer NOT NULL REFERENCES Compositori,
                     durata integer);
CREATE TABLE Concerti ( codice integer NOT NULL PRIMARY KEY,
                        titolo text ,
                        descrizione text );
CREATE TABLE Programmazione ( pezzo integer NOT NULL REFERENCES Pezzi,
                              concerto integer NOT NULL REFERENCES Concerti,
                              posizione integer,
                              PRIMARY KEY(pezzo, concerto) );

-- Test data (not part of the exercise): two composers named Rossi, a composer
-- with no pieces, a piece never played, and a tie for the most pieces played.
INSERT INTO Compositori VALUES (1, 'Rossi', 'Gioachino'), (2, 'Rossi', 'Luigi'),
                               (3, 'Bianchi', 'Anna'), (4, 'Verdi', 'Giuseppe');
INSERT INTO Pezzi VALUES (10, 'Overture A', 1, 8), (11, 'Aria B', 1, 4),
                         (12, 'Sonata C', 2, 12), (13, 'Studio D', 3, 6),
                         (14, 'Notturno E', 3, 7), (15, 'Preludio F', 2, 3);
INSERT INTO Concerti VALUES (100, 'Spring', 'Season opening'), (101, 'Summer', 'Open air');
INSERT INTO Programmazione VALUES (10, 100, 1), (11, 100, 2), (13, 100, 3),
                                  (10, 101, 1), (14, 101, 2), (12, 101, 3);

The schema is the one given in the exercise; the test data are mine.

Queries¶

Query 1 · Long pieces by composers named Rossi¶

Find the pieces longer than 5 whose author is a composer with surname 'Rossi'. Show the composer's code and the code, title and length of the piece; order by composer and piece.

SELECT C1.codice AS codiceCompositore, C2.codice AS codicePezzo, C2.titolo, C2.durata
FROM Compositori AS C1
JOIN Pezzi AS C2
ON C1.codice = C2.autore

WHERE C2.durata > 5  AND C1.cognome = 'Rossi'
ORDER BY C1.codice, C2.codice

Query 2 · Pieces never played¶

Find code and title of the pieces that are not in any concert, ordered by code.

SELECT C1.codice, C1.titolo
FROM Pezzi AS C1
LEFT JOIN Programmazione AS C2
ON C1.codice = C2.pezzo

WHERE C2.pezzo IS NULL
ORDER BY C1.codice

The anti-join pattern: an outer join, then keep the rows where the optional side is missing.

Query 3 · Distinct pieces played, per composer¶

For each composer, the number of different pieces played in at least one concert. Ignore composers with no piece played; show code, surname, name and number of pieces.

SELECT C1.codice, C1.cognome, C1.nome, COUNT(DISTINCT C3.pezzo) AS numeroPezzi
FROM Compositori AS C1
JOIN Pezzi AS C2
ON C1.codice = C2.autore

JOIN Programmazione AS C3
ON C2.codice = C3.pezzo

GROUP BY C1.codice, C1.cognome, C1.nome

COUNT(DISTINCT C3.pezzo) counts a piece once even when it is played in several concerts (Overture A is in both). The inner joins drop the composers with nothing played, as required.

Query 4 · Composers with the most pieces played¶

Find the composer (or composers) with the largest number of different pieces played, as computed in Query 3. Show code, name, surname and number of pieces; order by code.

CREATE VIEW massimo_numero_pezzi AS
SELECT C1.codice, C1.cognome, C1.nome, COUNT(DISTINCT C3.pezzo) AS numeroPezzi
FROM Compositori AS C1
JOIN Pezzi AS C2
ON C1.codice = C2.autore

JOIN Programmazione AS C3
ON C2.codice = C3.pezzo

GROUP BY C1.codice, C1.cognome, C1.nome
ORDER BY C1.codice;

SELECT codice, cognome, nome, numeroPezzi
FROM massimo_numero_pezzi
WHERE numeroPezzi = (SELECT MAX(numeroPezzi) FROM massimo_numero_pezzi)

Takeaways¶

• "Not in any…" is an outer join filtered on IS NULL (or a NOT IN / NOT EXISTS).
• COUNT(DISTINCT …) when the joins can repeat the thing being counted.
• Reusing a query as a view keeps the "maximum" query short and readable.