个人关于SQLzoo的总结

1.你不能把一個单引号直接的放在字符串中。但您可连续使用兩個单引号在字符串中當作一個单引号
例。查找尤金•奧尼爾EUGENE O’NEILL得獎的所有細節

SELECT *
FROM nobel
WHERE winner = 'EUGENE O''NEILL';

2.LEFT的用法。
一个字符串’12345’
select left(‘12345’,2)选取的一个字符串位数。
结果
‘12’

例.SELECT winner, yr, subject FROM nobel
WHERE left(winner, 3) = 'Sir'
order by yr desc;

3.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
   FROM nobel
   WHERE yr=1984
   ORDER BY subject IN ('Physics','Chemistry'), subject,winner

4.其中LIMIT用于SELECT语句中，可以强制返回指定的记录数.如果写作 LIMIT n(补全为 LIMIT 0，n),则表示显示前n条记录
列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)

SELECT continent,name 
FROM world x 
WHERE x.name=(SELECT y.name FROM world y WHERE y.continent=x.continent 
ORDER BY name LIMIT 1)；

5.有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。

SELECT name,continent FROM world x
   WHERE x.population / 3 >= ALL(SELECT population FROM world y
      WHERE y.continent = x.continent 
      AND population >0
      AND y.name != x.name);

6.哪幾年的得獎者人數多於12人呢? 列出得獎人數多於12人的年份，獎項和得獎者。

select yr,subject,winner
from nobel
where yr in (select yr
             from nobel
             group by yr
             having count(winner)>12);

7.首次頒發的經濟獎 (Economics)的得獎者是誰?

select winner
from nobel
where yr=(select yr
        from nobel
        where subject='Economics'
        order by yr
        limit 1)
and subject='Economics'；

8.列出有至少100百萬(1億)(100,000,000)人口的洲份

SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population)>=100000000;

9.對於每一個洲份，顯示洲份和國家的數量

SELECT continent,COUNT(name)
FROM world
GROUP BY continent;

10.哪年哪獎項，是同一獎項(subject)頒發給3個人。只列出2000年及之後的資料。

SELECT yr,subject
FROM nobel
WHERE yr >= 2000
GROUP BY yr,subject
HAVING count(subject)=3;

11.每一場波蘭’POL’有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。

SELECT matchid, mdate, COUNT(teamid)
FROM game ga JOIN goal go ON (ga.id= go.matchid)
WHERE (ga.team1 = 'POL' OR ga.team2 = 'POL')
GROUP BY matchid, mdate;

12.列出每场赛事两队得分

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

注：如果用join会丢失两组数据（对比可以发现缺少24 June 2012 ENG 0 ITA 0和27 June 2012 POR 0 ESP 0这两条数据），这两场比赛没有得分，所以没有记录在goal表中，但题目是需要统计这部分数据的，因此必须用外连接。

13.将三个表连接起来

例.列出1962年首影的電影及它的第1主角

SELECT m.title, a.name
FROM casting c 
JOIN movie m ON (m.id= c.movieid)
JOIN actor a ON (a.id= c.actorid)
WHERE c.ord = 1 AND yr = 1962

14.尊·特拉華達’John Travolta’最忙是哪一年? 顯示年份和該年的電影數目
方法1.

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
   movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
 where name='John Travolta'
 GROUP BY yr) AS t
)

方法2.

SELECT yr, COUNT(title)
FROM casting c 
JOIN movie m ON (m.id= c.movieid)
JOIN actor a ON (a.id= c.actorid)
WHERE name = 'John Travolta'
GROUP BY yr
ORDER BY COUNT(title) DESC LIMIT 1;

15.COALESCE(合并)
COALESCE(x,y,z) = x （if x is not NULL)
COALESCE(x,y,z) = y (if x is NULL and y is not NULL)
COALESCE(x,y,z) = z (if x and y are NULL but z is not NULL)
COALESCE(x,y,z) = NULL (if x and y and z are all NULL)

例.Show teacher name and mobile number or '07986 444 2266'
SELECT name, COALESCE(mobile,'07986 444 2266')
FROM teacher

16.Self Join
self join是自己内部连接,自连接，可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。
例.執行自我合拼來，留意b.stop代表由Craiglockhart出發不用轉車可前住的地方。 修改它來顯示由Craiglockhart到 London Road的服務資料

例1.

SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
(a.company=b.company AND a.num=b.num)  //用以确保是同一辆公交车
WHERE a.stop=53 AND b.stop=149

例2.

SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON
(a.company=b.company AND a.num=b.num)
JOIN stops stopa ON (a.stop=stopa.id)
JOIN stops stopb ON (b.stop=stopb.id)  //使站名与编号连接，可以使用站名查找
WHERE stopa.name='Craiglockhart' AND stopb.name='London Road'

例3.

Find the routes involving two buses that can go from Craiglockhart to Sighthill.
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.

SELECT DISTINCT bus1.num, bus1.company, name, bus2.num, bus2.company 
FROM (SELECT start1.num, start1.company, stop1.stop 
      FROM route AS start1 JOIN route AS stop1 
        ON start1.num = stop1.num AND start1.company = stop1.company 
AND start1.stop != stop1.stop 
        WHERE start1.stop = (SELECT id 
                               FROM stops 
                              WHERE name = 'Craiglockhart')) AS bus1 
             JOIN (SELECT start2.num, start2.company, start2.stop 
                     FROM route AS start2 JOIN route AS stop2 
                       ON start2.num = stop2.num AND start2.company = stop2.company AND start2.stop != stop2.stop 
                     WHERE stop2.stop = (SELECT id 
                                           FROM stops 
                                           WHERE name = 'Sighthill')) AS bus2     
                      ON bus1.stop = bus2.stop 
                          JOIN stops 
                            ON bus1.stop = stops.id