SQL50题.docVIP

SQL50题 Student 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, select s#,score from SC where C# 002 b where a.score b.score and a.s# b.s#; 2、查询平均成绩大于60分的同学的学号和平均成绩； select S#,avg score from sc group by S# having avg score 60; 3、查询所有同学的学号、姓名、选课数、总成绩； select Student.S#,Student.Sname,count SC.C# ,sum score from Student left Outer join SC on Student.S# SC.S# group by Student.S#,Sname 4、查询姓“李”的老师的个数； select count distinct Tname from Teacher where Tname like 李%; 5、查询没学过“叶平”老师课的同学的学号、姓名； select Student.S#,Student.Sname from Student where S# not in select distinct SC.S# from SC,Course,Teacher where SC.C# Course.C# and Teacher.T# Course.T# and Teacher.Tname 叶平 ; 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select Student.S#,Student.Sname from Student,SC where Student.S# SC.S# and SC.C# 001and exists Select * from SC as SC_2 where SC_2.S# SC.S# and SC_2.C# 002 ; 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； select S#,Sname from Student where S# in select S# from SC ,Course ,Teacher where SC.C# Course.C# and Teacher.T# Course.T# and Teacher.Tname 叶平 group by S# having count SC.C# select count C# from Course,Teacher where Teacher.T# Course.T# and Tname 叶平 ; 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； Select S#,Sname from select Student.S#,Student.Sname,score , select score from SC SC_2 where SC_2.S# Student.S# and SC_2.C# 002 score2 from Student,SC where Student.S# SC.S# and C# 001 S_2 where score2 score; 9、查询所有课程成绩小于60分的同学的学号、姓名； select S#,Sname from Student where S# not in select Student.S# from Student,SC where S.S# SC.S# and score 60 ; 10、查询没有学全所有课的同学的学号、姓名； select Student.S#,Student.Sname from Student,SC where Student.S# SC.S# group by Student.S#,Student.Sname having count C# select count C# from Course ; 11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名； select S#,Sname from Student,SC where Student.S# SC.S# and C# in select C# from SC wh