【SQL練習】SQL Bolt lesson5
1.解答
List all the Canadian cities and their populations
カナダのすべての都市とその人口を一覧化してください
SELECT * FROM North_american_cities WHERE country = 'Canada';
2.解答
Order all the cities in the United States by their latitude from north to south
北から南への緯度で米国のすべての都市を並び替えてください
SELECT * FROM North_american_cities WHERE country = 'United States' ORDER BY latitude DESC;
「ASC」は昇順、「DESC」は降順
3.解答
List all the cities west of Chicago, ordered from west to east
シカゴの西にあるすべての都市を一覧化して、西から東に並び替えてください
SELECT * FROM North_american_cities WHERE longitude < -87.629798 ORDER BY longitude;
4.解答
List the two largest cities in Mexico (by population)
メキシコの2つの大都市(人口別に)を一覧化してください
SELECT * FROM North_american_cities WHERE country = 'Mexico' ORDER BY population DESC LIMIT 2;
5.解答
List the third and fourth largest cities (by population) in the United States and their population
米国の3番目と4番目に大きい都市(人口別に)とその人口を一覧化してください
SELECT * FROM North_american_cities WHERE country = 'United States' ORDER BY population DESC LIMIT 2 OFFSET 2;
※サブクエリを使用すれば、上記の解答でなくても検索結果を取得することができます