扫码加入数据分析学习群

##12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select distinct stu.* from stu join sc on stu.s_id=sc.s_id where c_id

in (select sc.c_id from stu join sc on stu.s_id=sc.s_id where stu.s_id='01')

and stu.s_id !='01';

##13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select group_concat(sc.c_id) from stu join sc on stu.s_id=sc.s_id where stu.s_id='01';

select stu.*,group_concat(sc.c_id) from stu join sc on stu.s_id=sc.s_id group by stu.s_id having group_concat(sc.c_id)

= (select group_concat(sc.c_id) from stu join sc on stu.s_id=sc.s_id where stu.s_id='01')

and stu.s_id !='01';

##14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select * from co

left join te on te.t_id=co.t_id

left join sc on sc.c_id=co.c_id

left join stu on stu.s_id=sc.s_id

where t_name='张三';

select distinct(s_name) from stu join sc on sc.s_id=stu.s_id where sc.c_id !=

(select distinct(sc.c_id) from co join te on te.t_id=co.t_id join sc on sc.c_id=co.c_id join stu on stu.s_id=sc.s_id where t_name='张三');

##15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select stu.s_id,s_name ,sum(score<60) 不及格门数,avg(score) 平均成绩

from stu left join sc on stu.s_id=sc.s_id group by stu.s_id having sum(score<60)>=2;

##16、检索"01"课程分数小于60，按分数降序排列的学生信息

select stu.*from stu left join sc on stu.s_id=sc.s_id where c_id='01' and score<60 order by score desc;

##17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select stu.s_id,s_name,(sum(c_id='01')*score) '01',(sum(c_id='02')*score) '02',(sum(c_id='03')*score) '03',

sum(score)/(select count(c_id) from co) 平均成绩

from stu left join sc on stu.s_id=sc.s_id group by stu.s_id order by 平均成绩 desc;

##18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name， 最高分，最低分，平均分，及格率，中等率，优良率，优秀率

## 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select co.c_id,c_name,max(score) 最高分,min(score) 最低分,avg(score) 平均分,

sum(score>=60)/(count(s_id)) 及格率,sum(score>=70 and score<80)/(count(s_id)) 中等率,

sum(score>=80 and score<90)/(count(s_id)) 优良率,sum(score>=90 )/(count(s_id)) 优秀率

from sc join co on sc.c_id=co.c_id group by c_id;

##19、按各科成绩进行排序，并显示排名

select *,rank() over(partition by c_id order by score desc) 排名 from sc ;

##20、查询学生的总成绩并进行排名

select s_id,sum(score), rank() over(order by sum(score) desc) 排名 from sc group by s_id order by sum(score) desc;

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

select distinct t_id,co.c_id, avg(score) over(partition by co.c_id ) 平均分 from sc join co on sc.c_id=co.c_id order by 平均分 desc;