【DA】-【SQL】-【SQLZOO】-刷题记录

文章目录

- - 0.描述
  - 1.sql编写题
  - - 1.1 world表相关
    - - 1.1.1 表结构
      - 1.1.2 题目8Exclusive OR (XOR).
      - 1.1.3 题目8参考答案
      - 1.1.4 题目10
      - 1.1.5 题目10参考答案
      - 1.1.6 本部分总结
    - 1.2 nobel表相关
    - - 1.2.1 表结构
      - 1.2.2 题目14
      - 1.2.3 总结
    - 1.3 select嵌套
    - - 1.3.1 表结构
      - 1.3.2 题目6
      - 1.3.3 题目6答案
      - 1.3.4 题目7
      - 1.3.5 题目7答案：
      - 1.3.6 题目10：
      - 1.3.7 题目10答案：
      - 1.3.7 本部分总结
    - 1.4 求和与计数
    - - 1.4.1 表结构
      - 1.4.2 本部分总结
    - 1.5 Join用法（一）
    - - 1.5.1 收获总结
    - 1.6 Join用法（二）
    - - 1.6.1 本部分总结
    - 1.7使用null
    - - 1.7.1 总结
    - 1.8 自连接
    - - 1.8.1 本部分总结

0.描述

刷题过程中，个人认为值得记录的内容。
SQL zoo网址：http://old.sqlzoo.net/
sqlzoo答案参考（全）这篇博客值得参考。

1.sql编写题

1.1 world表相关

SELECT from WORLD Tutorial

1.1.1 表结构

【DA】-【SQL】-【SQLZOO】-刷题记录

1.1.2 题目8Exclusive OR (XOR).

(1)Show the countries that are big by area or big by population but not both. （人口多：>=250,000,000；面积大：>=300,000,000）Show name, population and area.展示面积大或人口多（但不包括人口多且面积大）的国家姓名，人口和面积。

(2)Australia has a big area but a small population, it should be included.Indonesia has a big population but a small area, it should be included.澳大利亚面积大但人口少，符合要求；印度尼西亚人口多但面积小，也符合要求。

(3)China has a big population and big area, it should be excluded.咱们中国面积大且人口多，所以不满足查询要求。

(4)United Kingdom has a small population and a small area, it should be excluded.关于英国，人口少且面积小，显然不符合要求。

1.1.3 题目8参考答案

注意xor的用法！

select name, population, area
from world
where area>=3000000 xor population>=250000000;

1.1.4 题目10

Show the name and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.显示那些GDP至少为1万亿（1000000000000；即12个零）的国家的名称和人均GDP。将此值舍入为最接近的1000。

Show per-capita GDP for the trillion dollar countries to the nearest $1000.显示万亿美元国家的人均GDP，精确到1000美元。

1.1.5 题目10参考答案

起初我不知道round()函数的第二个参数可以为负数，所以我的思路是先将数值对1000取整再乘以1000达到精确到1000的效果，如下：

select name, round(gdp/1000/population)*1000 as 'per-capita GDP'
from world
where gdp>=1000000000000;

实际上，通过round(value, -3)达到的效果一样且简单，如下：

select name, round(gdp/population, -3) as 'per-capita GDP'
from world
where gdp>=1000000000000;

1.1.6 本部分总结

（1）xor的用法和or一样，直接加在条件之间；效果表示仅满足其中一个，是比or更严格的条件。

（2）round(value, bits)第二个参数可以是负数，表示向小数点前推bits位。比如round(1234, -2)，得到1200.

1.2 nobel表相关

SELECT from Nobel Tutorial

1.2.1 表结构

【DA】-【SQL】-【SQLZOO】-刷题记录

1.2.2 题目14

Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.显示1984年获奖者和主题（按主题和获奖者名称排序）；但最后列出化学和物理。
提示：The expression subject IN (‘Chemistry’,‘Physics’) can be used as a value - it will be 0 or 1.可以将表达式主题IN（“化学”，“物理”）用作值-它可以是0或1。
分析：因为需要将化学和物理排在最后，根据提示“subject IN (‘Chemistry’,‘Physics’)”的结果位0或1，所以可以先利用“subject IN (‘Chemistry’,‘Physics’)”的结果进行升序排序，这样结果位1的化学和物理就会显示在最后。
我的答案：

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

1.2.3 总结

subject in (‘c’,‘p’)会返回0或1，可以作为排序的依据。

1.3 select嵌套

SELECT_within_SELECT_Tutorial

1.3.1 表结构

所用数据依然是world表；

1.3.2 题目6

哪些国家的GDP高于欧洲每个国家？（某些国家/地区的gdp值可能为NULL）。

1.3.3 题目6答案

select name from world
where gdp>all(select gdp from world where continent='Europe' and gdp>0);

或者用is not null替换>0作为判断非空的方式。

select name from world
where gdp>all(select gdp from world where continent='Europe' and gdp is not null);

注意使用>all判断时，如果有数据为空null，会出错。

1.3.4 题目7

查找每个大陆中最大的国家（按面积），显示该大陆，名称和区域。

1.3.5 题目7答案：

答案1通过表连接：

select w1.continent, name, area from world as w1 inner join(
select continent, max(area) as max_area from world group by continent) as w2 on w1.continent=w2.continent
where w1.area = w2.max_area ;

答案2使用>all(大于除了自己的所有本大洲其他国家面积):

select continent,name,area from world as w1
where area>all(select w2.area from world as w2
where w2.name<>w1.name and w1.continent=w2.continent);

或者>=all（大于本大洲所有国家面积）(这里判断条件加了area>0，考虑更全面，防止area出现null空值导致结果出错):

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

1.3.6 题目10：
1.3.7 题目10答案：

select w1.name,w1.continent from world as w1
where w1.population>=all(select w2.population*3 as population from world as w2
where w2.continent=w1.continent and w1.name<>w2.name);

最开始我使用的是>any，是错的。any指的是满足其中任何一个即可，而all才是满足所有。

1.3.7 本部分总结

注意all和any的使用区别，并注意返回的值是否存在null空值。

1.4 求和与计数

sum and count

1.4.1 表结构

依然是world表；

1.4.2 本部分总结

注意（原则上）group by后字段才可以被select；

1.5 Join用法（一）

The JOIN operation

1.5.1 收获总结

select后的非聚合字段需要在group by中出现
case when x then a else b end
使用join时应当注意左右连接，注意null值	当select选择内容不允许null空值时使用inner join
在本部分13题当中，统计每场比赛主客场球队的进球数，应该对比赛game表进行左连接left join	因为在0：0的比赛当中，在进球goal表当中均没有记录，因此应当允许比赛连接空null的进球纪录，记为0

1.6 Join用法（二）

More Join Operations

1.6.1 本部分总结

要取某字段最大值所在行的其他值	可以通过两种方法
第一种是嵌套查询，将查询出来的字段最大值作为外层的查询条件	第二种是对该字段降序排序，用limit 1取第一行

1.7使用null

1.7.1 总结

coalesce()函数	返回参数当中第一个不为null的值
判断是否为空	不能用=null，而用is null
count(字段)	返回不包括取值为空的计数
case when的用法	可写多个when，比如:case when x then a when y then b when z then c else d end

1.8 自连接

Self Join

1.8.1 本部分总结

使用多重自连接时	关键在于先理清思路
比如从站A经某一未知站B到达站C
分析可知：A和B是一条线路，B和C是一条线路	因此可以通过两次自连接之后，再次自连接
即A连接B1，通过线路和公司字段	B2连接C，通过线路和公司字段
B1和B2连接，通过站点stop连接

【DA】-【SQL】-【SQLZOO】-刷题记录

文章目录

0.描述

1.sql编写题

1.1 world表相关

1.1.1 表结构

1.1.2 题目8Exclusive OR (XOR).

1.1.3 题目8参考答案

1.1.4 题目10

1.1.5 题目10参考答案

1.1.6 本部分总结

1.2 nobel表相关

1.2.1 表结构

1.2.2 题目14

1.2.3 总结

1.3 select嵌套

1.3.1 表结构

1.3.2 题目6

1.3.3 题目6答案

1.3.4 题目7

1.3.5 题目7答案：

1.3.6 题目10：

1.3.7 题目10答案：

1.3.7 本部分总结

1.4 求和与计数

1.4.1 表结构

1.4.2 本部分总结

1.5 Join用法（一）

1.5.1 收获总结

1.6 Join用法（二）

1.6.1 本部分总结

1.7使用null

1.7.1 总结

1.8 自连接

1.8.1 本部分总结

继续阅读