sql练习题目(持续更新)

1. 题目：选出1984年的诺贝尔获奖者和项目，并按照项目和获奖者姓名排序，同时，将化学奖和物理学奖放到最后(Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.) 题目链接：在最下面第14个练习

思路：对于前面非加粗那部分要求，是很容易实现的，但如何在按项目和获奖者排序的情况下，实现加粗部分的要求呢？这个时候可以考虑使用关键词 in，对于subject in (‘Chemistry’, ‘Physics’)，sql可能返回两个值——0或1，即若subject 是’Chemistry’或 'Physics '，返回1；否则返回0.
因此，可以通过order by subject in (‘Chemistry’, ‘Physics’), subject,winner达到排序要求。意思是，由于subject in (‘Chemistry’, ‘Physics’)返回0和1，因此对于返回1的Chemistry and Physics就会被放到最后面.

SELECT winner, subject
  FROM nobel
 WHERE yr=1984
  ORDER BY subject IN ('Physics','Chemistry'), subject,winner

2. 找出每个国家人口都少于25000000的大陆，然后找到位于这些大陆的国家，并展示国家名，大陆和人口数目。(Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.)题目链接：第9题

思路1：对于表中的每个国家，找到其相应的大洲，并判断这个大陆上的每个国家人口是否都小于25000000。

select name, continent, population
from world x
where 25000000>=all(select population from world where continent=x.continent)

思路2：按照题目的提示，先找出表中每个国家人口都少于25000000的大陆，然后，找到这个大陆里所有的国家

select name, continent, population
from world
where continent=(
	-- 找出表中每个国家人口都少于25000000的大洲
	select distinct continent
	from (select continent from world) as c
	where 25000000>=all(select population from world where continent=c.continent)
)

对比：显然思路2的方法会比较快捷，因为思路2只需要比较8个大陆（为什么有8个？）即可很快得出答案，所以应该是思路2比较快吧

3. 找出每场比赛中每个队伍的得分，并将结果按mdate, matchid, team1 and team2排序。(List every match with the goals scored by each team as shown. And sort your result by mdate, matchid, team1 and team2.)题目链接：第13题
   

思路：本题难点在于如何对每个队伍进行计分。这可以通过如下方法解决：如果这是一个team1，那么得分1中将显示1，否则为0。最后这一列求和(sum)以获取team1的得分。
易错点：这里计算team1和team2时，应该对game表使用左连接，因为对于并不是每一场比赛都有得分，也即，并不是每一个game都对应有一条/n条goal得分，所以为了避免丢失game中的某些比赛，这里要采用左连接进行。此外，基于同样的理由，对于得到score后的team1和team2表，连接的时候主键应该选取game.id，而不能选取goal.matchid。

select t1.mdate, t1.team1, t1.score1, t2.team2, t2.score2
from 
(SELECT mdate, id, matchid, team1,
  sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) as score1
  FROM game LEFT JOIN goal ON id=matchid
  group by mdate, id, matchid, team1) as t1
inner join 
(SELECT mdate,id,team2,
  sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) as score2
  FROM game LEFT JOIN goal ON id=matchid
  group by mdate, id, matchid, team2) as t2
on t1.id=t2.id
order by t1.mdate, t1.matchid, t1.team1, t2.team2

4. 查找可以通过两辆巴士从Craiglockhart到Lochend的巴士的路线。显示第一辆巴士的公司和巴士号，中转站的名称以及第二辆巴士的公司和巴士号。(Find the routes involving two buses that can go from Craiglockhart to Lochend. Show the bus no. and company for the first bus, the name of the stop for the transfer, and the bus no. and company for the second bus.)题目链接：第10题
   思路：分别找到以Craiglockhart为起点和以Lochend为终点的巴士的终点和起点，并用stop将二者联合起来。

select t1.num, t1.company, t1.name, t2.num, t2.company
from (
	--找到以Craiglockhart为起点的巴士的终点站b.stop
	select a.num, a.company, sb.name, b.stop
	from route a
	inner join route b on a.company=b.company and a.num=b.num
	inner join stops sa on a.stop=sa.id and sa.name='Craiglockhart'
	inner join stops sb on b.stop=sb.id
) as t1 inner join
(
	-- 找到以Lochend为终点的巴士的起点站bb.stop
	select c.num, c.company, bb.stop
	from route bb 
	inner join route  c on bb.company=c.company and bb.num=c.num
	inner join stops sc on c.stop=sc.id and sc.name='Lochend'
)as t2 on t1.stop=t2.stop
order by t1.num, t1.name, t2.num

5. 筛选出意大利每周一的新增患者数目，这里的新增=本周一患者数目 - 上周一患者数(Show the number of new cases in Italy for each week - show Monday only.)（题目链接：第4题）
   思路1：问题的难点在于如何获取上周一的患者数目，这里可以借用LAG(col_c, num) over(partition by col_a order by col_b),表示按列col_a进行分组，按col_b进行排序后，对每一行的col_c，获取该行的前面第num条记录的col_c的值显示到这一行，若往前第num条记录不存在，则为null。
   类似的函数还有lead() over()：这个函数与lag相反，是获取本行的下面第num条记录的col_c值显示到本行

SELECT name, DATE_FORMAT(whn,'%Y-%m-%d'), (confirmed-old) as 'new this week'
 FROM (
   select name, whn, confirmed,
      lag(confirmed, 7) over(partition by name order by whn) as old
   from covid
) new_covid
WHERE name = 'Italy'
AND WEEKDAY(whn) = 0
ORDER BY whn

思路2：如果对于LAG函数不熟悉，也可以通过对表进行自连接来实现，这里用到了一个DATE_ADD(date,INTERVAL expr type)的函数，该函数用于向日期添加指定的时间间隔。w3school：DATE_ADD函数介绍

SELECT tw.name, DATE_FORMAT(tw.whn,'%Y-%m-%d'), 
  tw.confirmed-lw.confirmed
 FROM covid tw LEFT JOIN covid lw ON 
  DATE_ADD(lw.whn, INTERVAL 1 WEEK) = tw.whn
   AND tw.name=lw.name
WHERE tw.name = 'Italy' and WEEKDAY(tw.whn) = 0
ORDER BY tw.whn

6. 对于一天中至少有1000个新病例的每个国家/地区，请显示新病例高峰数量的日期(For each country that has had at last 1000 new cases in a single day, show the date of the peak number of new cases.)
   思路：首先选出每个国家的每天的新病例数并筛选出其数量不少于1000的数据，然后便是常规的选出每个国家单日新病例最多的数据

select name, DATE_FORMAT(whn, '%Y-%m-%d'), max(peak) as peakNewCases
from (
	select name, whn,
		confirmed-lag(confirmed, 1) over(partition by name order by whn) as 	peak
	from covid
	--这里记得order，因为后面group by默认选取分组后的第一条记录的基本信息。
	order by name, peak desc
) as c
where peak>=1000
group by name
ORDER BY whn, peakNewCases

7. 显示2017年每个爱丁堡选区赢得的政党.(Show the parties that won for each Edinburgh constituency in 2017.)题目链接：第5题

SELECT constituency,party
  FROM ge g
 WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr = 2017 and votes=(
   	  -- 找到每个选区票数最多的值
      SELECT max(votes) 
      FROM ge 
      WHERE yr=2017 and constituency=g.constituency
      )
ORDER BY constituency

8. 题目来源

• sqlzoo