Correction exercices les bases de données MySQL Série 01

SELECT nomFleuve
FROM fleuve;

SELECT nomPays, capitale
FROM pays
WHERE capitale LIKE 'B%';

SELECT nomPays, superficieKm²
FROM pays
WHERE superficieKm² >= 200000;

SELECT COUNT(*)
FROM pays;

SELECT nomPays, superficieKm²
FROM pays
WHERE superficieKm² = (SELECT MAX(superficieKm²) FROM pays);

SELECT nomPays, capitale
FROM pays 
   INNER JOIN continent ON pays.numContinent = continent.NumContinent 
WHERE nomContinent = 'Afrique'
ORDER BY nomPays;

SELECT nomPays, capitale
FROM pays  
    INNER JOIN continent ON pays.numContinent = continent.NumContinent 
WHERE nomContinent = 'Afrique'
ORDER BY superficieKm² DESC;

SELECT SUM(superficieKm²)
FROM pays 
INNER JOIN continent ON pays.numContinent = continent.numContinent 
WHERE nomContinent ='Afrique';

SELECT nomContinent, SUM(superficieKm²)
FROM pays 
INNER JOIN continent ON pays.numContinent = continent.numContinent 
GROUP BY nomContinent;

SELECT AVG(superficieKm²)
FROM pays;

SELECT nomPays, COUNT(numFleuve)
FROM traversee 
INNER JOIN pays ON traversee.numPays = pays.numPays 
GROUP BY nomPays;

SELECT nomPays
FROM pays 
INNER JOIN continent ON pays.numContinent = continent.numContinent 
WHERE superficieKm² <=  100000 
AND nomContinent = 'Amérique' 
ORDER BY superficieKm² ASC;

SELECT nomPays, capitale
FROM pays, traversee
WHERE pays.numPays = traversee.numPays
GROUP BY nomPays, capitale
HAVING COUNT(*) > 2;

SELECT p1.nomPays, p2.nompays
FROM pays p1, pays p2, frontalier
WHERE p1.numPays = numPays1 
AND numPays2 = p2.numPays 
ORDER BY p1.nomPays, p2.nomPays