50SQL语句题目及答案.docxVIP

PAGE PAGE 1欢。迎下载 精品文档 精品文档 /* Student(S#,Sname,Sage,Ssex)学生表 Course(C#,Cname,T#) 课程表SC(S#,C#,score) 成绩表Teacher(T#,Tname) 教师表 */ --1、查询“001”课程比“002”课程成绩高的所有学生的学号；select s1.s# from SC s1,SC s2where s1.s#=s2.s# --1、查询“001”课程比“002”课程成绩高的所有学生的学号； select s1.s# from SC s1,SC s2 where s1.s#=s2.s# and s1.c#=001 and s2.c#=002 and s1.scores2.score; --2、查询平均成绩大于60分的同学的学号和平均成绩； select s.s#,avg(s.score) from SC s group by s.s# having avg(s.score)60; --3、查询所有同学的学号、姓名、选课数、总成绩； select d.s#,max(d.sname),count(distinct s.c#),sum(s.score) from Student d,SC s where d.s#=s.s# group by d.s#; --4、查询姓“李”的老师的个数； select count(0) from Teacher t where t.tname like 李%; --5、查询没学过“叶平”老师课的同学的学号、姓名； select d.s#,d.sname from Student d where not exists ( select distinct(s.s#) from SC s,Teacher t,Course c where t.t#=c.t# and s.c#=c.c# and t.tname=叶平 and d.s#=s.s#); --6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select d.s#, d.sname from Student d, SC s where s.s# = d.s# and s.c# = 001 and exists (select s1.s# from SC s1 where s1.s# = s.s# and s1.c# = 002); --7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； select s.s#,max(d.sname) from SC s,Student d,Course c,Teacher t where s.s#=d.s# and s.c#=c.c# and c.t#=t.t# and t.tname=叶平 group by s.s# having count(distinct s.c#) = ( select count(0) from Course c,Teacher t where c.t#=t.t# and t.tname=叶平); --8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； select d.s#,d.sname from SC s1,SC s2,Student d where s1.s#=s2.s# and s1.s#=d.s# and s1.c#=002and s2.c#=001 and s1.scores2.score; --9、查询所有课程成绩小于60分的同学的学号、姓名； select s.s#,max(d.sname) from Student d,SC s where d.s#=s.s# and s.score60 group by s.s# having count(0) = (select count(0) from SC s1 where s.s#=s1.s#); --10、查询没有学全所有课的同学的学号、姓名； select s.s#,max(d.sname) from Student d,SC s where d.s#=s.s# group by s.s# 精品文档 having count(distinct s.c#) (select count(0) from Course); --11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；select distinct --11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名； select distinct s.s#,d.sname from Student d,SC s where d.s#=s.s# and s.s#1001 a