SQL学习-sqlzoo练习题记录(3)

4-子查询
注意：子查询有所会得出多于一个的结果，如果进行结果比较会出现错误，此时使用IN会更加安全。

原网站教学：
1)

SELECT name FROM world WHERE continent = (SELECT continent FROM world WHERE name = 'Brazil')

2)

SELECT name, continent FROM worldWHERE continent IN  (SELECT continent      FROM world WHERE name='Brazil'                   OR name='Mexico')

3)

SELECT population/(SELECT population FROM world             WHERE name='United Kingdom')  FROM worldWHERE name = 'China'

4)

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

注意点：可以使用 ALL 或 ANY ，当运算符后有多于一个值后

练习：
1)

SELECT name FROM worldWHERE population >     (SELECT population FROM world      WHERE name='Russia')

2)

SELECT name FROM worldWHERE continent='Europe' AND gdp/population >    (SELECT gdp/population FROM world WHERE name='United Kingdom')

3)

SELECT name,continent FROM worldWHERE continent IN (SELECT continent FROM world WHERE name IN ('Argentina','Australia'))ORDER BY name

4)

SELECT name,populationFROM worldWHERE population>(SELECT population FROM world WHERE name='Canada') AND population<(SELECT population FROM world WHERE name='Poland')

5)

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

注意读题
6)

SELECT nameFROM worldWHERE gdp > ALL (SELECT gdp FROM world WHERE continent='Europe' AND gdp!=0)

错误点：不排除掉NULL，返回的结果会出现错误
7)

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

8)

SELECT continent,nameFROM world as aWHERE name <= ALL (SELECT name FROM world as b WHERE a.continent=b.continent) 

注意点：<= />=(从z开始)
9)

SELECT name,continent,populationFROM world as aWHERE 25000000 >= ALL(SELECT population FROM world as b WHERE a.continent=b.continent)

注意点：别名as可以省略
10)

SELECT name,continentFROM world aWHERE a.population/3 >= ALL(SELECT population FROM world b WHERE a.continent=b.continent AND a.name!=b.name)

错误点：不相等的国家名

5-群组函数
1)

SELECT sum(population)FROM world

2)

SELECT distinct continentFROM world

注意点：DISTINCT，除重复值
3)

SELECT sum(gdp)FROM worldWHERE continent='Africa'

4)

SELECT count(name)FROM worldWHERE area>1000000

5)

SELECT sum(population)FROM worldWHERE name IN ('France','Germany','Spain')

以上使用SUM/COUNT/DISTINCT
6)

SELECT continent,count(name)FROM worldGROUP BY continent

GROUP BY 分组
7)

SELECT continent,count(name)FROM worldWHERE population>10000000GROUP BY continent

注意点：GROUP BY 前面要结合聚合函数一起用
8)

SELECT continent FROM worldGROUP BY continentHAVING SUM(population)>=100000000

注意点：HAVING功能可以在GROUP BY排完的结果后进行筛选

诺贝尔奖-分组函数
1)

SELECT COUNT(winner) FROM nobel

2)

SELECT DISTINCT subject FROM nobel

3)

SELECT COUNT(winner)FROM nobelWHERE subject='Physics'

4)

SELECT subject,count(winner)FROM nobel GROUP BY subject

5)

SELECT subject,min(yr)FROM nobelGROUP BY subject

6)

SELECT subject,count(winner)FROM nobelWHERE yr=2000GROUP BY subject

7)

SELECT subject,count(DISTINCT winner)FROM nobelGROUP BY subject

8)

SELECT subject,count(DISTINCT(yr))FROM nobelGROUP BY subject

注意点：count(DISTINCT(name))——统计不同数，结合分组函数
9)

SELECT yrFROM nobelWHERE subject='Physics'GROUP BY yrHAVING count(winner)=3

错误点：GROUP BY 和 SELECT 是相对应？
10)

SELECT winnerFROM nobelGROUP BY winnerHAVING count(winner)>1

11)
列出谁获得多于一个奖项

SELECT winnerFROM nobelGROUP BY winnerHAVING count(DISTINCT(subject))>1

错误点：没有考虑重复的条件
12)

SELECT yr,subjectFROM nobelWHERE yr>=2000GROUP BY yr,subjectHAVING count(winner)=3

注意点：在select指定的字段，要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中

where——在分组之前过滤数据，不能包含聚组函数
having——在分组之后过滤数据，条件中经常包含聚组函数