SQL刷题(五)---SQL44题

1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

select a.学号 from score a join score b on a.学号 = b.学号 and a.成绩 > b.成绩 
where a.课程号 = '01' and b.课程号 = '02';

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

select student.学号,avg(score.成绩) as 平均成绩 from student 
inner join score 
on student.学号 = score.学号
group by student.学号
having avg(score.成绩) > 60;

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

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

4.查询姓张老师的个数

select count(teacher.教师姓名) as 教师数 
from teacher
where 教师姓名 like '张%';

5..查询没学过“张三”老师课的学生的学号、姓名(重点)(不熟练) (暂时空起来)

select student.学号,student.姓名 from student 
where student.学号 not in 
(select student.学号 from student inner join score 
on student.学号 = score.学号 
inner join course
on score.课程号 = course.课程号 
inner join teacher
on course.教师号 = teacher.教师号
where 教师姓名 = '张三')

该题的难点在于将子查询 student.学号 not in (---)
查找条件没有上过张三老师课的学生

6..查询学过“张三”老师课的学生的学号、姓名(重点)(不熟练)

select student.学号,student.姓名,teacher.教师姓名 from student 
inner join score 
on student.学号 = score.学号 
inner join course 
on score.课程号 = course.课程号 
inner join teacher 
on course.教师号 = teacher.教师号
where teacher.教师姓名 = '张三';

7.查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(不熟练)

select student.学号,student.姓名 
from student 
where student.学号 in
(select score.学号 from score where score.课程号 = '01')
and student.学号 in 
(select score.学号 from score where score.课程号 = '02');

判断条件：需要对student.学号 将其加入到后面的子查询中.

8、查询课程编号为“02”的总成绩

select score.课程号,sum(score.成绩) as 总成绩
from score
where score.课程号 = '02';

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

select student.学号,student.姓名,score.成绩 from student 
inner join score
on student.学号 = score.学号 
where score.成绩 < 60;

10.查询没有学全所有课的学生的学号、姓名(不熟练)

写法一：
select student.学号,student.姓名 from student 
inner join score
on student.学号 = score.学号 
group by score.学号
having count(score.学号) < (select count(course.课程号) from course);

写法2：
select student.学号,student.姓名 from student 
inner join score
on student.学号 = score.学号 
group by score.学号
having count(score.学号) < 3；

11查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名(不熟练)---（该题不懂要记下来）

select distinct student.学号,student.姓名 from student 
inner join score 
on student.学号 = score.学号
where score.课程号 in 
(select score.课程号 from score where score.课程号 = '01')
and student.学号 <> '01';

score表中的课程号为'01'，学生号 != '01'

12.查询和“01”号同学所学课程完全相同的其他同学的学号 (不熟练)

select score.学号 
from score
where score.课程号 in 
(select score.课程号 from score where score.学号 = '01')
and score.学号 <> '01'
group by score.学号 
having count(score.课程号) = (select count(score.课程号) from score where score.学号 = '01');

将学号为01的学生的学生课程号全部筛选出来，同时学号不能为01

13.把“SCORE”表中“张三”老师教的课的成绩都更改为此课程的平均成绩(跳过，无法实现)

update score as a join
(select avg(score.成绩) as t,score.课程号 from score
inner join course
on score.课程号 = course.课程号 
inner join teacher
on course.教师号 = teacher.教师号
where teacher.教师姓名 = '张三'
group by course.课程号) as b 
on a.课程号 = b.课程号
set a.成绩 = b.t;

14、查询和“02”号的同学学习的课程完全相同的其他同学学号和姓名(同12题）

select score.学号,student.姓名 from score 
inner join student
on score.学号 = student.学号
where score.课程号 in 
(select score.课程号 from score where score.学号 = '02')
and 
score.学号 <> '02'
group by score.学号
having count(score.课程号) = (select count(score.课程号) from score where score.学号 = '02');

15.删除学习“张三”老师课的SC表记录

delete from score
where score.课程号 in 
(select course.课程号 from course inner join teacher on course.教师号 = teacher.教师号
where teacher.教师姓名 = '张三')

添加了一个子查询

17、按平均成绩从高到低显示所有学生的“数据库”（c_id='04'）、“企业管理”（c_id='01'）、“英语”（c_id='06'）三门的课程成绩，按如下形式显示：学生ID，数据库，企业管理，英语，有效课程数，有效平均分(无法实现暂时跳过)

1. 查询各科成绩最高和最低的分：以如下的形式显示：课程ID，最高分，最低分

select score.课程号 as 课程ID,max(score.成绩) as 最高分,min(score.成绩) as 最低分 
from score
group by score.课程号;

明白大致的思路，之前已经把2给删了

19.按各科平均成绩从低到高和及格率的百分数从高到低排列，以如下形式显示：课程号,课程名,平均成绩,及格百分数

select score.课程号 as 课程号,course.课程名称 as 课程名,avg(score.成绩) as 平均成绩，
from score
inner join course
on score.课程号 = course.课程号
group by 课程名;

及格率那一块不是特别会处理。暂时放下

20(题不用做)查询如下课程平均成绩和及格率的百分数（用1行显示），其中企业管理为001，马克思为002，UML为003，数据库为004

21.查询不同老师所教不同课程平均分从高到低显示

select teacher.教师号,teacher.教师姓名,course.课程名称,avg(score.成绩) as 平均成绩
from score inner join course
on score.课程号 = course.课程号
inner join teacher
on course.教师号 = teacher.教师号
group by 教师姓名
order by 平均成绩 desc;

22(有问题不用做)查询如下课程成绩第3名到第6名的学生成绩单，其中企业管理为001，马克思为002，UML为003，数据库为004，以如下形式显示：

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

select score.课程号 as 课程ID, course.课程名称 as 课程名称,
sum(case when score.成绩 between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when score.成绩 between 70 and 85 then 1 else 0 end) as '[70-85]',
sum(case when score.成绩 between 60 and 70 then 1 else 0 end) as '[60-70]',
sum(case when score.成绩 < 60 then 1 else 0 end) as '[<60]'
from score inner join course 
on score.课程号 = course.课程号
group by course.课程号,course.课程名称;

24.查询学生平均成绩及其名次(该题在排名上无法实现)

select student.学号,student.姓名, avg(score.成绩) as 平均成绩
from score inner join student
on score.学号 = student.学号
group by score.学号
order by 平均成绩 desc;

25.查询各科成绩前三名的记录（不考虑成绩并列情况）--(不会)

26.查询每门课程被选修的学生数

select course.课程号,course.课程名称,count(score.课程号) as 选课人数 from score
inner join course 
on score.课程号 = course.课程号
group by score.课程号;

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

select score.学号,student.姓名,count(score.课程号) as 选课数 from score 
inner join student
on student.学号 = score.学号
group by student.姓名
having count(score.课程号) = 2;

28.查询男生、女生人数

select student.性别,count(student.性别) as 男女生人数 
from student
group by student.性别;

29.查询名字中含有“风”字的学生信息

select student.姓名 from student
where student.姓名 like '%风';

30.查询同名同姓学生名单并统计同名人数

select * from student 
as a 
inner join student 
as b
on a.姓名 = b.姓名
where a.学号 <> b.学号;

31.1990年出生的学生名单

select * from student
where year(student.出生日期) = 1990;

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

select student.学号,student.姓名,avg(score.成绩) from student
inner join score
on student.学号 = score.学号 
group by student.学号
having avg(score.成绩) > 85;

33查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select score.课程号, avg(score.成绩) as 平均成绩 from score
group by score.课程号
order by 平均成绩,score.课程号 desc;

34查询课程名称为“数学”且分数低于60的学生姓名和分数

select student.姓名,score.成绩 from student 
inner join score
on student.学号 = score.学号
inner join course
on score.课程号 = course.课程号
where course.课程名称 = '数学'
and
score.成绩 < 60;

35查询所有学生的选课情况

select student.学号,student.姓名,course.课程名称 from student
inner join score
on student.学号 = score.学号
inner join course
on score.课程号 = course.课程号;

36查询任何一门课程成绩在70分以上的姓名、课程名称和分数

select student.姓名,course.课程名称,score.成绩 from student
inner join score
on student.学号 = score.学号 
inner join course
on score.课程号 = course.课程号
where score.成绩 > 70;

37查询不及格的课程并按课程号从大到小排列

select score.课程号,score.成绩 as 分数 from score
where score.成绩 < 60
order by score.课程号;

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

select student.姓名,student.学号 from student 
inner join score
on student.学号 = score.学号
where score.课程号 = '03'
and
score.成绩 > 80;

39 查询选了课程的学生人数

select score.学号, count(score.课程号) as 选课人数
from score
group by score.学号;

39写法二

select count( distinct score.学号) as 选课人数
from score;

40查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

select student.姓名,score.成绩 from student 
inner join score 
on student.学号 = score.学号
inner join course
on score.课程号 = course.课程号 
inner join teacher
on course.教师号 = teacher.教师号
where teacher.教师姓名 = '张三'
order by score.成绩 desc limit 1;

41查询各个课程及相应的选修人数

select score.课程号,course.课程名称,count(score.课程号) as 选课人数 
from score inner join course
on score.课程号 = course.课程号
group by score.课程号;

42查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select distinct a.学号,a.课程号,a.成绩 from score as a 
inner join score as b 
on a.学号 = b.学号 and a.课程号 != b.课程号 
where a.成绩 = b.成绩;

43查询每门课程成绩最好的前两名 (本题较难不会做)

44统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

select score.课程号,count(score.学号) as 选课人数 from score
group by score.课程号
having count(选课人数) > 5
order by 选课人数 desc,score.课程号;

45查询至少选修两门课程的学生学号

select score.学号
from score
group by score.学号
having count(score.课程号) > 2;

46.查询选修了全部课程的学生信息

select student.学号,student.姓名,student.性别,student.出生日期 
from student inner join score 
on student.学号 = score.学号
group by student.学号
having count(score.课程号) = (select count(distinct score.课程号) from score);

47查询没学过“张三”老师讲授的任一门课程的学生姓名

select student.姓名 from student
where student.学号 not in 
(select student.学号 from student 
inner join score 
on student.学号 = score.学号 
inner join course
on score.课程号 = course.课程号 
inner join teacher 
on course.教师号 = teacher.教师号 
where teacher.教师姓名 = '张三');

比较细致的写法是:

select student.姓名,teacher.教师姓名 from student inner join score on student.学号 = score.学号 
inner join course on score.课程号 = course.课程号 
inner join teacher   
on course.教师号 = teacher.教师号
where student.学号 not in 
(select student.学号 from student 
inner join score 
on student.学号 = score.学号 
inner join course
on score.课程号 = course.课程号 
inner join teacher 
on course.教师号 = teacher.教师号 
where teacher.教师姓名 = '张三');

48、查询两门以上不及格课程的同学的学号及其平均成绩

select score.学号,student.姓名,avg(score.成绩) from student
inner join score
on student.学号 = score.学号
where score.成绩 < 60
group by score.学号
having count(score.课程号) >= 2;

49.检索课程编号为“04”且分数小于60的学生学号，结果按按分数降序排列

select student.学号 from student inner join
score on student.学号 = score.学号
where score.课程号 = '04'
and 
score.成绩 < 60
order by score.成绩 desc;

50.删除学生编号为“02”的课程编号为“01”的成绩

delete from score 
where score.学号 = '02' 
and score.课程号 = '01';