sql语句练习50题.pdf

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.查询平均成绩⼤于60分的同学的学号和平均成绩；SELECTsid,avg(score)FROMscGROUPBYsidhavingavg(score)>60;此题知识点，GROUP语句⽤于结合合计函数，根据⼀个或多个列对结果集进⾏分组。BYgroup后⾯不能接bywhere，having代替了where3.查询所有同学的学号、姓名、选课数、总成绩SELECTStudent.sid,Student.Sname,count(SC.cid),sum(score)FROMStudentleftOuterJOINSConStudent.sid=SC.cidGROUPBYStudent.sid,Sname4.查询姓“李”的⽼师的个数；selectcount(teacher.tid)from李%'teacherwhereteacher.tnamelike'5.查询没学过“叶平”⽼师课的同学的学号、姓名；SELECTStudent.sid,Student.SnameFROMStudentWHEREsidnotin(SELECTdistinct(SC.sid)FROMSC,Course,TeacherWHERESC.cid=Course.cidANDTeacher.id=Course.tid叶平');ANDTeacher.Tname='此题知识点，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=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.snamefrom(selectstudent.sid,student.snamefromstudent,teacher,course,scwhereteacher.TNAME='杨巍巍'andteacher.tid=course.tidandcourse.cid=sc.cidandstudent.sid=sc.sid)a标准答案：SELECTsid,SnameFROMStudentWHEREsidin(SELECTsidFROMSC,Course,TeacherWHERESC.cid=Course.cidANDTeacher.tid=Course.tid杨巍巍'ANDGROUPTeacher.Tname='BYsidhavingcount(SC.cid)=(SELECTcount(cid)FROMCourse,TeacherWHERETeacher.tid=Course.tid杨巍巍'))ANDTname='8.查询课程编号“”的成绩⽐课程编号“”课程低的所有同学的学号、姓名；selecta.sid,a.snamefrom(selectstudent.SID,student.sname,sc.SCOREfromstudent,scwherestudent.sid=sc.sidandsc.cid=1)a,(selectstudent.SID,student.sname,sc.sco