SQLZOO典型题记录

SQLZOO是一个MySQL的在线练习平台，本文记录了其中的一些典型题目。

SQLZOO：https://sqlzoo.net/

SQLZOO习题全解，答案参考-英文版

ROUND to the nearest 1000

how to type umlaut

如何打出Ü：wiki

write apostrophe in string

在字符串内用两个单引号代替一个单引号。

IN expression can be used as a value

这里输入标准答案会报错。

其他答案：

1
2
3
4
5
6
7
8
9
10

SELECT 
  winner, subject
FROM 
  nobel
WHERE 
  yr = 1984
ORDER BY 
  CASE WHEN subject IN ('Physics','Chemistry') THEN 1 ELSE 0 END, 
  subject, 
  winner

display percentage

<、> over a list

You need the condition population>0 in the sub-query as some countries have null for population!

First of each

其他答案：

1
2
3
4

SELECT continent, MIN(name) AS name
FROM world 
GROUP BY continent
ORDER by continent

错误👇但没明白为什么

1
2
3
4

select continent, name
from world x
where name = (select name from world y
where x.continent = y.continent limit 1)

select continent that all the countries in it …

其他答案：

1
2
3
4
5
6
7
8
9
10

SELECT name, continent, population FROM world WHERE continent IN (SELECT continent FROM world  x WHERE 25000000 >= (SELECT MAX(population) FROM world y WHERE x.continent = y.continent));
or
SELECT y.name, y.continent, y.population
FROM world AS y
JOIN
(SELECT continent,max(population)
FROM world
GROUP BY continent
HAVING max(population) <= 25000000) AS x
ON y.continent = x.continent

bigger than any of

注意要加上 and x.name <> y.name，排除自己！

compare to null

最后一个选项错误因为null不可以和其他值比较，只能够使用is null/is not null。

where and having

但这个做法会将符合要求的国家数为0的continent排除不显示，若要将这一类显示为0应该：

1
2
3
4
5
6
7
8

select x.continent, COALESCE(y.count,0)
from 
(select continent from world group by continent) as x left join (
    select continent, COUNT(name) as count
    from world
    where population > 10000000
    group by continent) as y
on x.continent = y.continent

用自己和自己join后的表，加上COALESCE()来显示为null的值。

complex join

思路：

1. 需要展示的信息是title、leading actor name -> 需要连接movie、actor、casting三张表
2. 要求是’Julie Andrews’这个actor参演过的电影 -> 子查询

最后的数据表有点难理解，解释如下：

先查看一下表的数据：

We got the name of the stop, the bus number, and the bus company. We learned from an earlier problem that we would usually refer to a bus by their num and company like ‘124 SMT’. So, by looking at our table we could assume that at the stop in Aberlady we can get the 124 SMT bus.

pos这个字段有点儿难理解，它指的是这辆车走的路线中这个站的顺序（第几个到达的）。

If we order by the num then by position, we’ll see that the ‘1 LRT’ bus, for example, leaves from Leith then goes on a round trip until it gets back to Leith after stopping at 9 other stops. If we go down the table, we see that not all buses go on a round trip as they won’t always end up at the same stop where they started.

最后一题的解析：SQLZOO-self join最后一题解析-复杂自联结应用（附加where判断条件的数组应用拓展）

另一篇解析（写得太好了）：Learning Self JOIN Queries With SQLZOO

complex self join

列出从Craiglockhart到lochend的换乘选项，需要的5个内容分别是第一辆车号，哪家公司运营，换乘站，第二辆车号，哪家公司运营。

In other words, we need to find the buses that stop at Craiglockhart and have stops in common with the buses that stop at Lochend. Because that’s where they can get off and change buses.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SELECT DISTINCT v1.num,v1.company, name,v2.num,v2.company
// 找出经过Craiglockhart的车辆和其途径的其它站点集合v1(num,company,stop)
FROM 
(SELECT r1.num ,r1.company ,r2.stop
   FROM route AS r1 JOIN route AS r2 ON (r1.num=r2.num 
   AND r1.company=r2.company)JOIN stops ON r1.stop=stops.id 
  WHERE stops.name='Craiglockhart'  AND r2.stop!= r1.stop) AS v1 

JOIN
// 找出经过Lochend的车辆和其途径的其它站点集合v2(num,company,stop)
(SELECT r1.num,r1.company,r2.stop
   FROM route AS r1 JOIN route AS r2 ON (r1.num=r2.num AND
r1.company=r2.company)JOIN stops ON r1.stop=stops.id 
  WHERE stops.name='Lochend'  AND r2.stop!= r1.stop) AS v2 

// v1和v2需要经过同一个站点作为周转站点
ON v1.stop=v2.stop

JOIN stops ON v1.stop=stops.id

✌