![c06b179f1ffef3be3b4c9e44174927ba.png](https://i-blog.csdnimg.cn/blog_migrate/ab5855bfad78f3b3da0accbd9a28b381.jpeg)
#创建视图
create view 按性别汇总(性别,人数)
as
select 性别,count(*)
from student
group by 性别;
select 性别,人数
from 按性别汇总;
#子查询
select 性别,人数
from(
select 性别,count(*) as 人数
from student
group by 性别
)as 按性别汇总;
select 学号,成绩
from score
where 成绩 in(
select min(成绩)
from score
group by 课程号
);
select 学号,成绩
from score
where 成绩 > all(
select 成绩
from score
where 课程号 = '0002'
);
![ff1dfa9f3f0dafd37e6326258ac90627.png](https://i-blog.csdnimg.cn/blog_migrate/5891f8788f3240cd5d37cfaf045f2b0d.jpeg)
#标量子查询
select 学号,成绩
from score
where 成绩 > (
select avg(成绩)
from score
);
select 学号,成绩
from score
where 成绩 between
(select avg(成绩)
from score
where 成绩 <= 60) and
(select avg(成绩)
from score
where 成绩 >= 80);
select 学号,成绩,(select avg(成绩)
from score) as 平均成绩
from score;
![6a9a13092f196634f8f40e81989cfa5d.png](https://i-blog.csdnimg.cn/blog_migrate/60242cd4814f6061b14c3e5175f77a44.jpeg)
#关联子查询,比较难理解
select 学号,课程号,成绩
from score as s1
where 成绩 >
(select avg(成绩)
from score as s2
where s1.课程号 = s2.课程号
group by 课程号
);
![32759ea475844bd6d5bbd7479ea017e9.png](https://i-blog.csdnimg.cn/blog_migrate/3a7c5f066f48d58459a90fc3f7eac57d.jpeg)
明显能感觉到第四关开始难度上升了很多,无论是课程还是习题。所以我都有开始写注释了,怕以后忘了看不懂,知乎live的图片我也做成了PPT和PDF两种格式,以后看起来方便一些。
![220f3a0aa96506156f4486f9f0e1ed89.png](https://i-blog.csdnimg.cn/blog_migrate/aea39661f8e400094fa64cf675134f7e.jpeg)
5,8,10三个题目想了很久,5最后还是做出来了,感觉自己在瞎凑。。把那个语句写的特别复杂,主要是要表示出德国的人口作为分母,我最开始用的是题目给出的80million,后来发现不行,精度对不上,就说明必须用SQL语句找出精确的德国人口了,感觉最后的写出公式特别复杂。。
8,10两题还是没有写出来,总觉得10快要接近了,可是就是差了点的感觉。
8还是不太理解的感觉。。我一直是觉得要按照名字排序的,觉得会用到order by,可是这里用了name<=all name,不太懂。
Select continent,name
from world as x
where name <= all
(select name
from world as y
where y.continent=x.continent);
10看了答案后找到了自己的问题,因为后面的子句中没有去掉和自己比较的情况,自己的三倍是不可能>=自己的。
#10题原想法
SELECT name,continent
FROM world s1
WHERE population/3 >= all
(SELECT population
FROM world s2
WHERE s1.continent=s2.continent)
#改正后
SELECT name,continent
FROM world s1
WHERE population/3 >= all
(SELECT population
FROM world s2
WHERE s1.continent=s2.continent
and s1.name <> s2.name)
所有习题代码
1.
SELECT name
FROM world
WHERE population >
(SELECT population
FROM world
WHERE name='Russia')
2.
SELECT name
FROM world
WHERE gdp/population >
(SELECT gdp/population
FROM world
WHERE name='United Kingdom') and continent = 'Europe'
3.
SELECT name,continent
FROM world
WHERE continent in
(SELECT continent
FROM world
where continent in ('Argentina','Australia')
or name in ('Argentina','Australia'))
order by name
4.
SELECT name,population
FROM world
WHERE 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 world
where
continent = 'Europe'
6.
select name
from world
where gdp > (select max(gdp)
from world
where continent = 'Europe')
7.
SELECT continent,name,area
FROM world s1
WHERE area >= ALL
(SELECT area
FROM world s2
WHERE s1.continent=s2.continent)
8.
Select continent,name
from world as x
where name <= all
(select name
from world as y
where y.continent=x.continent);
9.
SELECT name,continent,population
FROM world s1
WHERE 25000000 >= all
(SELECT population
FROM world s2
WHERE s1.continent=s2.continent)
10.
select name, continent
from world as x
where population > all
(select 3*population
from world as y
where y.continent=x.continent and x.name <> y.name);
再单独再把8,10的图片发一下吧。
![4f3c9d6185cd2a27208093cc216353bc.png](https://i-blog.csdnimg.cn/blog_migrate/5c9413683e438075a4efd9fbe9178da6.jpeg)
![545b24690f07acfc06030dbfe75d088c.png](https://i-blog.csdnimg.cn/blog_migrate/4321eecfaf3fbcd69b05c408d4e08484.jpeg)