SQL练习2-SQLZoo 4-6章错题整理

重点语法
concat & round, ALL, CASE WHEN

4 SELECT within SELECT Tutorial

地址：sqlzoo.net/wiki/SELECT…

表结构
world (name, continent, area, population, gdp)

Q & A
5. Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.
Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.
The format should be Name, Percentage for example:

name	percentage
Albania	3%
Andorra	0%
Austria	11%
...	...

SELECT name,CONCAT(ROUND(100*population/(select population from world where name='Germany')),'%') 
    FROM world 
    WHERE continent='Europe'

• 困难：同时用到字符串拼接及保留小数位数函数，函数的嵌套。

6. Which countries have a GDP greater than every country in Europe? [Give thenameonly.] (Some countries may have NULL gdp values)

• wrong answer:

SELECT name FROM world
    WHERE gdp > ALL(SELECT gdp 
                        FROM world
                        WHERE continent='Europe')

查出来是空值，因为提示说有些国家的gdp是空值，所以要把为空值的先排除掉。

SELECT name FROM world 
    WHERE gdp > ALL(SELECT gdp 
                        FROM world 
                        WHERE gdp > 0 AND continent='Europe')

7. 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 >= ALL(SELECT area 
                        FROM world y
                        WHERE y.continent=x.continent)

• 知识点：不用group by实现了分组查询.

8. List each continent and the name of the country that comes first alphabetically.

SELECT continent,name FROM world w1
    WHERE name <= ALL(SELECT name 
                        FROM world w2
                        WHERE w1.continent=w2.continent)
    ORDER BY continent,name

10.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 x.population/3 >= ALL(SELECT population 
                                    FROM world y 
                                    WHERE x.continent=y.continent 
                                    AND x.name!=y.name 
                                    AND y.population>0)

• 注意 x.name!=y.name 这里.

6 The JOIN operation

地址：sqlzoo.net/wiki/The_JO…

表结构
game (id, mdate, stadium, team1, team2)
goal (matchid, teamid, player, gtime)
eteam (id, teamname, coach)

Q & A
13.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 July 2012	ESP	4	ITA	0
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) 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