Pour chaque question, proposer une requête écrite en algèbre relationnelle ou en SQL.

Question 1

Dans le schéma world, lister les pays où les dix plus grandes villes ont chacune au moins 10 millions d’habitants.

Solution 
SELECT 
  ci.countrycode
FROM 
  world.city ci 
WHERE 
  ci.population_city > 10000000
GROUP BY 
  ci.countrycode 
HAVING 
  COUNT(ci.id) >= 10 ;
Question 2

Dans le schéma world, pour chaque pays, donner les trois langues les plus parlées.

Solution 
WITH R AS (
  SELECT 
    cl.countrycode, cl.language, 
    RANK() OVER w AS rnk 
  FROM 
    world.countrylanguage cl 
  WINDOW 
    w AS (PARTITION BY cl.countrycode ORDER BY cl.percentage DESC)
)

SELECT 
  R.countrycode, R.language
FROM 
  R
WHERE 
  R.rnk <= 3 ;
Question 3

Dans le schéma world, lister les langues parlées sur tous les continents.

Solution 
SELECT
  language
FROM
  country co
NATURAL JOIN
  countrylanguage cl
GROUP BY
  cl.language
HAVING COUNT(DISTINCT co.continent) = (
  SELECT COUNT(DISTINCT c.continent) FROM country c
) ;
Question 4

Dans le schéma world, lister par région le nombre de langues officielles distinctes.

Solution 
SELECT 
  co.region, 
  COUNT(DISTINCT cl.language) AS n
FROM 
  world.countrylanguage cl 
NATURAL JOIN 
  world.country co 
WHERE 
  cl.isofficial
GROUP BY 
  co.region
Question 5

Dans le schéma world, lister pour chaque forme de gouvernement, la population des villes des pays qui subissent cette forme de gouvernement.

Solution 
SELECT 
  co.governmentform, 
  SUM(ci.population) AS pop
FROM 
  world.country co 
JOIN 
  world.city ci  USING (countrycode)
GROUP BY 
  co.governmentform ;
Question 6

Dans le schéma world, lister les pays où la capitale est la ville la plus peuplée.

Solution 
WITH R AS (
  SELECT 
    co.countrycode, co.name_country, ci. name, ci.id, ci.population,
1    (co.capital=ci.id) AS capitale,
2    RANK() OVER (PARTITION BY co.countrycode ORDER BY ci.population DESC) AS rnk
  FROM 
      world.country co
    JOIN
      world.city ci  USING (countrycode) 
)

SELECT 
  R.name_country, R. name, R.population
FROM 
  R 
WHERE 
3  capitale AND rnk = 1
;
1
On note les villes qui sont capitales
2
On range les villes d’un pays par population décroissante
3
On ne garde que les capitales qui sont aussi les villes les plus peuplées du royaume
Question 7

Dans le schéma world, lister les pays où \(90\%\) de la population parle une langue officielle (il peut y avoir plusieurs langues officielles, dont la somme des locuteurs et locutrices dépasse \(90\%\) de la population).

Solution 
SELECT 
  cl.countrycode
FROM 
  world.countrylanguage cl 
WHERE 
  cl.isofficial
GROUP BY cl.countrycode
HAVING SUM(cl.percentage) >= 90 ;

Toutes les questions portent sur le schéma world.

Schéma world