SQL语句库联系

Studentdent(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
1、查询“001”课程比“002”课程成绩高的所有学生的学号；

Select a.s# from (select s#,score from sc where c#=‘001’) a join (select s#,socre from sc where c#=‘002’) b on a.s# = b.s# where a.score > b.score;

2、查询平均成绩大于60分的同学的学号和平均成绩；

Select S#,avg(avg) from sc group by S# having avg(score)>60;

3、查询所有同学的学号、姓名、选课数、总成绩；

Select Student.S#,Student.sname,count(C#),sum(score) from Studentdent Student join sc s on Student.S# = s.S#

4、查询姓“李”的老师的个数；
select count(T#) from teacher where tname like ‘李%’;
5、查询没学过“叶平”老师课的同学的学号、姓名；
select s#,sname from student where s# not in (select distinct s# from sc where c# in (select c# from course where T# = (select T# from teacher where tname=“叶平”)));
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select s#,sname from student where s# in ((select s# from sc c# =‘001’) intersect (select s# from sc c# =‘002’));
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select s# from (select count(c#) num from sc where c# in (select c# from course where T# = (select T# from teacher where tname=“叶平”)) group by s#) where num=(select count(c#) from course where T# = (select T# from teacher where tname=“叶平”));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
Select a.s# from (select s#,score from sc where c#=‘002’) a join (select s#,socre from sc where c#=‘001’) b on a.s# = b.s# where a.score < b.score;

9、查询所有课程成绩小于60分的同学的学号、姓名；
select distinct s# from sc where s# not in (select distinct s# from sc where score>=60);
10、查询没有学全所有课的同学的学号、姓名；
select s#,count(c#) from sc group by s# having count(c#)<(select count(c#) from course);