SQLZOO练习(仅错题整理)

1. 美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。

顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。

# 方式1
SELECT name, population, area FROM world
WHERE area > 3000000 XOR population > 250000000;
# 方式2（有点傻）
select name, population, area
from world
where name not in 
(select name from world where area > 3000000 and population > 250000000) 
and (area > 3000000 or population > 250000000)

2. 顯示國家有至少一個萬億元國內生產總值（萬億，也就是12個零）的人均國內生產總值。四捨五入這個值到最接近1000。

顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000。

SELECT name, ROUND(gdp/population/1000)*1000 FROM world
WHERE gdp > 1000000000000;

3. Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B

SELECT name,
       CASE WHEN continent IN ('Europe','Asia') THEN 'Eurasia'
            WHEN continent IN ('North America','South America','Caribbean') THEN 'America'
            ELSE continent 
       END AS continent
FROM world
WHERE name LIKE 'A%' OR name LIKE 'B%';

4. Put the continents right…

Oceania becomes Australasia
Countries in Eurasia and Turkey go to Europe/Asia
Caribbean islands starting with ‘B’ go to North America, other Caribbean islands go to South America
Show the name, the original continent and the new continent of all countries.

SELECT name,continent,
CASE WHEN continent IN ('Eurasia', 'Turkey')
     THEN 'Europe/Asia'

     WHEN continent = 'Oceania'
     THEN 'Australasia'

     WHEN continent = 'Caribbean'
          THEN
          CASE
          WHEN name LIKE 'B%'
          THEN 'North America'
          ELSE 'South America'
          END
     ELSE continent
     END
FROM world
ORDER BY name ASC;

5. 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;

-- 当把subject in ('Physics','Chemistry')放在select后面的时候，出来的结果subject in ('Physics','Chemistry')作为一个column显示的是1或0
-- 这就是说当把subject in ('Physics','Chemistry')放在order by后面的时候，先是0，后是1，也就是先排序非物理和化学领域，再排序化学和物理领域。因此把subject in ('Physics','Chemistry')放在ORDER BY后第一个可以保证物理和化学领域在最后

6. 列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)

方法1：

SELECT continent,name FROM world x
WHERE name <= ALL(SELECT name FROM world y
                  WHERE y.continent = x.continent);

方法2：

SELECT continent,name FROM world
WHERE name IN (SELECT MIN(name) FROM world
               GROUP BY continent);

7. 找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。

SELECT name,continent,population FROM world x
WHERE 25000000 >= ALL(SELECT population FROM world y
                      WHERE y.continent = x.continent
                      AND y.population > 0);
-- ALL子句放在最后

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

select name, continent
from world x
where population >= ALL(select 3*population from world y where x.continent = y.continent and x.name != y.name)

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

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

10. 如表所示，列出每支球队的每场比赛和得分。这将使用“CASE WHEN”，这在以前的练习中没有解释过。注意，在查询中列出了每个目标。如果是团队1的进球，则在score1中出现1，否则为0。您可以将这一列相加，得到team1的进球数。根据mdate, matchid, team1和team2对你的结果排序。

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 JOIN goal ON matchid = id
GROUP BY mdate,matchid,team1,team2
ORDER BY mdate,matchid,team1,team2;		-- LEFT JOIN是为了防止缺少比赛中双方都没进球的情况

11. 顯示電影異型’Alien’ 的演員清單。

方法1：

select name
from actor join casting on (id = actorid)
where movieid = (select id from movie where title = 'Alien')

方法2：

SELECT name
FROM casting 
JOIN actor ON actor.id = actorid
JOIN movie ON movie.id = movieid
WHERE title = 'Alien';

12. 用例显示每个老师的名字，如果老师在1或2系，后面跟着“Sci”，否则是“Art”。

SELECT name,
       CASE WHEN dept IN (1,2) THEN 'Sci'
            ELSE 'Art' END AS dept
FROM teacher;

13. 执行所示的self join并观察b。停给出所有你可以从克雷格洛克哈特到达的地方，而不改变路线。更改查询，使其显示从Craiglockhart到London Road的服务。

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
JOIN stops c ON c.id = a.stop
JOIN stops d ON d.id = b.stop
WHERE c.name = 'Craiglockhart' AND d.name = 'London Road';

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

SELECT S.num,S.company,stops.name,E.num,E.company
FROM(SELECT a.num,a.company,b.stop
FROM route a JOIN route b ON(a.company = b.company AND a.num=b.num)
WHERE a.stop=(SELECT id FROM stops WHERE name='Craiglockhart'))S
JOIN
(SELECT c.num,c.company,d.stop
FROM route c JOIN route d ON(c.company = d.company AND c.num=d.num)
WHERE c.stop=(SELECT id FROM stops WHERE name='Lochend'))E
ON(S.stop=E.Stop)
JOIN stops ON (stops.id =S.stop)
order by S.num,S.company,stops.name,E.num,E.company