数据多重完整嵌套查询语句
Student(SNO,Sname,Sage,Ssex) 学生表Course(CNO,Cname,TNO) 课程表
SC(SNO,CNO,score) 成绩表
Teacher(TNO,Tname) 教师表
问题:
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
Select a.SNO from (select Sno,score from SC where CNO='001') a,
(select Sno,scorefrom SC where CNO='002') b
Where a.score>b.score and a.Sno=b.Sno;
2、查询平均成绩大于60分的同学的学号和平均成绩;
Select SNO,avg(score)
From sc
group by SNO having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩;
Select Student.SNO, Student.Sname, count(SC.CNO),sum(score)
From Student left Outer join SC on Student.SNO=SC.SNO
group by Student.SNO,Sname
4、查询姓“李”的老师的个数;
Select count(distinct(Tname))
From Teacher
Where Tname like '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名;
Select Student.SNO,Student.Sname
From Student
Where SNO not in (select distinct( SC.SNO) from SC,Course,Teacher where SC.CNO=Course.CNO and Teacher.TNO=Course.TNO and Teacher.Tname='叶平');
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
Select Student.SNO,Student.Sname from Student,SC where Student.SNO=SC.SNO and SC.CNO='001'and exists( Select * from SC as SC_2 where SC_2.SNO=SC.SNO and SC_2.CNO='002');
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
selectSNO,Sname
from Student
where SNO in (select SNO from SC ,Course ,Teacher where SC.CNO=Course.CNO and Teacher.TNO=Course.TNO and Teacher.Tname='叶平' group by SNO having count(SC.CNO)=(select count(CNO) from Course,Teacher where Teacher.TNO=Course.TNO and Tname='叶平'));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
Select SNO,Sname from (select Student.SNO,Student.Sname,score ,(select score from SC SC_2 where SC_2.SNO=Student.SNO and SC_2.CNO='002') score2
From Student,SC where Student.SNO=SC.SNO and CNO='001') S_2 where score2 <score;
9、查询所有课程成绩小于60分的同学的学号、姓名;
selectSNO,Sname
from Student
where SNO not in (select Student.SNO from Student,SC where S.SNO=SC.SNO and score>60);
10、查询没有学全所有课的同学的学号、姓名;
Select Student.SNO,Student.Sname
From Student,SC
Where Student.SNO=SC.SNO group by Student.SNO,Student.Sname having count(CNO) <(select count(CNO) from Course);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
Select SNO,Sname from Student,SC