资源描述：
《sql语句练习50题》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、sql语句练习50题 Student(Sid,Sname,Sage,Ssex)学生表Course(Cid,Cname,Tid)课程表SC(Sid,Cid,score)成绩表Teacher(Tid,Tname)教师表练习内容：1.查询“某1”课程比“某2”课程成绩高的所有学生的学号；SELECTa.sidFROM(SELECTsid,scoreFROMSCWHEREcid=1)a,(SELECTsid,scoreFROMSCWHEREcid=3)bWHEREa.score>b.scoreANDa.sid=b.sid;此题知识点，嵌套查询和给查出来的表起别名2.
2、查询平均成绩大于60分的同学的学号和平均成绩；SELECTsid,avg(score) FROMsc GROUPBYsidhavingavg(score)>60;此题知识点，GROUPBY语句用于结合合计函数，根据一个或多个列对结果集进行分组。groupby后面不能接where，having代替了where3.查询所有同学的学号、姓名、选课数、总成绩SELECTStudent.sid,Student.Sname,count(SC.cid),sum(score)FROMStudentleftOuterJOINSConStudent.sid=SC.cidGROU
3、PBYStudent.sid,Sname4.查询姓“李”的老师的个数；selectcount(teacher.tid)fromteacherwhereteacher.tnamelike'李%'5.查询没学过“叶平”老师课的同学的学号、姓名；SELECTStudent.sid,Student.SnameFROMStudentWHEREsidnotin(SELECTdistinct(SC.sid)FROMSC,Course,TeacherWHERE SC.cid=Course.cidANDTeacher.id=Course.tidANDTeacher.Tname
4、='叶平'); 此题知识点，distinct是去重的作用6.查询学过“```”并且也学过编号“```”课程的同学的学号、姓名；selecta.SID,a.SNAMEfrom(selectstudent.SNAME,student.SIDfromstudent,course,scwherecname='c++'andsc.sid=student.sidandsc.cid=course.cid)a,(selectstudent.SNAME,student.SIDfromstudent,course,scwherecname='english'andsc.sid=
5、student.sidandsc.cid=course.cid)bwherea.sid=b.sid;标准答案（但是好像不好使）SELECTStudent.S#,Student.SnameFROMStudent,SCWHEREStudent.S#=SC.S#ANDSC.C#='001'andexists(SELECT*FROMSCasSC_2WHERESC_2.S#=SC.S#ANDSC_2.C#='002'); 此题知识点，exists是在集合里找数据，as就是起别名7.查询学过“叶平”老师所教的所有课的同学的学号、姓名；selecta.sid,a.snam
6、efrom(selectstudent.sid,student.snamefromstudent,teacher,course,sc whereteacher.TNAME='杨巍巍'andteacher.tid=course.tidandcourse.cid=sc.cidandstudent.sid=sc.sid)a标准答案：SELECTsid,SnameFROMStudentWHEREsidin(SELECTsidFROMSC,Course,TeacherWHERESC.cid=Course.cidANDTeacher.tid=Course.tidANDT
7、eacher.Tname='杨巍巍'GROUPBYsidhavingcount(SC.cid)=(SELECTcount(cid)FROMCourse,Teacher WHERETeacher.tid=Course.tidANDTname='杨巍巍'))8.查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名；selecta.sid,a.snamefrom(selectstudent.SID,student.sname,sc.SCORE fromstudent,scwherestudent.sid=sc.sidandsc.cid=1)a,(sel
8、ectstudent.SID,student.sna
