【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`)
);
shadowed插件#成绩表
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' , '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分的学⽣的学号和平均成绩
mysql面试题型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
int转string c++语言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分的学⽣的学号、姓名
#这个暂时不对
水平线html代码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
amaze ui有那些优缺点
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='张三')
java 教程 schoolorder by s_score desc
limit 0,1
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论