SQL 2

汇总分析

查询学生的总成绩并进行排名：

select 学号,sum(成绩) as 总成绩
from score
group by 学号
order by 总成绩

查询平均成绩大于60分的学生的学号和平均成绩

select 学号,avg(成绩) as 平均成绩
from score
group by 学号
having 平均成绩 >60 

 3.复杂查询

查询所有课程成绩小于60分学生的学号、姓名

我的答案：

select 学号,姓名
from student
where 学号 not in 
(select 学号
from score
where 成绩>=60
)

 原答案：

select 学号,姓名
from student
where  学号 in (
select 学号 
from score
where 成绩 < 60
);

感觉这个题说的不明白，我的理解为  学生的所有的课程的成绩都要小于60才被选出来

 

查询没有学全所有课的学生的学号、姓名：

select 学号,姓名
from student
where 学号 not in (
select 学号
from score
group by 学号
having count(学号) = (select count(distinct 课程号) from score)
)

原答案：忽略了一个根本没有选课的学生

select 学号,姓名
from student
where 学号 in(
select 学号 
from score
group by 学号
having count(课程号) < (select count(课程号) from course)
);

 

查询出只选修了两门课程的全部学生的学号和姓名

select 学号,姓名
from student
where 学号 in (
select 学号
from score
group by 学号
having count(课程号) = 2
)

 

4.多表查询

查询所有学生的学号、姓名、选课数、总成绩

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号=b.学号
group by a.学号

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select a.学号,a.姓名,avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号=b.学号
group by a.学号
having 平均成绩>85

查询学生的选课情况：学号，姓名，课程号，课程名称

select a.学号,a.姓名,c.课程号,c.课程名称
from student as a left join score as b on a.学号=b.学号
inner join course as c on b.课程号=c.课程号

查询出每门课程的及格人数和不及格人数

select 课程号,sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,
			  sum(case when 成绩<60 then 1 else 0 end) as 不及格人数
from score
group by 课程号

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

select a.课程号,b.课程名称,
sum(case when 成绩 between 85 and 100 then 1 else 0 end) as '一人数',
sum(case when 成绩 between 70 and 84 then 1 else 0 end) as '二人数',
sum(case when 成绩 between 60 and 69 then 1 else 0 end) as '三人数',
sum(case when 成绩 <60 then 1 else 0 end) as '四人数'
from score as a right join course as b on a.课程号=b.课程号
group by a.课程号,b.课程名称

查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名

select a.学号,a.姓名
from student as a join score as b on a.学号=b.学号
where b.课程号='0003' and 成绩>80

 关于行列互换的问题：

select 学号,
max(case 课程号 when '0001' then 成绩 else 0 end) as '课程0001',
max(case 课程号 when '0002' then 成绩 else 0 end) as '课程0002',
max(case 课程号 when '0003' then 成绩 else 0 end) as '课程0003'
from score
group by 学号