SQL 查漏补缺

1.显示 GDP 至少为 1 万亿（1000000000000;即 12 个零）的国家/地区的名称和人均 GDP。将人均GDP舍入到最接近的 1000，即保留至千位

【解析】round，第2个参数为正数时，表示保留几位小数；如果为负数时，表示取几位数字；

select name, round(gdp/population,-3) from world
where gdp >= 1000000000000;

2.查找1984年获奖者和主题，按主题和获胜者名称排序，并把化学奖和物理奖排到最后面显示。

SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Physics','Chemistry'),subject,winner

【解析】：

SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY
CASE
WHEN subject IN ('Physics','Chemistry') THEN 1
ELSE 0
END ASC,
subject,
winner

subject in()条件是一个Boolean表达式，
如果满足这个条件，这个表达式的结果=1；否则不满足条件，这个表达式的结果=0.
默认情况下，按条件升序排列；因此排序的结果将是0在前，而1在后；所以满足条件的将会在最后显示。

3.查找名称中有三个或更多A 的国家/地区。
下面展示一些 内联代码片。

SELECT name 
FROM worldWHERE name LIKE '%A%A%A%'

4.查询有两个 o 字符，由另外两个字符分隔的国家/地区。

SELECT name 
FROM worldWHERE name LIKE '%o__o%'

5.查找正好有四个字符的国家/地区。

SELECT name 
FROM world
WHERE name LIKE '____'

6.查询：首都名称包含国家名称的首都

select capital, name from world where capital like concat('%',name,'%')

7.显示首都是国家名称扩展的名称和扩展。
【解析】此处用到replace替代函数，replace（原内容，需替换部分，替换后内容）

select name, replace (capital, name, '') from world where capital like concat(name,'%') and capital <> name;

8.列出包括阿根廷或澳大利亚在内的各大洲的国家名称和大洲

select name,continent from world where continent in (select continent from world where name in('Argentina', 'Australia')) order by name

9.哪些国家的GDP比欧洲的每个国家都高?只告诉我名字。(有些国家可能有零gdp值)

SELECT name FROM world

WHERE gdp>All(SELECT gdp FROM world WHERE continent='Europe'

      AND  gdp IS NOT NULL)

10.在每个洲找出最大的国家(按面积)，显示洲，名称和地区:

select continent, name, area
from world x
where area >= all(select area
                  from world y
                 where x.continent=y.continent
                 and area>0)

or

select continent,name,area 
from world as x
where area =
(select max(area)
from world as y
where s1.continent=s2.continent);

or

select continent,name,area from world where area in(select max(area) from world group by continent)

【解析】：首先选择一个洲比如Asia，然后进行子查询SELECT area FROM world yWHERE y.continent='Asia' AND area>0)找出所有area>0的情况，然后再执行主查询中的where area >= ALL（关联子查询）条件，即筛选出面积大于等于子查询查询出的所有情况，从而找到该洲面积最大的国家。

注意：这里需要注意的是：

select name,continent,area from world where continent = 'Asia'

这样的查询是合法的，但是以下的查询方式却会报错：

select name,continent,max(area) from world where continent = 'Asia'

Column ‘world.name’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
因此查询的列中聚合函数和单列名不要一起使用，这里Group by例外：

select continent,max(area) from world where continent = 'Asia' group by continent

这样的查询也是合法的，因为continent是后面group by分组的条件，

select name,continent,max(area) from world where continent = 'Asia' group by continent

这样的查询是非法的，主要是name非法，既不是在聚合函数中，又不在group
by子句中

11.列出每一个大陆和按字母顺序排列第一的国家的名称

SELECT continent,name FROM world a WHERE name <= ALL(SELECT name from world b WHERE a.continent = b.continent )ORDER by name

12.找出所有国家的人口都小于2500亿的大陆。然后找出与这些大陆相关的国家的名字。显示姓名，大陆和人口。

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

13.有些国家的人口是其邻国(在同一大陆)的三倍还多。给出国家和大陆。

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

【解析：】首先选择一个洲比如Asia，然后进行子查询select population from world as y where y.continent = 'Asia' AND y.name != 'China' 找出所有population>0的情况，然后再执行主查询中的where population/3 >= ALL（关联子查询）条件

where x.continent=y.continent – 洲相同
and x.name != y.name – 名字不能相同

14.列出“朱莉·安德鲁斯”出演的所有电影的片名和主演。

select title, name   -- 找出电影和主演
from movie
join casting on (movieid=movie.id and ord=1)   -- 3. 找出主演
join actor on (actorid=actor.id)
where movie.id in (select movieid    -- 2.从1中选择出的全部演员中找出全部的电影movieid
                  from casting 
                  where actorid in (select id   -- 1. 找出JA参加的电影的全部演员actorid
                                  from actor 
                                  where name='Julie Andrews'))

15.按字母顺序列出至少有15个主演的演员名单

select name
from actor 
join casting on casting.actorid=actor.id
group by name
having sum(case ord when 1 then 1 else 0 end) >= 15  -- 当ord=1则计数1，sum求和
order by name;