A museum: artists, paintings and rooms¶

SQL exercises · Part I

Problem¶

A museum database: artists, their paintings, and the rooms where paintings are exhibited, each room on a floor. A painting may have no room (for example, when it is in storage). Write four queries.

Schema and test data¶

schema.sqltest_data.sql

CREATE TABLE Artisti (
codice text NOT NULL PRIMARY KEY,
cognome text,
nome text);

CREATE TABLE Quadri (
codice integer NOT NULL PRIMARY KEY,
artista text NOT NULL REFERENCES Artisti,
descrizione text NOT NULL,
stanza text REFERENCES Stanze,
anno integer
);

CREATE TABLE Stanze (
nome text NOT NULL PRIMARY KEY,
piano integer NOT NULL
);

-- Test data (not part of the exercise): an empty room on floor 1, a painting in
-- storage (no room), and an artist with two paintings in the same room.
INSERT INTO Artisti VALUES ('CAR', 'Caravaggio', 'Michelangelo'), ('RAF', 'Sanzio', 'Raffaello'),
                           ('TIZ', 'Vecellio', 'Tiziano');
INSERT INTO Stanze VALUES ('Sala A', 1), ('Sala B', 1), ('Sala C', 2), ('Sala D', 1);   -- Sala D: empty
INSERT INTO Quadri VALUES
    (1, 'CAR', 'Canestra di frutta', 'Sala A', 1599),
    (2, 'CAR', 'Bacchino malato',    'Sala A', 1594),
    (3, 'RAF', 'Madonna del prato',  'Sala A', 1506),
    (4, 'RAF', 'La velata',          'Sala B', 1516),
    (5, 'TIZ', 'Venere di Urbino',   'Sala C', 1538),
    (6, 'TIZ', 'Flora',              NULL,     1515);    -- in storage

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

Queries¶

Query 1 · Paintings on the first floor¶

All the paintings on floor 1, with code and description of the painting and surname and name of the artist.

SELECT C1.codice, C1.descrizione, C3.cognome, C3.nome

FROM Quadri AS C1
JOIN Stanze AS C2
ON C1.stanza = C2.nome
JOIN Artisti AS C3
ON C1.artista = C3.codice

WHERE C2.piano = 1

codice	descrizione	cognome	nome
1	Canestra di frutta	Caravaggio	Michelangelo
2	Bacchino malato	Caravaggio	Michelangelo
3	Madonna del prato	Sanzio	Raffaello
4	La velata	Sanzio	Raffaello

Query 2 · Rooms on the first floor and how many paintings they show¶

For each room on floor 1: name, floor and number of paintings exhibited (0 if none).

SELECT C1.nome, C1.piano, COUNT(C2.stanza) AS numero_totoale_quadri

FROM Stanze AS C1
LEFT JOIN Quadri AS C2
ON C1.nome = C2.stanza

WHERE C1.piano = 1

GROUP BY C1.nome, C1.piano

nome	piano	numero_totoale_quadri
Sala A	1	3
Sala B	1	1
Sala D	1	0

The empty Sala D appears with 0 thanks to the LEFT JOIN and to counting C2.stanza rather than rows.

Query 3 · Works of each artist in each first-floor room¶

For each room on floor 1 that shows at least one painting: name and floor of the room, name and surname of each artist with works there, and how many works of that artist it shows. Solved in two ways.

With an outer joinWith inner joins

SELECT C1.nome AS nome_stanza , C1.piano AS piano_stanza, C3.nome AS nome_artista, C3.cognome AS cognome_artista, COUNT(C2.stanza) AS numero_opere_esposte

FROM Stanze AS C1
LEFT JOIN Quadri AS C2
ON C1.nome = C2.stanza
JOIN Artisti AS C3
ON C2.artista = C3.codice

WHERE C1.piano = 1 AND C2.stanza IS NOT NULL

GROUP BY C1.nome, C1.piano, C3.nome, C3.cognome

-- it could also be solved as follows, more directly (without left join and where)

SELECT C1.nome AS nome_stanza , C1.piano AS piano_stanza, C3.nome AS nome_artista, C3.cognome AS cognome_artista, COUNT(C2.stanza) AS numero_opere_esposte

FROM Stanze AS C1
JOIN Quadri AS C2
ON C1.nome = C2.stanza
JOIN Artisti AS C3
ON C2.artista = C3.codice

WHERE C1.piano = 1

GROUP BY C1.nome, C1.piano, C3.nome, C3.cognome

nome_stanza	piano_stanza	nome_artista	cognome_artista	numero_opere_esposte
Sala A	1	Michelangelo	Caravaggio	2
Sala A	1	Raffaello	Sanzio	1
Sala B	1	Raffaello	Sanzio	1

Both versions give the same result: since rooms without paintings must be excluded anyway, the inner join of the second version does directly what the first obtains with LEFT JOIN plus IS NOT NULL.

Query 4 · Artists with at least two paintings in the same room¶

The (name, surname, room) triples where the room contains at least two paintings of that artist.

SELECT C1.nome AS nome_artista, C1.cognome AS cognome_artista, C3.nome AS nome_stanza

FROM Artisti AS C1
JOIN Quadri AS C2
ON C1.codice = C2.artista
JOIN Stanze AS C3
ON C2.stanza = C3.nome

GROUP BY C1.nome, C1.cognome, C3.nome

HAVING COUNT (C2.codice) >= 2 -- having filters AFTER the grouping (where would not work here, because it filters before)

nome_artista	cognome_artista	nome_stanza
Michelangelo	Caravaggio	Sala A

Takeaways¶

WHERE filters rows before grouping, HAVING filters groups after: a condition on a count can only go in HAVING.
An outer join followed by a filter on the optional side is just an inner join written the long way.
The # comments of the original (MySQL syntax) are written here as --, the standard SQL comment, so that the queries run on any database.