《sql语句练习50题》由会员分享，可在线阅读，更多相关《sql语句练习50题（3页珍藏版）》请在金锄头文库上搜索。
1、sqlsql语句练习 5050题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 WHEREa.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.sid=SC.cid GROUP
3、 BYStudent.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,TeacherWHERE SC.cid=Course.cid AND Teacher.id=Course.tid AND Teacher.Tname
4、=叶平);此题知识点，distinct是去重的作6.查询学过“”并且也学过编号“”课程的同学的学号、姓名；select a.SID,a.SNAME from (select student.SNAME,student.SID from student,course,sc where cname=c+and sc.sid=student.sidand sc.cid=course.cid) a,(select student.SNAME,student.SID from student,course,sc where cname=englishand sc.sid=student.sid and
5、sc.cid=course.cid) bwhere a.sid=b.sid;标准答案（但是好像不好使）SELECT Student.S#,Student.Sname FROM Student,SC WHERE Student.S#=SC.S# ANDSC.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.sid,a.sname from (select stud
6、ent.sid,student.sname from student,teacher,course,scwhere 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.cidAND Teacher.tid=Course.tid AND Teacher.Tna
7、me=杨巍巍 GROUP BY sid having count(SC.cid)=(SELECT count(cid) FROMCourse,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 sc.cid=1) a,(select student.SID
8、,student.sname,sc.score from student,sc where student.sid=sc.sid and sc.cid=2) b where a.scoreb.score anda.sid=b.sid标准答案：SELECT sid,Sname FROM (SELECT Student.sid,Student.Sname,score ,(SELECT score FROM SC SC_2 WHERE SC_2.sid=Student.sid AND SC_2.cid=1) score2 FROM Student,SCWHERE Student.sid=SC.sid
9、 AND cid=1) S_2 WHERE score2 60);此题知识点，先查出于60分的，然后not in 就是于60分的了10.查询没有学全所有课的同学的学号、姓名；SELECT Student.sid,Student.Sname FROM Student,SCWHERE Student.sid=SC.sid GROUP BY Student.sid,Student.Sname having count(cid) (SELECT count(cid) FROM Course);11.查询少有门课与学号为“”的同学所学相同的同学的学号和姓名；12.查询少学过学号为“”同学所有门课的其他同
10、学学号和姓名；SELECT student.sid,student.Sname FROM Student,SC WHERE Student.sid=SC.sid AND cid in (SELECT cid FROM SC WHEREsid=1)此题知识点，SELECT sid,Sname FROM Student,SC WHERE Student.sid=SC.sid AND cid in (SELECT cid FROM SC WHEREsid=1)这样写是错误的，因为from后是两个表，不能明确是哪个表的sid和sname所以错误提是“未明确定义列”13.把“SC”表中“叶平”师教的课的
11、成绩都更改为此课程的平均成绩；update sc set score=(select avg(score) from sc,course,teacher where course.cid=sc.cid and course.tid=teacher.tid andteacher.tname=杨巍巍)14.查询和“”号的同学学习的课程完全相同的其他同学学号和姓名；SELECT sid FROM SC WHERE cid in (SELECT cid FROM SC WHERE sid=6) GROUP BY sid having count(*)=(SELECT count(*)FROM SC W
12、HERE sid=6);此题知识点，数量来判断15.删除学习“叶平”师课的SC表记录；delete from sc s where s.cid in (select c.cid from teacher t,course c where t.tid = c.tid and tname= 李)此题知识点，嵌套查询可以分布考虑，先查出李师都交了什么课的id，然后再删除那些id的值16.向SC表中插些记录，这些记录要求符合以下条件：没有上过编号“”课程的同学学号、课程的平均成绩；Insert into SC SELECT sid,2,(SELECT avg(score) FROM SC WHERE
13、cid=2) FROM Student WHERE sid not in (SELECT sid FROMSC WHERE cid=2);17.按平均成绩从到低显所有学的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显：学ID,数据库,企业管理,英语,有效课程数,有效平均分；(没做出来)18.查询各科成绩最和最低的分：以如下形式显：课程ID，最分，最低分；select cid as 课程号,max(score)as最分,min(score) as最低分 from sc group by cid标准答案（但是运不好使）SELECT L.cid As 课程ID,L.score AS
14、最分,R.score AS 最低分FROM SC L ,SC AS RWHERE L.cid = R.cid ANDL.score = (SELECT MAX(IL.score)FROM SC AS IL,Student AS IMWHERE L.cid = IL.cid AND IM.sid=IL.sidGROUP BY IL.cid)AND R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.cid = IR.cid GROUP BY IR.cid );19.按各科平均成绩从低到和及格率的百分数从到低顺序26.查询每门课程被选修的学数
15、select sc.cid,count(sc.sid) from sc,course where sc.cid=course.cid group by sc.cid27.查询出只选修了门课程的全部学的学号和姓名SELECT SC.sid,Student.Sname,count(cid) AS 选课数 FROM SC ,StudentWHERE SC.sid=Student.sid GROUP BY SC.sid ,Student.Sname having count(cid)=1;32.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列SELECT Cid,Avg
16、(score) FROM SC GROUP BY cid ORDER BY Avg(score),cid DESC ;37.查询不及格的课程，并按课程号从到排列SELECT cid,sid FROM sc WHERE score 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 s
17、c.sid=student.sid and sc.score=(select max(score)from sc where sc.cid=c.cid)41.查询各个课程及相应的选修数select sc.cid ,count(sc.sid)from sc,student where sc.sid=student.sid group by sc.cid43.查询每门功成绩最好的前两名44.统计每门课程的学选修数（超过的课程才统计）。要求输出课程号和选修数，查询结果按数降序排列，查询结果按数降序排列，若数相同，按课程号升序排列select sc.cid,count(sc.cid)from sc,c
18、ourse where sc.cid=course.cid group by sc.cid order by sc.cid desc45.检索少选修两门课程的学学号SELECT sid FROM sc group by sid having count(*) = 2rownum的法查询所有成绩第名到第四名的成绩select * from（select rownum p,t.score from （SELECT s.score score FROM sc s ORDER BY score desc）t ）tt where tt.p1 andtt.p547.查询没学过“叶平”师讲授的任门课程的学姓
19、名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 andteacher.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
