50道经典SQL语句题.pdfVIP

50道经典SQL语句题 表 Student(S#,Sname,Sage,Ssex) 学⽣表 Course(C#,Cname,T#) 课程表 SC(S#,C#,s ore) 成绩表 Tea her(T#,Tname) 教师表 问题 ： 1、查询 “00 1”课程⽐ “002”课程成绩⾼的所有学⽣的学号 ； sele t a.S# from (sele t s#,s ore from SC where C#=’00 1’) a,(sele t s#,s ore from SC where C#=’002’) b where a.s oreb.s ore and a.s#=b.s#; 2、查询平均成绩⼤于60分的同学的学号和平均成绩 ； select S# ,avg(score) from sc group by S# having avg(score) 60 3、查询所有同学的学号、姓名、选课数、总成绩 ； sele t Student.S#,Student.Sname, ount(SC.C#),sum(s ore) from Student left Outer j oin SC on Student.S#=SC.S# group by Student.S#,Sname 4、查询姓 “李”的⽼师的个数 ； sele t ount(distin t(Tname)) from Tea her 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、查询学过 “00 1”并且也学过编号 “002”课程的同学的学号、姓名 ； sele t Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#=’00 1’and exists( Sele t * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002’); 7、查询学过 “叶平”⽼师所教的所有课的同学的学号、姓名 ； sele t S#,Sname from Student where S# in (sele t S# from SC ,Course ,Tea her where SC.C#=Course.C# and Tea her.T#=Course.T# and Tea her.Tname=’叶平’ group by S# having ount(SC.C#)=(sele t ount(C#) from Course,Tea her where Tea her.T#=Course.T# and Tname=’叶平’)); 8、查询课程编号 “002”的成绩⽐课程编号 “00 1”课程低的所有同学的学号、姓名 ； Sele t S#,Sname from (sele t Student.S#,Student.Sname,s ore ,(sele t s ore from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#=’002’) s ore2 from Student,SC where Student.S#=SC.S# and C#=’00 1’) S_2 where s ore2 Select Top N * From 表 N到M条记录(要有主索引ID) Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc N到结尾记录 Sele t Top N * From 表 Order by ID Des 案例 例如1：⼀张表有⼀万多条记录，表的第⼀个字段 Re ID 是 ⾃增长字段， 写⼀个SQL语句， 找出表的第3 1到第40个记录。 sele t top 10 re id from A where