常见的sql面试题.docxVIP

学生表 :Student (S1,Sname,Sage,Ssex) S1：学号； Sname：学生姓名； Sage：学生年龄； Ssex：学生性别课程表 : Course(C1,Cname,T1)C1,课程编号；Cname：课程名字；T1：教师编号成绩表: SC(S1,C1,score)S1：学号；C1,课程编号；score：成绩教师表 :Teacher(T,#Tname)T1：教师编号； Tname：教师名字问题：1、查询“001”课程比“002”课程成绩高的所有学生的学号； select a.S1 from (select S1,score from SC where C1=001) a,(select S1,score from SC where C1=002) b where a.scoreb.score and a.S1=b.S1;2、查询平均成绩大于60分的同学的学号和平均成绩； select S1,avg(score) from sc group by S1 having avg(score) 60;3、查询所有同学的学号、姓名、选课数、总成绩； select Student.S1,Student.Sname,count(SC.C1),sum(score) from Student left Outer join SC on Student.S1=SC.S1 group by Student.S1,Sname4、查询姓“李”的老师的个数； select count(distinct(Tname)) from Teacher where Tname like 李%;5、查询没学过“叶平”老师课的同学的学号、姓名； select Student.S1,Student.Sname from Student where S1 not in (select distinct( SC.S1) from SC,Course,Teacher where SC.C1=Course.C1 and Teacher.T1=Course.T1 and Teacher.Tname=叶平);6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select Student.S1,Student.Sname from Student,SC where Student.S1=SC.S1 and SC.C1=001and exists( Select * from SC as SC_2 where SC_2.S1=SC.S1 and SC_2.C1=002);7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； select S1,Sname from Student where S1 in (select S1 from SC ,Course ,Teacher where SC.C1=Course.C1 and Teacher.T1=Course.T1 and Teacher.Tname=叶平 group by S1 having count(SC.C1)=(select count(C1) from Course,Teacher where Teacher.T1=Course.T1 and Tname=叶平));8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； Select S1,Sname from (select Student.S1,Student.Sname,score ,(select score from SC SC_2 where SC_2.S1=Student.S1 and SC_2.C1=002) score2 from Student,SC where Student.S1=SC.S1 and C1=001) S_2 where score2 score;9、查询所有课程成绩小于60分的同学的学号、姓名； select S1,Sname from Student where S1 not in (select Student.S1 from Student,SC where S.S1=SC.S1 and score60);10、查询没有学全所有课的同学的学号、姓名； select Student.S1,Student.Sname from Student,SC where Student.S1=SC.S1 group by Student.S1,Student.Sname having count(C1) (select count(C1) from Course);11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名； select S1