此文记录sqlzoo的全部刷题记录，不包括那些比较简单的题

1.SELECT names

表名: world 字段名: name continent area population gdp capital tld flag

Find the capital and the name where the capital includes the name of the country.

SELECT capital, name FROM world WHERE capital like concat('%',name,'%')

Find the capital and the name where the capital is an extension of name of the country.

SELECT capital, name FROM world WHERE captial like concat('%', name, '%') and capital <> name

Show the name and the extension where the capital is an extension of name of the country.

SELECT name, replace(capital, name, '') as extension FROM world WHERE capital like conat('%', name, '%') and capital <> name

2.SELECT FROM world

表结构同上

Show the name and the capital where the first letters of each match. Don’t include countries where the name and the capital are the same word.

SELECT name, capital FROM world WHERE LEFT(name,1) = LEFT(capital,1) AND name <> capital

Find the country that has all the vowels and no spaces in its name.

SELECT name FROM world WHERE name LIKE '%a%' AND name LIKE '%e%' AND name LIKE '%i%' AND name LIKE '%o%' AND name LIKE '%u%' AND name NOT LIKE '% %'

3.Select within select

表结构同上

Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values) 注意NULL和ALL连用的坑

SELECT name FROM world WHERE gdp > all (SELECT gdp FROM world WHERE continent='europe' AND gdp IS NOT NULL)

Find the largest country (by area) in each continent, show the continent, the name and the area 关联子查询

SELECT continent, name, area FROM world x WHERE area = (SELECT MAX(area) FROM world y WHERE x.continent = y.continent)

or

SELECT continent, name, area FROM world x WHERE area >= ALL (SELECT area FROM world y WHERE x.continent = y.continent)

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.

SELECT name, continent, population FROM world x WHERE 25000000 >= ALL(SELECT population FROM world y WHERE x.continent = y.continent)

Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

SELECT name, continent FROM world x WHERE population/3 >= all (SELECT population FROM world y WHERE x.continent=y.continent AND x.name <> y.name)

4.SELECT from Nobel

表名： nobel 列名： yr，subject，winner

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

5.The Join Operation

show the name of all players who scored a goal against Germany

SELECT distinct player FROM game g INNER JOIN goal s ON g.id = s.matchid WHERE (team1 = 'GER' OR team2 = 'GER') AND teamid <> 'GER'

For every match where ‘GER’ scored, show matchid, match date and the number of goals scored by ‘GER’

SELECT id, mdate, count(player) FROM game INNER JOIN goal ON id = matchid WHERE teamid = 'GER' GROUP BY id, mdate

List every match with the goals scored by each team as shown. Sort your result by mdate, matchid, team1 and team2.

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

6. Using NULL

表结构：

COALESCE(value1, value2, value3,…) Use COALESCE to print the mobile number. Use the number ‘07986 444 2266’ if there is no number given. Show teacher name and mobile number or ‘07986 444 2266’

SELECT name, COALESCE(mobile, '07986 444 2266') FROM teacher

Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.

SELECT d.name, COUNT(t.name) AS staff FROM dept d LEFT JOIN teacher t ON d.id = t.dept GROUP BY d.name

Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2, show ‘Art’ if the teacher’s dept is 3 and ‘None’ otherwise.

SELECT name, CASE WHEN dept=1 or dept=2 THEN 'Sci' WHEN dept = 3 THEN 'Art' ELSE 'None' END FROM teacher

7.Numerical Examples

表结构： 说明：每个学校、每个专业、每个问题为一行，受访人数为sample，有效回复为response，各等级回答及score为百分比（和response的）

Show the subject and total number of students who responded to question 22 for each of the subjects ‘(8) Computer Science’ and ‘(H) Creative Arts and Design’.

SELECT subject, sum(response) FROM nss WHERE question='Q22' AND subject in ('(8) Computer Science', '(H) Creative Arts and Design') GROUP BY subject

Show the subject and total number of students who A_STRONGLY_AGREE to question 22 for each of the subjects ‘(8) Computer Science’ and ‘(H) Creative Arts and Design’.

SELECT subject, SUM(A_STRONGLY_AGREE*response/100) FROM nss WHERE question='Q22' AND subject in ('(8) Computer Science', '(H) Creative Arts and Design') group by subject

Show the percentage of students who A_STRONGLY_AGREE to question 22 for the subject ‘(8) Computer Science’ show the same figure for the subject ‘(H) Creative Arts and Design’.

SELECT subject, ROUND(100*SUM(A_STRONGLY_AGREE*response/100)/SUM(response)) FROM nss WHERE question='Q22' AND subject in ('(8) Computer Science', '(H) Creative Arts and Design') GROUP BY subject

Show the average scores for question ‘Q22’ for each institution that include ‘Manchester’ in the name.

SELECT institution, round(100*sum(score*response/100)/sum(response)) FROM nss WHERE question = 'Q22' AND institution LIKE '%Manchester%' GROUP BY institution

Show the institution, the total sample size and the number of computing students for institutions in Manchester for ‘Q01’.

SELECT institution, SUM(sample), SUM(CASE WHEN subject='(8) Computer Science' THEN sample ELSE 0 end) FROM nss WHERE question = 'Q01' AND institution LIKE '%Manchester%' GROUP BY institution

8. SELF JOIN

表结构： stops

route num：公交车代号 conpany：公交车公司 pos：访问的次序 stop：访问的站点，对应stops表格

Give the id and the name for the stops on the ‘4’ ‘LRT’ service.

SELECT id, name FROM stops WHERE id IN (SELECT stop FROM route WHERE company='LRT' AND num= '4')

Change the query so that it shows the services from Craiglockhart to London Road.

SELECT a.company, a.num, a.stop, b.stop FROM route a INNER JOIN route b ON a.num = b.num AND a.company = b.company WHERE a.stop = 53 and b.stop = 149

Change the query so that the services between ‘Craiglockhart’ and ‘London Road’ are shown. If you are tired of these places try ‘Fairmilehead’ against ‘Tollcross’

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'

Give a list of all the services which connect stops 115 and 137 (‘Haymarket’ and ‘Leith’)

SELECT DISTINCT a.company, a.num FROM route a INNER JOIN route b ON (a.num = b.num AND a.company = b.company) WHERE a.stop = 115 and b.stop = 137

Give a list of the services which connect the stops ‘Craiglockhart’ and ‘Tollcross’

SELECT a.company, a.num FROM route a INNER JOIN route b ON (a.num = b.num AND a.company = b.company) INNER JOIN stops as stopa on stopa.id = a.stop INNER JOIN stops as stopb on stopb.id = b.stop WHERE stopa.name = 'Craiglockhart' and stopb.name = 'Tollcross'

Give a distinct list of the stops which may be reached from ‘Craiglockhart’ by taking one bus, including ‘Craiglockhart’ itself, offered by the LRT company. Include the company and bus no. of the relevant services.

SELECT DISTINCT stopa.name, a.company, a.num FROM route a INNER JOIN route b ON (a.num = b.num AND a.company = b.company) INNER JOIN stops stopa ON stopa.id = a.stop INNER JOIN stops stopb ON stopb.id = b.stop WHERE a.company = 'LRT' AND stopb.name = 'Craiglockhart'

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. 找到坐两辆车能从Craiglockhart 到Lochend的路线，并给出中转站点

这题难度比较大

SELECT a.num, a.company, stopb.name, d.num, d.company FROM route a INNER JOIN route b ON (a.num=b.num AND a.company=b.company) INNER JOIN route c ON b.stop = c.stop INNER JOIN route d ON (c.num=d.num AND c.company=d.company) INNER JOIN stops stopa ON stopa.id = a.stop INNER JOIN stops stopb ON stopb.id = b.stop INNER JOIN stops stopd ON stopd.id = d.stop WHERE stopa.name = 'Craiglockhart' AND stopd.name = 'Lochend'

详细写法

SELECT DISTINCT v1.num,v1.company, name,v2.num,v2.company // 找出经过Craiglockhart的车辆和其途径的其它站点集合v1(num,company,stop) FROM (SELECT r1.num ,r1.company ,r2.stop FROM route AS r1 JOIN route AS r2 ON (r1.num=r2.num AND r1.company=r2.company)JOIN stops ON r1.stop=stops.id WHERE stops.name='Craiglockhart' AND r2.stop!= r1.stop) AS v1 JOIN // 找出经过Lochend的车辆和其途径的其它站点集合v2(num,company,stop) (SELECT r1.num,r1.company,r2.stop FROM route AS r1 JOIN route AS r2 ON (r1.num=r2.num AND r1.company=r2.company)JOIN stops ON r1.stop=stops.id WHERE stops.name='Lochend' AND r2.stop!= r1.stop) AS v2 // v1和v2需要经过同一个站点作为周转站点 ON v1.stop=v2.stop JOIN stops ON v1.stop=stops.id