第四次作业

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

select * from student where SId not in (select c.SId from course a join teacher b on a.TId=b.TId join sc c on a.CId=c.CId where b.Tname='张三'); 

1. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

Select sid,sname,score from student inner join sc on sc.sid=student.sid  where score>60;

1. 查询在 SC 表存在成绩的学生信息

Select * from sc where score is not null;

1. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

Select group_concat(distinct sc.sid) as sid,group_concat(distinct sname) as sname,count(cid),sum(score) from student inner join sc on sc.sid=student.sid  group by sc.sid;4.1 查有成绩的学生信息

1. 查询「李」姓老师的数量

Select count(*) from teacher where tname like “李%”；

1. 查询学过「张三」老师授课的同学的信息

Select student.sid,sname,tname from student inner join sc on sc.sid=student.sid inner join course on course.cid=sc.cid inner join teacher on teacher.tid=course.tid where tname="张三";

1. 查询没有学全所有课程的同学的信息

Select group_concat(distinct sc.sid) as sid,group_concat(distinct sname) as sname,count(cid),sum(score) from student inner join sc on sc.sid=student.sid  group by sc.sid having  count(cid)<count(cid);

1. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信

Select distinct student.sid,sname from student inner join sc on sc.sid=student.sid  inner join course on course.cid=sc.cid where course.cname in ( select course.cname from course inner join sc on sc.cid=course.cid where sc.sid=01);

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

错：Select sname  from student inner join sc on sc.sid=student.sid inner join course on course.cid=sc.cid inner join teacher on teacher.tid=course.tid where course.cid != (select course.cid from course inner join teacher on teacher.tid=course.tid where tname=”张三”);

select * from student where SId not in (select distinct a.SId from sc a join (select course.cid from course inner join teacher on teacher.tid=course.tid where tname="张三") b on a.CId=b.CId);

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

Select group_concat(distinct sname) as name,group_concat(distinct sc.sid) as id ,count(cid),avg(score) from student inner join sc on sc.sid=student.sid where score<60 group by sc.sid  having count(cid) > 2;检索" 01 "课程分数小于 60，按分数降序排列的学生信息

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

Select group_concat(score),avg(score) from sc group by sid order by avg(score) desc;