Last active
October 24, 2017 15:03
-
-
Save smwhr/ed852de72726b187bd3a27888fdeb9c6 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
https://dev.mysql.com/doc/index-other.html | |
https://gist.github.com/smwhr | |
Quel est le pays le plus peuplé ? | |
> SELECT Population, Name FROM `country` WHERE 1 ORDER BY Population DESC | |
Quel est le pays le plus grand ? | |
> SELECT SurfaceArea, Name FROM `country` WHERE 1 ORDER BY SurfaceArea DESC | |
Quel est le total de la population mondiale ? | |
> SELECT SUM(Population) FROM `country` | |
Quelle est la superficie totale des terres habitées ? | |
> SELECT SUM(SurfaceArea) FROM `country` | |
Quelle est la densité moyenne de population mondiale ? | |
> SELECT SUM(Population)/SUM(SurfaceArea) FROM `country` | |
Quel est le pays le plus densément peuplé ? | |
> SELECT (Population/SurfaceArea) as density FROM `country` ORDER BY density DESC | |
Combien de pays ont une densité supérieure à la pop mondiale ? | |
SELECT COUNT(1) FROM ( | |
SELECT Code, Name, (Population/SurfaceArea) as density, | |
wm.avg_density | |
FROM country | |
JOIN ( | |
SELECT SUM(Population)/SUM(SurfaceArea) as avg_density FROM `country` | |
) as wm | |
HAVING density > avg_density | |
ORDER BY density DESC | |
) as c | |
Quel est le pays le plus ayant le plus haut GNP/hab ? | |
> Luxembourg | |
Quel est le pays le plus peuplé de chacun des continents ? (en 1 seule requête) | |
> SELECT Continent, Code, Name, Population | |
FROM country | |
JOIN | |
(SELECT Continent as maxcont, MAX(Population) as maxpop | |
FROM country | |
WHERE Code != "RUS" | |
GROUP BY Continent) as m | |
ON Continent = maxcont AND Population = maxpop | |
ORDER BY Continent ASC | |
Quelles sont les 3 formes de gouvernement les plus répandues ? | |
> SELECT COUNT() etc. | |
Quelles sont les 3 formes de gouvernement gouvernant le plus de personnes ? | |
> SELECT SUM(Population) etc. | |
Quel est le pays avec la capitale la plus peuplée ? | |
> SELECT country.Name, city.Name, city.Population | |
FROM country | |
JOIN city ON city.ID = country.Capital | |
ORDER BY city.Population DESC | |
> SELECT country.Name, city.Name, city.Population | |
FROM country, city | |
WHERE city.ID = country.Capital | |
ORDER BY city.Population DESC | |
Quelles sont les 3 langues les plus parlées en France ? | |
> SELECT CountryCode, Language, Percentage | |
FROM `countrylanguage` | |
WHERE CountryCode = 'FRA' | |
ORDER BY Percentage | |
Quelles est la population de personnes qui parlent portugais en France ? | |
SELECT Language, Percentage * country.Population / 100 as speaker | |
FROM `countrylanguage` | |
JOIN country ON country.Code = countrylanguage.CountryCode | |
WHERE country.Code = "FRA" | |
ORDER BY speaker DESC | |
Quelle est la proportion de francophones qui sont français ? | |
SELECT french_french, world_french, french_french / world_french * 100 | |
FROM | |
(SELECT SUM(Percentage*Population/100) as world_french | |
FROM country | |
JOIN countrylanguage ON country.Code = countrylanguage.CountryCode | |
WHERE Language = "French") as wf | |
JOIN | |
(SELECT Percentage * country.Population / 100 as french_french FROM `countrylanguage` JOIN country ON country.Code = countrylanguage.CountryCode WHERE country.Code = "FRA" AND Language = "French") as ff | |
Quelles sont les 10 langues les plus parlées dans le monde ? | |
/!\ Quelle est la langue la plus parlée de chaque continent ? | |
Quelles sont les 5 plus grandes communautés francophones hors de France ? | |
Si l'on considère que les proportions nationales sont respectées dans les capitales, quelle est la langue la plus parlée dans ces villes ? | |
Existe-t-il des pays comportant plus d'individus parlant une langue non-officielle que dans le pays avec la plus grande communauté parlant officiellement cette langue ? |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment