Sql语句综合练习

2010-10-11SQL语句综合练习信息工程学院计算机专业教研室2010-10-102010-10-11St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表2010-10-11selectcount(distinctSno)fromSCourse;St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表1、求选了课程的学生人数2010-10-11selectSno,avg(score)fromSCoursegroupbySnohavingavg(score)60;2、查询平均成绩大于60分的同学的学号和平均成绩•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表2010-10-11selectSno,count(Cno),sum(score)fromSCoursegroupbySno;3、查询选课同学的学号、选课数、总成绩•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表2010-10-11selectcount(ssex)男生人数fromStgroupbyssexhavingssex='男';selectcount(ssex)女生人数fromStgroupbyssexhavingssex='女';•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表4、查询男生、女生人数selectcount(sno)fromStwhereSsex='男';2010-10-11selectSname,count(*)fromStgroupbySnamehavingcount(*)1;•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表5、查询同名学生名单，并统计同名人数2010-10-11selectCno,avg(score)fromSCoursegroupbyCnoorderbyavg(score),Cnodesc;•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表6、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列2010-10-11selectSno,avg(score)fromSCoursewhereSnoin(selectSnofromSCoursewherescore60groupbySnohavingcount(*)2)groupbySno;•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表7、查询两门以上不及格课程的同学的学号及其平均成绩2010-10-11selectSno,avg(score)fromSCoursewherescore60groupbySnohavingcount(*)2;•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表7+、查询两门以上不及格课程的同学的学号及其平均成绩2010-10-11selectcount(Tname)fromTeacherwhereTnamelike'李%';8、查询姓“李”的老师的个数•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表2010-10-11selectdistinctA.Sno,A.Cno,A.scorefromSCourseA,SCourseBwhereA.Score=B.ScoreandA.Cno!=B.CnoandA.Sno=B.Sno;•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表9、查询同一个学生成绩相同、课程不同的课程号、学生成绩和该学生的学号2010-10-11selectSt.Sname,scorefromSt,SCourse,C,TeacherwhereSt.Sno=SCourse.Snoandscourse.cno=c.cnoandC.Tno=Teacher.TnoandTeacher.Tname='张山'andscore=(selectmax(score)fromSCourse,C,Teacherwherescourse.cno=c.cnoandC.Tno=Teacher.TnoandTeacher.Tname='张山');•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表10、查询选修“张山”老师所授课程的学生中，成绩最高的学生姓名及其成绩2010-10-11selectSt.Sname,scorefromSt,SCourse,C,TeacherwhereSt.Sno=SCourse.Snoandscourse.cno=c.cnoandC.Tno=Teacher.TnoandTeacher.Tname='张山'andscore=(selectmax(score)fromSCoursewherecnoin(selectcnofromCwhereTnoin(selectTnofromTeacherwhereTname='张山')));•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表10、查询选修“张山”老师所授课程的学生中，成绩最高的学生姓名及其成绩2010-10-11selectSt.Sname,max(score)fromSt,SCourse,C,TeacherwhereSt.Sno=SCourse.Snoandscourse.cno=c.cnoandC.Tno=Teacher.TnoandTeacher.Tname='张山';•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表10+、查询选修“张山”老师所授课程的学生中，成绩最高的学生姓名及其成绩2010-10-11•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表selectSt.Sno,St.SnamefromStwhereSnonotin(selectdistinct(SCourse.Sno)fromSCourse,C,TeacherWhereSCourse.Cno=C.CnoandTeacher.Tno=C.TnoandTeacher.Tname='张山');（解二、解三在备注）11、查询没选修“张山”老师课的同学的学号、姓名2010-10-11•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表selectSnofromstwhereSnonotin(selectSnofromSCoursegroupbySnohavingcount(Cno)=(selectcount(Cno)fromC));12、查询没有选全所有课的同学的学号2010-10-11selectSnofromSCoursegroupbySnohavingcount(Cno)(selectcount(Cno)fromC);12+、查询没有学全所有课的同学的学号•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表2010-10-11updateSCoursesetscore=(selectavg(score)fromSCoursewherecnoin(selectcnofromcwheretnoin(selecttnofromteacherwhereTname='张山')))Wherecnoin(selectcnofromcwheretnoin(selecttnofromteacherwhereTname='张山'));•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表13、把SCourse表中“张山”老师教的课的成绩都更改为“张山”老师教的课程的平均成绩2010-10-11InsertintoSCourse(selectSno,'0002',(Selectavg(score)fromSCoursewhereCno='0002')fromStwhereSnonotin(SelectSnofromSCoursewhereCno='0002'));•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表14、向SCourse表中插入一些记录，这些记录要求符合以下条件：没有上过编号0002课程的同学学号、0002号课、0002号课的平均成绩；2010-10-11deletefromScoursewhereCnoin(selectCnofromCwhereTnoin(selectTnofromTeacherwhereTname='张山'));•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表16、删除学习“张山”老师课的SCourse表记录；2010-10-1117、查询和“0405102303”号的同学学习的课程完全相同的学生学号•St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表2010-10-11(SELECTDISTINCTSnoFROMSCourseSCXWHERENOTEXISTS(SELECT*FROMSCourseSCYWHERESCY.Sno='0405102303'ANDNOTEXISTS(SELECT*