Q1

要求从Category表中按照字母顺序列出所有CategoryName内容

sqlite> SELECT CategoryName FROM Category ORDER BY CategoryName;
Beverages
Condiments
Confections
Dairy Products
Grains/Cereals
Meat/Poultry
Produce
Seafood

Q2

要求从Order表中按照字母顺序列出所有包含-的内容，且不能有重复内容

1. 要求必须包含-，那么我们需要like '%-%'来筛选
2. 不能有重复内容我们可以用DISTINCT关键字
3. 字母顺序使用ORDER BY关键字

坑点是查询的时候记得FROM "Order"，因为Order是关键字，不加引号会出现语法错误

sqlite> SELECT DISTINCT ShipName FROM "Order" WHERE ShipName like '%-%' ORDER BY ShipName;
Bottom-Dollar Markets
Chop-suey Chinese
GROSELLA-Restaurante
HILARION-Abastos
Hungry Owl All-Night Grocers
LILA-Supermercado
LINO-Delicateses
QUICK-Stop
Save-a-lot Markets

Q3

要求从Order表中查询Id和ShipCountry按照id递增输出从15445开始的20条信息，并且如果ShipCountry是北美的三个国家('USA', 'Mexico', 'Canada')那么显示'NorthAmerica'，否则是'OtherPlace'

1. 查询Id和ShipCountry直接SELECT Id, ShipCountry
2. 对ShipCountry进行判断并添加为一列的方法是使用case when方法 SELECT WHEN ShipCountry in when ShipCountry in ("USA", "Canada", "Mexico") then 'NorthAmerica' else 'OtherPlace' end
3. 从ID为15445开始输出是WHERE Id >= 15445
4. 按照ID递增输出是ORDER BY ID ASC
5. 只输出前20条信息是LIMIT 20

sqlite> SELECT Id, ShipCountry, 
(	case
		when ShipCountry in ("USA", "Canada", "Mexico")
		then 'NorthAmerica'
		else 'OtherPlace' 
	end
)
FROM "Order" 
WHERE Id >= 15445 
ORDER BY Id ASC 
LIMIT 20;
15445|France|OtherPlace
15446|Italy|OtherPlace
15447|Portugal|OtherPlace
15448|Argentina|OtherPlace
15449|Portugal|OtherPlace
15450|Venezuela|OtherPlace
15451|Brazil|OtherPlace
15452|France|OtherPlace
15453|France|OtherPlace
15454|Canada|NorthAmerica
15455|USA|NorthAmerica
15456|France|OtherPlace
15457|Mexico|NorthAmerica
15458|USA|NorthAmerica
15459|Germany|OtherPlace
15460|Argentina|OtherPlace
15461|Austria|OtherPlace
15462|Austria|OtherPlace
15463|Finland|OtherPlace
15464|Brazil|OtherPlace

Q4

要求查询每家公司的名字和不准时的订单的比率，并递减顺序输出

1. 要求按照每家公司查询，所以我们需要GROUP BY分组
2. 要求查询不准时的订单比率，只需要用每家公司的不准时订单数量除以每家公司的总订单数量即可，这样的话就是从两个表中统计。即SELECT * FROM (SELECT ...) INNER JOIN (SELECT ...) ON ...
3. 要求对计算的百分率四舍五入保留两位小数，需要ROUND(x, n)函数即对x保留n位小数

sqlite> SELECT CompanyName, ROUND(OvertimeCnt.cnt * 100.0 / TotalCnt.cnt, 2) AS cnt_rate 
FROM 
	(SELECT ShipVia, COUNT(*) AS cnt FROM "Order" WHERE ShippedDate > RequiredDate GROUP BY ShipVia) AS OvertimeCnt 
	INNER JOIN (SELECT ShipVia, COUNT(*) AS cnt FROM "Order" GROUP BY ShipVia) AS TotalCnt ON OvertimeCnt.ShipVia = TotalCnt.ShipVia 
	INNER JOIN Shipper ON Shipper.Id = OvertimeCnt.ShipVia 
ORDER BY cnt_rate DESC;
Federal Shipping|23.61
Speedy Express|23.46
United Package|23.44