数据库表如下：
teacher：老师表
course：课程表
student：学生表
class：班级表
sc：成绩表
一、根据上面5张表写sql语句
1. 查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
select student.*,t1.score 课程1分数,t2.score 课程2分数 from studentinner join (select * from sc where cid=1) t1 on student.sid=t1.sidinner join (select * from sc where cid=2) t2 on t1.sid=t2.sidwhere t1.score>t2.score;2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select student.sid,sname,avg(score) from studentleft join sc on student.sid=sc.sidgroup by student.sidhaving avg(score)>=60;select student.sid,sname,avg(score) from sc,studentwhere student.sid=sc.sidgroup by student.sidhaving avg(score)>=60;3. 查询在 SC 表存在成绩的学生信息
select * from student where sid in(select sid from sc);select distinct student.* from studentinner join sc on student.sid=sc.sid ;4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select student.sid,sname,count(cid),sum(score) from studentleft join sc on student.sid=sc.sidgroup by student.sid;5. 查询「李」姓老师的数量
select count(*) from teacher where tname like '李%';6. 查询学过「张三」老师授课的同学的信息
select * from student where student.sid in(select sid from sc where sc.cid =(select cid from course where course.tid=(select tid from teacher where tname='张三')));7. 查询没有学全所有课程的同学的信息
select student.*,count(cid) from studentleft join sc on student.sid=sc.sidgroup by student.sidhaving count(cid)<(select count(*) from course);8. 查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息
select distinct student.* from studentleft join sc on student.sid=sc.sidwhere cid in(select cid from sc where sid=1 and student.sid != 1);9. 查询和” 01 “号的同学学习的课程完全相同的其他同学的
注意：一模一样 1.范围相同 2.个数相同
select student.* from studentinner join sc on student.sid=sc.sidwhere student.sid not in -- 范围（逆向）(select sid from sc where cid not in(select cid from sc where sid=1))and student.sid != 1group by student.sidhaving count(cid)=(select count(*) from sc where sid=1); -- 个数相同10. 查询没学过”张三”老师讲授的任一门课程的学生姓名
select sname from student where student.sid not in(select sid from sc where sc.cid =(select cid from course where course.tid =(select tid from teacher where tname='张三')))11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.sid,sname,avg(score) from sc,studentwhere score<60 and sc.sid=student.sidgroup by student.sidhaving count(cid)>=2;12. 检索” 01 “课程分数小于 60，按分数降序排列的学生信息
select * from student,scwhere student.sid=sc.sid and cid=1 and score<60order by score desc13. 查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）
select sc.cid,cname,max(score),min(score),avg(score),count(case when score >=60 then score end)/count(*) * 100 及格率,count(case when score >=70 and score <80 then score end)/count(*) * 100 中等率,count(case when score >=80 and score <90 then score end)/count(*) * 100 优良率,count(case when score >90 then score end)/count(*) * 100 优秀率from course,scwhere course.cid=sc.cidgroup by course.cid
-- 各科成绩最高分、最低分和平均分
select sc.cid,cname,max(score),min(score),avg(score) from course,scwhere course.cid=sc.cidgroup by course.cid
-- 及格率，中等率，优良率，优秀率
select cid,count(case when score >=60 then score end)/count(*) * 100,count(case when score >=70 and score <80 then score end)/count(*) * 100,count(case when score >=80 and score <90 then score end)/count(*) * 100,count(case when score >90 then score end)/count(*) * 100from scgroup by cid
