SQLZOO习题（我的错题）

SQLZOO习题

一、All the vowels

(SELECT from WORLD Tutorial中的第13题)

Equatorial Guinea and Dominican Republic have all of the vowels (a e i o u) in the name. They don’t count because they have more than one word in the name.
Find the country that has all the vowels and no spaces in its name.

• You can use the phrase name NOT LIKE ‘%a%’ to exclude characters from your results.
• The query shown misses countries like Bahamas and Belarus because they contain at least one ‘a’.

参考答案：

SELECT name FROM world
WHERE name LIKE '%a%' AND (len(name) - len(replace(name,'a','')) = 1)
AND name LIKE '%e%' AND (len(name) - len(replace(name,'e','')) = 1)
AND name LIKE '%i%' AND (len(name) - len(replace(name,'i','')) = 1)
AND name LIKE '%o%' AND (len(name) - len(replace(name,'o','')) = 1)
AND name LIKE '%u%' AND (len(name) - len(replace(name,'u','')) = 1)
AND name NOT LIKE '% %'

备注

%a% 包含首字母是a的情况，且大小写均有

二、Nobel_Quiz

Nobel_Quiz的第十三题

Pick the code that shows the amount of years where no Medicine awards were given

解释

第一个选项中，如果有年份又有‘Medicine’又有其他奖项，那么这个年份也会被选中，不符合题意。

三、Knights of the realm

SELECT_from_Nobel_Tutorial的第十三题 排序ORDER BY、DESC、ASC的应用

Knights in order

List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.

答案

SELECT winner, yr, subject FROM nobel
WHERE winner LIKE 'Sir%' ORDER BY yr DESC, winner ASC;

备注

若ORDER BY后面什么都没有，则默认升序排列。

四、Chemistry and Physics last

The expression subject IN (‘Chemistry’,‘Physics’) can be used as a value - it will be 0 or 1.

Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

答案

SELECT winner, subject, case when subject IN ('physics','chemistry') then 1
else 0 end from nobel
WHERE yr='1984'
ORDER BY 
case when subject IN ('physics','chemistry') then 1
else 0 end,
subject,winner

备注

其中case when subject IN ('physics','chemistry') then 1 else 0 end,
表示subject是('physics','chemistry') 时赋值1，否则赋值0。

展示最后一列赋值情况，则结果如下：

先按照最后一列0/1排，然后按照subject顺序排，最后按照winner顺序排。

五、The JOIN operation

The_JOIN_operation中的第八题
The example query shows all goals scored in the Germany-Greece quarterfinal.
Instead show the name of all players who scored a goal against Germany.

SELECT DISTINCT player
  FROM game JOIN goal ON matchid = id 
    WHERE ((team1='GER' OR team2='GER') AND teamid != 'GER')
    #最后一个WHERE语句仔细体会

六、The JOIN operation

The_JOIN_operation中的第八题

For every match involving ‘POL’, show the matchid, date and the number of goals scored.

SELECT matchid,mdate,COUNT(gtime)
  FROM game JOIN goal ON matchid = id 
 WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY matchid,mdate#注意这里写两个,因为SELECT了两个，不然只GROUP BY其中一个的话，另一个怎么显示呢？

七、The JOIN operation

The_JOIN_operation中的第十三题
List every match with the goals scored by each team as shown. This will use “CASE WHEN” which has not been explained in any previous exercises.

mdate	team1	score1	team2	score2
1 Jul	2012	ESP	4	ITA
10 June 2012	ESP	1	ITA	1
10 June 2012	IRL	1	CRO	3
…

Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

SELECT mdate,
       team1,
       SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) AS score1,
       team2,
       SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) AS score2 FROM
    game JOIN goal ON (id = matchid)
    GROUP BY mdate,team1,team2

八、Star Trek movies

More_JOIN_operations的第三题

List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.

SELECT id, title, yr FROM  movie
 WHERE title LIKE '%Star Trek%'  ##注意这里是单引号
ORDER BY yr

注意：LIKE 字段，或者其他使用引号的地方，均为单引号！！！

九、Lead actor in Julie Andrews movies

More_JOIN_operations的第十二题

List the film title and the leading actor for all of the films ‘Julie Andrews’ played in.
列出“‘Julie Andrews’”出演的所有电影的片名和主角

SELECT title, name FROM movie
 JOIN casting ON movie.id=movieid
 JOIN actor   ON actorid=actor.id
WHERE movieid IN
(SELECT movieid FROM casting
WHERE actorid IN (
  SELECT id FROM actor
  WHERE name='Julie Andrews'))  AND ord = 1

备注

嵌套了很多子查询，最内层子查询查找出了’Julie Andrews’的actorid，次内层子查询，找出了’Julie Andrews’出演电影的movieid，最外层查询查找出这些电影的名字和主演。

十、Actors with 15 leading roles

More_JOIN_operations的第十三题

Obtain a list, in alphabetical order, of actors who’ve had at least 15 starring roles.

SELECT name FROM actor
JOIN casting ON actor.id=casting.actorid 
WHERE ord=1
GROUP BY name HAVING COUNT(*)>=15
ORDER BY name

十一、JOIN_Quiz_2

JOIN_Quiz_2的第三题第六题

3. Select the statement that shows the list of actors called ‘John’ by order of number of movies in which they acted

SELECT name, COUNT(movieid)
  FROM casting JOIN actor ON actorid=actor.id
 WHERE name LIKE 'John %'
 GROUP BY name ORDER BY 2 DESC

注：ORDER BY 2 DESC，以第2列降序排列

6. There are two sensible ways to connect movie and actor. They are:

• link the director column in movies with the primary key in actor
• connect the primary keys of movie and actor via the casting table

十二、不能用 “= NULL”，只能用“IS NULL”

十三、Using_Null_Quiz

1. Using_Null_Quiz错题
   注意：展示（display）和设置（set）的区别

十四、SQLZOO的最后一题

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

Hint
Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.

答案

Select c.num,c.company,d.name,d.num,d.company from
(Select distinct a.num,a.company,name,a.stop from
(Select num,company,stop from route where (num,company) in(Select num,company from route
join stops on stops.id=route.stop
where stops.name='Craiglockhart') )a
join stops on stops.id=a.stop) c
join
(Select distinct b.num,b.company,name,b.stop from
(Select num,company,stop from route where (num,company) in(Select num,company from route
join stops on stops.id=route.stop
where stops.name='Lochend') )b
join stops on stops.id=b.stop) d
on c.name=d.name
order by c.num

详细解析