Exemples sql

1. Projections

Donner toutes les lignes de la table.


SELECT * FROM countries ;

Donner le nom de chaque pays.


SELECT name, area * 100 FROM countries;

Donner la superficie en hectares de chaque pays, la colonne se nommera areaH.


SELECT name, area * 100 AS areaH 
FROM countries;

Donner l’ensemble des region_id, sans doublons.


SELECT DISTINCT region_id FROM countries ;

2. Sélection

Donner le nom de chaque pays de la region_id 2.


SELECT name FROM countries WHERE region_id = 2;

Donner le nom de chaque pays de la region_id 3 ayant une superficie inférieure à mille kilomètre carrés.


SELECT name FROM countries 
    WHERE region_id = 3 AND area <= 1e3;

Donner le nom de chaque pays d’une superficie supérieure à un milliards d’hectares.

Vous afficherez le nom du pays et la superficie en hectare que l’on nommera areaH.


SELECT name, (area * 100) AS areaH 
    FROM countries 
    WHERE (area * 100) > 1e9;
-- SELECT name, (area * 100) AS areaH 
--    FROM countries WHERE areaH > 1000; 
-- Cette requête ne fonctionne pas. ;

3. Agrégats

Donner la plus petite superficie parmis les pays.


SELECT COUNT(DISTINCT region_id) FROM countries ;

Donner le nombre de pays ayant une fête nationale.


SELECT COUNT(national_day) FROM countries;

Donner le nombre de pays dans chaque région. On affichera le numéro de région et le nombre de pays dans une colonne nb_pays.


SELECT region_id, COUNT(*) AS nb_pays FROM countries GROUP BY region_id ;

Donner la plus grande superficie en hectare de chaque région. On affichera le numéro de région et la superficie dans une colonne max_areaH.


SELECT name, MAX(area * 100) AS max_areaH FROM countries GROUP BY region_id ;

Donner le nombre de pays de plus de dix millions d’hectares dans chaque région. On affichera le numéro de région et le nombre de pays dans une colonne nb_areaM.


SELECT region_id, COUNT(country_id) AS nb_areaM 
FROM countries 
WHERE area * 100 > 1e6
GROUP BY region_id ;

Donner les régions ayant plus de 20 pays.


SELECT region_id FROM countries GROUP BY region_id HAVING COUNT(*) >= 20;

Donner le nombre de pays dans chaque tranche de dix mille hectare. On affichera la tranche sous une colonne tranche et le nombre de pays dans une colonne nb_slice.


SELECT FLOOR(area / 1e4) * 1e4 AS tranche, COUNT(*) 
    FROM countries 
    GROUP BY FLOOR(area / 1e4) ;

Donner le nombre de pays dans chaque tranche de dix mille hectare pour chaque région. On affichera la tranche sous une colonne tranche, le numéro de région et le nombre de pays dans une colonne nb_slice.


SELECT region_id, FLOOR(area / 1e4) * 1e4 AS tranche, COUNT(*) 
    FROM countries 
    GROUP BY region_id, FLOOR(area / 1e4) ;

Donner pour chaque région le nombre de tranche de dix mille hectares différentes représentée par les pays qui la constitue. On affichera la region et le nombre de tranche dans une colonne nb_tranches.


SELECT region_id, COUNT(DISTINCT FLOOR(area / 1e4)) AS nb_tranches
FROM countries 
GROUP BY region_id ;

Donner les numéros de région ayant une superficie supérieure à dix million de kilomètres carrés.


SELECT region_id FROM countries 
GROUP BY region_id 
HAVING SUM(area) >= 1e7 ;

Donner les numéros de région ayant une au moins cinq pays de plus de cent mille kilomètres carrés.


SELECT region_id FROM countries
WHERE area > 1e5
GROUP BY region_id
HAVING COUNT(*) > 5 ;

4. Tri

Donner les nom des pays dans l’ordre alphabétique.


SELECT name FROM countries ORDER BY name DESC ;

Donner par ordre de région, les noms et superficie des pays de ces régions par ordre croissant de superficie.


SELECT region_id, name, area 
FROM countries
ORDER BY region_id, area ;

Donner le pays avec la plus grande superficie.


SELECT name FROM countries
ORDER BY area DESC LIMIT 1 ;

Donner le pays ayant la troisième plus petite superficie.


SELECT name FROM countries 
ORDER BY area LIMIT 1 OFFSET 2 ;

Donner, parmi les pays de plus de mille kilomètre carré, les dix pays ayant la plus petites superficie.


SELECT name FROM countries 
WHERE area > 1e3 
ORDER BY area LIMIT 10 ;

Donner les numéros de régions par ordre décroissant de superficie.


SELECT region_id FROM countries 
GROUP BY region_id 
ORDER BY SUM(area) DESC ;

5. Jointures

Donner pour chaque pays son nom et le nom de la région à laquelle il appartient. Les colonnes seront nommées nom_pays et nom_region.


SELECT P.name AS nom_pays, R.name AS nom_region 
FROM countries AS P JOIN regions AS R 
    ON P.region_id = R.region_id ;

Donner pour chaque région son nom et sa superficie. La colonne superficie sera nommée superficie et les résultats seront triés par ordre alphabétique.


SELECT R.name, SUM(P.area) AS superficie
FROM countries AS P JOIN regions AS R 
    ON P.region_id = R.region_id 
GROUP BY R.region_id 
ORDER BY R.name;

Donner le nom de chaque région qui possède au moins un pays de plus d’un million de kilomètres carrés.


SELECT DISTINCT R.name 
FROM countries AS P JOIN regions AS R 
    ON P.region_id = R.region_id 
WHERE P.area >= 1e6 ;

Donner pour chaque pays, son nom et le continent auquel il appartient. Les colonnes seront nommées nom_pays et nom_continent.


SELECT P.name AS nom_pays, C.name AS nom_continent
FROM countries AS P JOIN regions AS R 
    ON P.region_id = R.region_id 
    JOIN continents AS C
    ON C.continent_id = R.region_id ;

Donner le continent ayant le plus de langue parlées différentes.


SELECT C.name
FROM country_languages AS O
    JOIN countries AS P ON P.country_id = O.country_id
    JOIN regions AS R ON P.region_id = R.region_id 
    JOIN continents AS C ON C.continent_id = R.region_id 
GROUP BY C.continent_id
ORDER BY COUNT(*) DESC LIMIT 1;

Donner le nom de chaque langue qui est parlée dans au moins deux continents. On donnera aussi le nombre de continent en question, dans une colonne nommée nombre_continents.


SELECT L.language, 
    COUNT(DISTINCT C.continent_id) AS nombre_continents
FROM languages AS L 
    JOIN country_languages AS O ON L.language_id = O.language_id
    JOIN countries AS P ON P.country_id = O.country_id
    JOIN regions AS R ON P.region_id = R.region_id 
    JOIN continents AS C ON C.continent_id = R.region_id 
GROUP BY L.language_id 
HAVING COUNT(DISTINCT C.continent_id) > 1;

Donner le nom des langues qui sont parlée dans au moins cinq pays de plus de mille kilomètres carrés, triées par ordre décroissant de nombre de pays la parlant.


SELECT L.language
FROM languages AS L 
    JOIN country_languages AS O ON L.language_id = O.language_id
    JOIN countries AS P ON P.country_id = O.country_id
WHERE P.area >= 1e3 
GROUP BY L.language_id 
HAVING COUNT(DISTINCT P.country_id) > 4;

6. Requêtes Imbriquées

Donner le nom de chaque pays plus grand que la France.


SELECT name FROM countries 
    WHERE area >= (SELECT area 
        FROM countries 
        WHERE name = "France") ;

Donner pour chaque region, son numéro et le nom du pays le plus grand de cette région.


SELECT A.region_id, P.name FROM countries AS P
    JOIN (SELECT MAX(area) AS max_area, region_id 
        FROM countries GROUP BY region_id) AS A 
    ON P.region_id = A.region_id 
    WHERE P.area = max_area ;

Donner le pourcentage de pays parlant une langue parmi celles parlées en France.


SELECT COUNT(P.country_id) / 
    (SELECT COUNT(*) FROM countries) 
    AS pourcentage 
FROM countries AS P 
JOIN country_languages AS O ON P.country_id = O.country_id
WHERE O.language_id IN 
    (SELECT language_id FROM country_languages AS O
    JOIN countries AS P ON O.country_id = P.country_id
    WHERE P.name = "France" ) ;

Donner le nom de toutes les régions ayant une superficie plus petite que celle du Brézil.

SELECT R.name, SUM(P.area) FROM regions as R 
    JOIN countries AS P ON P.region_id = R.region_id
    GROUP BY R.region_id
    HAVING SUM(P.area) >= 
    (SELECT area FROM countries WHERE name = "Brazil") ;

Donner pour chaque langue son nom et le nom du plus grand pays dans laquelle elle est parlée.

SELECT L.language, P.name 
    FROM languages AS L 
    JOIN country_languages AS O ON O.language_id = L.language_id
    JOIN countries AS P ON P.country_id = O.country_id
    JOIN ( SELECT O.language_id, MAX(P.area) AS max_area
        FROM country_languages AS O 
        JOIN countries AS P ON P.country_id = O.country_id
        GROUP BY O.language_id ) AS M 
    ON L.language_id = M.language_id 
    WHERE M.max_area = P.area ;

7. Opérations Ensemblistes

Donner l’ensemble des noms de region qui sont aussi nom d’un continent.


(SELECT name FROM regions) 
    INTERSECT 
(SELECT name FROM continents) ;

Donner l’ensemble des noms de region qui ne sont pas aussi nom d’un continent.


(SELECT name FROM regions) 
    EXCEPT 
(SELECT name FROM continents) ;

Donner l’ensemble des noms de pays, regions et continents.


(SELECT name FROM countries)
UNION (SELECT name FROM regions)
UNION (SELECT name FROM continents) ;

Donner l’ensemble des noms de pays, region et continents avec leur superficie, triés par ordre décroissant de superficie.


(SELECT name, area FROM countries)
UNION (SELECT R.name, SUM(P.area) AS area FROM regions AS R 
    JOIN countries AS P ON R.region_id = P.region_id 
    GROUP BY P.region_id)
UNION (SELECT C.name, SUM(P.area) AS area FROM regions AS R 
    JOIN countries AS P ON R.region_id = P.region_id 
    JOIN continents AS C ON C.continent_id = R.region_id
    GROUP BY C.continent_id);