4、数据多重完整嵌套查询语句

数据多重完整嵌套查询语句

 

 

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