50个常用sq语句实例(学生表 课程表 成绩表 教师表).docVIP

Student S#,Sname,Sage,Ssex 学生表 Course C#,Cname,T# 课程表 SC S#,C#,score 成绩表 Teacher T#,Tname 教师表 create table Student S# varchar 20 ,Sname varchar 10 ,Sage int,Ssex varchar 2 前面加一列序号： if exists select table_name from information_schema.tables where table_name Temp_Table drop table Temp_Table go select 排名 identity int,1,1 ,* INTO Temp_Table from Student go select * from Temp_Table go drop database [ ] --删除空的没有名字的数据库 问题： 1、查询“”课程比“”课程成绩高的所有学生的学号； 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、查询平均成绩大于分的同学的学号和平均成绩； 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、查询学过“”并且也学过编号“”课程的同学的学号、姓名； 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、查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名； 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、查询所有课程成绩小于分的同学的学号、姓名； select S#,Sname from Student where S# not in select Student.S# from Student,SC where S.S# SC.S# and sco