(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.
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的情况，且大小写均有
Pick the code that shows the amount of years where no Medicine awards were given
第一个选项中，如果有年份又有‘Medicine’又有其他奖项，那么这个年份也会被选中，不符合题意。
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后面什么都没有，则默认升序排列。
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 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语句仔细体会
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中的第十三题
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
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
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，最外层查询查找出这些电影的名字和主演。
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
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列降序排列
注意：展示（display）和设置（set）的区别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