Countries, cities and departments¶

SQL exercises · Part I

Problem¶

A database of countries, their cities (identified by a code within the country) and company departments located in a city. Write two queries.

Schema and test data¶

schema.sqltest_data.sql

-- The schema of the exercise, without the table impiegato: the queries do not use
-- it, and as written it declares a foreign key on a column (capo) it does not have.
CREATE TABLE paese (
  id int PRIMARY KEY,
  nome text);

CREATE TABLE citta (
  cittaId int,
  paeseId int not null references paese(id),
  nome text,
  popolazione int,
  PRIMARY key (cittaId, paeseId)
);

CREATE TABLE dipartimento (
  id int primary key,
  nome text,
  numeroDiDipendenti int,
  citta int not NULL,
  paese int not NULL,
  manager int,
  foreign key (citta, paese) references citta(cittaId, paeseId),
  FOREIGN key (manager, id) REFERENCES impiegato(id, lavoraPresso),
  UNIQUE(manager)
);

-- Test data (not part of the exercise): a country with no large department and
-- two countries with cities of the same name.
INSERT INTO paese VALUES (1, 'Italia'), (2, 'Francia'), (3, 'Spagna');
INSERT INTO citta VALUES (1, 1, 'Roma', 2800000), (2, 1, 'Milano', 1400000),
                         (1, 2, 'Parigi', 2100000), (2, 2, 'Lione', 520000),
                         (1, 3, 'Valencia', 790000);
INSERT INTO dipartimento VALUES
    (10, 'Ricerca',   25, 1, 1, NULL),
    (11, 'Vendite',    8, 2, 1, NULL),
    (12, 'Finanza',   40, 2, 1, NULL),
    (13, 'Logistica', 15, 1, 2, NULL),
    (14, 'Marketing',  5, 1, 3, NULL);

The exercise also defines a table of employees, impiegato, left out here: neither query uses it, and as written it declares a foreign key on a column, capo, that the table does not have, so a database would reject it. The test data are mine.

Queries¶

Query 1 · Each city with its country¶

For each city, its name and the name of its country, ordered by city name.

SELECT C1.nome AS nome_citta, C2.nome AS nome_paese

FROM citta AS C1
JOIN paese AS C2
ON C1.paeseid = C2.id

ORDER BY C1.nome

nome_citta	nome_paese
Lione	Francia
Milano	Italia
Parigi	Francia
Roma	Italia
Valencia	Spagna

Query 2 · Large departments per country¶

For each country, the number of its departments with more than 10 employees, ordered by country name.

SELECT C2.nome AS nome_paese, COUNT(C1.id) AS numero_di_dipartimenti

FROM dipartimento AS C1
JOIN paese AS C2 ON C1.paese = C2.id
WHERE C1.numeroDiDipendenti > 10

GROUP BY C2.nome

ORDER BY C2.nome;

nome_paese	numero_di_dipartimenti
Francia	1
Italia	2

One reading of the text

"For each country" could also mean listing the countries with no such department, with 0: Spagna, in the test data, does not appear. That would need the country table as the starting point and a LEFT JOIN with the condition moved into the join. The query is a correct answer to the other, equally plausible reading.

Takeaways¶

With a composite key (cittaId, paeseId), a city is identified only by both columns: the foreign key from dipartimento uses both.
Whether a "for each" includes the zeros is a question to settle before writing the query.