SQLBolt：https://sqlbolt.com/lesson/introduction

• SQL Review: Simple SELECT Queries

前言

今天我們來到了 review 環節，是針對我們前面學的學習進行的小檢視。

這讓筆者不禁想起公司的各種小檢核呢。

我們將複習到：SELECT WHERE ORDER BY LIMIT

SQL Review: Simple SELECT Queries

題目 1

-- 1. List all the Canadian cities and their populations 
SELECT city, population FROM north_american_cities
WHERE country = "Canada";

題目 2

-- 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;

題目 3

-- 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

-- 4. List the two largest cities in Mexico (by population)
-- 網站解法
SELECT city, population FROM north_american_cities
WHERE country LIKE "Mexico"
-- 不太清楚為什麼這邊要使用 lIKE 呢
ORDER BY population DESC
LIMIT 2;

-- 筆者解法
SELECT city, population FROM North_american_cities
WHERE country = "Mexico"
ORDER BY population DESC
LIMIT 2;

題目 5

-- 5. List the third and fourth largest cities (by population) in the United States and their population
SELECT city, population FROM North_american_cities
WHERE country = "United States"
ORDER BY population DESC
LIMIT 2 OFFSET 2;

經過這個針對基礎語法的小檢核，我們要前往下一個階段啦！

參考資料：

• SQLBlot - SQL Review