实验4 sql的复杂查询_4.SQL复杂查询

#创建视图
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'
);

#标量子查询
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;

#关联子查询，比较难理解
select 学号,课程号,成绩
from score as s1
where 成绩 >
(select avg(成绩)
from score as s2
where s1.课程号 = s2.课程号
group by 课程号
);

明显能感觉到第四关开始难度上升了很多，无论是课程还是习题。所以我都有开始写注释了，怕以后忘了看不懂，知乎live的图片我也做成了PPT和PDF两种格式，以后看起来方便一些。

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的图片发一下吧。