【mysql 练习题】查询和“01”号同学所学课程完全相同的其他同学的学号

先说一件有点难过的事情，我还是放弃了想做数据分析师~投了好多好多好多的简历，只有几个面试，可能真的和我长时间的空白期有关吧，哪怕很认真准备了很久也没办法得到青睐。把mysql的50题刷完~看一看剩下的投递里会不会有面试，这段很努力很努力的时光就暂时封存起来吧，可能未来我会在知识产权领域大有作为呢！

如果有需要数据分析相关资源的小伙伴可以找我~python，mysql，power bi，商业数据分析我这里都有学习的资料。

插入表： 

#学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
#课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
#教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
#成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
#插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
#课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

#教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

#成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

这一个想了很久~还是借鉴了其他人的答案

查询和“01”号同学所学课程完全相同的其他同学的学号

查询‘01’同学所学的课程

select c_id from score where s_id='01'

查询学习了‘01’同学没学过课程的同学

select s_id from score
where c_id not in(select c_id from score where s_id='01')

用not in查询和‘01’同学学过课程相同，且课程数量相等的人

select distinct s_id from score where s_id not in
(select s_id from score where c_id not in (select c_id from score where s_id='01'))
group by s_id
having count(c_id)=(select count(c_id) from score where s_id='01') and s_id<>'01'

用student表查学生信息

select * from student 
where s_id in(
select distinct s_id from score where s_id not in
(select s_id from score where c_id not in (select c_id from score where s_id='01'))
group by s_id
having count(c_id)=(select count(c_id) from score where s_id='01') and s_id<>'01')

其他的练习题：

#1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）
select Student.*,Score.*
from Student join score on student.s_id=score.s_id 
where student.s_id in
(select s1.s_id from
(select s_id,s_score from Score where c_id='01') s1,(select s_id,s_score from Score where c_id='02') s2
where s1.s_id=s2.s_id and s1.s_score>s2.s_score);

#2.查询平均成绩大于60分的学生的学号和平均成绩
select student.s_id,avg(s_score)
from student,score
where student.s_id=score.s_id
group by student.s_id
having avg(s_score)>60;
#只需要学生表的话 可以用in
select student.*
from student
where student.s_id in
(select s_id from score group by s_id having avg(s_score)>60);

#查询没学过“张三”老师课的学生的学号、姓名（重点）
select student.s_id, student.s_name
from student where s_id not in
(select s_id from course,teacher,score where course.t_id=teacher.t_id and course.c_id=score.c_id and teacher.t_name='张三');

#查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）
select student.s_id,student.s_name
from student,course,teacher,score 
where course.t_id=teacher.t_id and course.c_id=score.c_id and teacher.t_name='张三' and student.s_id=score.s_id
group by student.s_id
having count(s_score)=(select count(c.c_id) from course c, teacher t where c.t_id=t.t_id and t.t_name='张三')

#查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
select a.id, a.name from
(select student.s_id id, student.s_name name
from student,score
where student.s_id=score.s_id and score.c_id='01') a
inner join 
(select student.s_id id, student.s_name name
from student,score
where student.s_id=score.s_id and score.c_id='02') b
on a.id=b.id;
#可以用student作为主表 where student.s_id in 
select student.s_id,student.s_name
from student
where student.s_id in
(select a.id from (select score.s_id id from score where score.c_id='01') a inner join (select score.s_id id from score where score.c_id='02') b
where a.id=b.id );

#查询所有课程成绩小于60分的学生的学号、姓名
#这个暂时不对！！！
select student.s_id from student where s_id in
(select score.s_id from score, (select s_id,count(c_id) num from score group by s_id) cc
where s_score<60 and score.s_id=cc.s_id
group by s_id
having count(score.c_id)=cc.num);

#查询没有学全所有课的学生的学号、姓名(
select student.s_id,student.s_name
from student,score
where student.s_id=score.s_id
group by student.s_id
having count(score.c_id)<
(select count(distinct c_id) from course);

#查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
select distinct student.s_id,s_name from student,score
where student.s_id=score.s_id
and score.c_id in
(select c_id from score where s_id='01')
and  student.s_id!='01';

#查询和“01”号同学所学课程完全相同的其他同学的学号
select * from student 
where s_id in(
select distinct s_id from score where s_id not in
(select s_id from score where c_id not in (select c_id from score where s_id='01'))
group by s_id
having count(c_id)=(select count(c_id) from score where s_id='01') and s_id<>'01');

#查询没学过"张三"老师讲授的任一门课程的学生姓名
select s_name from student 
where s_id not in(
select s_id from score where c_id in(
select c_id from course,teacher
where course.t_id=teacher.t_id and t_name='张三'));

#查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.s_id,student.s_name,avg(s_score)
from student, score
where student.s_id=score.s_id and s_score<60
group by student.s_id
having count(c_id)>=2;
# case when then else end
select student.s_id,student.s_name,avg(s_score)
from student, score
where student.s_id=score.s_id 
group by student.s_id
having sum(case when s_score<60 then 1 else 0 end)>=2;

#按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select student.s_id,
sum(case when c_id='01' then s_score else NULL end ) cid1,
sum(case when c_id='02' then s_score else NULL end ) cid2,
sum(case when c_id='03' then s_score else NULL end ) cid3,
avg(s_score) average
from student,score
where student.s_id=score.s_id
group by student.s_id
order by average desc;

#查询各科成绩最高分、最低分和平均分：
#以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
#注意如果case when用于count则else必须是NULL不能是0
select score.c_id,course.c_name,max(s_score),min(s_score),avg(s_score),
sum(case when s_score>=60 then 1 else 0 end)/count(s_score) '及格率',
sum(case when s_score>=90 then 1 else 0 end)/count(s_score) '优秀率'
from score,course where score.c_id=course.c_id
group by score.c_id;

#按各科成绩进行排序，并显示排名
#row_number 不重复123, rank 存在相同排名重复113, dense_rank 相同排名重复 112 
select s_id, s_score, row_number() over(partition by c_id order by s_score desc) rank_cid
from score;

#查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
#注意where中不能用select中定义的列 因为顺序是 from-where-select
select s_id,s_score from
(select s_id, s_score, row_number() over(partition by c_id order by s_score desc) rank_cid
from score) cc
where rank_cid in (2,3) ;

#查询各科成绩前三名的记录（不考虑成绩并列情况）
select c_id,
sum(case when rank_cid=1 then s_score else null end) 'first',
sum(case when rank_cid=2 then s_score else null end) '2nd',
sum(case when rank_cid=3 then s_score else null end) '3rd'
from (select c_id,s_id,s_score, row_number() over (partition by c_id order by s_score desc) rank_cid from score) cc
group by c_id;

#查询不同老师所教不同课程平均分从高到低显示
select course.t_id,course.c_id,avg(s_score) average
from course,teacher,score
where course.t_id=teacher.t_id and course.c_id=score.c_id
group by course.t_id,course.c_id
order by average desc;

#查询出只有两门课程的全部学生的学号和姓名
select student.s_id,student.s_name
from student
where student.s_id in
(select score.s_id from score group by score.s_id
having count(c_id)=2)

#查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select distinct student.s_id,student.s_name,c_id,score.s_score
from student inner join score on student.s_id=score.s_id
where student.s_id not in
(select s_id from score where s_score<70)

#查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩
select student.s_id, s_name, score.s_score
from student inner join score on student.s_id=score.s_id
where c_id in(select course.c_id from course inner join teacher
              on course.t_id=teacher.t_id where t_name='张三')
order by s_score desc
limit 0,1

#查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）
select distinct s1.s_id,s1.c_id,s1.s_score
from score s1 join score s2
on s1.s_id=s2.s_id 
where s1.c_id<>s2.c_id  and s1.s_score=s2.s_score
order by c_id

#查询每门课程成绩最好的前两名
select s_id, c_id from(
select s_id,c_id,s_score,row_number() over(partition by c_id order by s_score desc) rn
from score) cc
where cc.rn in (1,2);

# 查询选修了全部课程的学生信息
select student.* from student
where s_id in
(select s_id from score group by s_id having count(c_id)=(select count(distinct c_id) from course));

#查询各学生的年龄
#按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一（这个我抄的！！！）
SELECT s_name,s_birth,
(DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(s_birth,'%Y') -
(CASE WHEN DATE_FORMAT(NOW(),'%m%d') > DATE_FORMAT(s_birth,'%m%d') 
THEN 0 ELSE 1 END)
) AS age
FROM student;

#本月过生日
SELECT * FROM student
where MONTH(s_birth)=MONTH(DATE(now()))