1、sql 语句练习 50 题Student(Sid,Sname,Sage,Ssex) 学生表Course(Cid,Cname,Tid) 课程表SC(Sid,Cid,score) 成绩表Teacher(Tid,Tname) 教师表练习内容：1.查询“某 1”课程比“某 2”课程成绩高的所有学生的学号；SELECT a.sid FROM (SELECT sid,score FROM SC WHERE cid=1) a,(SELECT sid,score FROM SC WHERE cid=3) b WHERE a.scoreb.score AND a.sid=b.sid;此题知识点，嵌套查询和给查出
2、来的表起别名2.查询平均成绩大于 60 分的同学的学号和平均成绩；SELECT sid,avg(score) FROM sc GROUP BY sid having avg(score) 60;此题知识点，GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。group by后面不能接 where，having 代替了 where3.查询所有同学的学号、姓名、选课数、总成绩SELECT Student.sid,Student.Sname,count(SC.cid),sum(score)FROM Student left Outer JOIN SC on Student.si
3、d=SC.cid GROUP BY Student.sid,Sname4.查询姓“李”的老师的个数；select count(teacher.tid)from teacher where teacher.tname like李%5.查询没学过“叶平”老师课的同学的学号、姓名；SELECT Student.sid,Student.Sname FROM Student WHERE sid not in (SELECT distinct( SC.sid) FROM SC,Course,Teacher WHERE SC.cid=Course.cid AND Teacher.id=Course.tid
4、AND Teacher.Tname=叶平);此题知识点，distinct 是去重的作用6.查询学过“”并且也学过编号“”课程的同学的学号、姓名；select a.SID,a.SNAME from (select student.SNAME,student.SID from student,course,sc where cname=c+and sc.sid=student.sid and sc.cid=course.cid) a,(select student.SNAME,student.SID from student,course,sc where cname=englishand sc.
5、sid=student.sid and sc.cid=course.cid) b where a.sid=b.sid;标准答案（但是好像不好使）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); 此题知识点，exists 是在集合里找数据，as 就是起别名7.查询学过“叶平”老师所教的所有课的同学的学号、姓名；select a.si
6、d,a.sname from (select student.sid,student.sname from student,teacher,course,sc where teacher.TNAME=杨巍巍 and teacher.tid=course.tid and course.cid=sc.cid and student.sid=sc.sid) a标准答案：SELECT sid,Sname FROM Student WHERE sid in (SELECT sid FROM SC ,Course ,Teacher WHERE SC.cid=Course.cid AND Teacher.t
7、id=Course.tid AND Teacher.Tname=杨巍巍 GROUP BY sid having count(SC.cid)=(SELECT count(cid) FROM Course,Teacher WHERE Teacher.tid=Course.tid AND Tname=杨巍巍)8.查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名；select a.sid,a.sname from(select student.SID,student.sname,sc.SCORE from student,sc where student.sid=sc.sid and
8、sc.cid=1) a,(select student.SID,student.sname,sc.score from student,sc where student.sid=sc.sid and sc.cid=2) b where a.score60); 此题知识点，先查出大于 60 分的，然后 not in 就是小于 60 分的了10.查询没有学全所有课的同学的学号、姓名；SELECT Student.sid,Student.Sname FROM Student,SC WHERE Student.sid=SC.sid GROUP BY Student.sid,Student.Sname
9、having count(cid) 60 and sc.sid=student.sid40.查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩select student.sname,sc.score from sc,student,teacher,course c where teacher.tname=李子and teacher.tid=c.tid and c.cid=sc.cid and sc.sid=student.sid and sc.score=(select max(score)from sc where sc.cid=c.cid)41.查询各个课程及相应的选修人数
10、select sc.cid ,count(sc.sid)from sc,student where sc.sid=student.sid group by sc.cid 43.查询每门功成绩最好的前两名44.统计每门课程的学生选修人数（超过人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列select sc.cid,count(sc.cid)from sc,course where sc.cid=course.cid group by sc.cid order by sc.cid desc45.检索至少选修两门课程的学生学号
11、SELECT sid FROM sc group by sid having count(*) = 2 rownum 的用法查询所有成绩第二名到第四名的成绩select * from （select rownum p,t.score from（SELECT s.score score FROM sc s ORDER BY score desc）t ）tt where tt.p1 and tt.p547.查询没学过“叶平”老师讲授的任一门课程的学生姓名select distinct sid from sc where sid not in(select sc.sid from sc,course,teacher where sc.cid=course.cid and course.tid=teacher.tid and teacher.tname=杨巍巍)48.查询两门以上不及格课程的同学的学号及其平均成绩49.检索“”课程分数小于，按分数降序排列的同学学号select sc.sid from sc,course where sc.cid=course.cid and ame=java and sc.score9050.删除“”同学的“”课程的成绩 delete from sc where sid=1 and cid=1
