子查询查缺补漏

题目来自sqlzoo的子查询题目。网址：

https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial/zh

 

 

 

 

表列名含义：

name:国家名称

continent:该国家属于哪个洲

area:面积

population:人口

gdp:国内

 

5.德国(Germany)在欧洲(Europe)國家的人口最多。奧地利(Austria)拥有德国总人口的11％。

查找欧洲的国家名称和每个国家的人口，其中人口以德国人口的百分比来显示人口数

【知识点】标量子查询，字符串连接函数concat,浮点数保留多少位round函数

-- 拼接函数
-- concat(round(population*100/(select population from world where name='Germany')), '%') AS population
SELECT name,
       CONCAT(round(population*100/(select population from world where name='Germany')),
              '%') AS population 
FROM world
WHERE continent = 'Europe';

6.哪些国家的GDP比欧洲(Europe)的全部国家都要高呢?  (有些国家的记录中，GDP是空值NULL，没有填入资料）

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

7.在每一个州中找出最大面积的国家，查找出洲, 国家名字，面积。 (有些国家的记录中，面试是空值NULL，没有填入资料） 

select continent, name, max(area )
from world
group by continent
-- 错了！！groupby只有一个continent但是select出现了那么 

-- 自己的做法 
select continent, name, area 
from world as x
where area = 
(select max(area) 
from world as y
where y.continent=x.continent)

-- 答案的做法
select continent, name, area 
from world as x
where area >= all
(select area 
from world as y
where y.continent=x.continentand area>0);
 
-- 自己编了一个小例子看看自己的做法也是对的~~
DROP TABLE appointment;
 
CREATE TABLE appointment ( 

appointment_id  INT  , 

patient INT NOT NULL,

room VARCHAR(25) NOT NULL

); 

INSERT INTO appointment VALUES(1,101,'A');

INSERT INTO appointment VALUES(NULL,103,'B');

INSERT INTO appointment VALUES(3,101,'A');

INSERT INTO appointment VALUES(4,101,'B');

INSERT INTO appointment VALUES(5,102,'A');

INSERT INTO appointment VALUES(6,101,'B');

INSERT INTO appointment VALUES(NULL,102,'A');

 
select appointment_id, patient, room
from appointment as x
where appointment_id = 
(select max(appointment_id) 
from appointment as y
where y.room = x.room);

8.找出符合条件的洲和国家名称，条件：该洲中的全部国家人口都有少于或等于 25000000 人口）

select name, continent, population 
from world as x
where 25000000 >= all
(select population 
from world as y
where y.continent=x.continent);

 

9.有些国家的人口是同洲份的所有其他国的3倍或以上。列出这些国家的名称和洲

select continent, name 
from world as x
where population > all (SELECT 3*population 
                        FROM world w2
                        WHERE w1.continent = w2.continent
                          AND w2.name <> w1.name)
                          ;