资源描述：
《Sql语句综合练习.ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、SQL语句综合练习信息工程学院计算机专业教研室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、求选
2、了课程的学生人数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、查询选课同学的学号、选课数、总成
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
4、)成绩表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
5、)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(selectSnofromSCoursewherescore<60groupbySn
6、ohavingcount(*)>2)groupbySno;St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表7、查询两门以上不及格课程的同学的学号及其平均成绩2010-10-11selectSno,avg(score)fromSCoursewherescore<60groupbySnohavingcount(*)>2;St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCou
7、rse(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.scoref
8、romSCourseA,SCourseBwhereA.Score=B.ScoreandA.Cno!=B.CnoandA.Sno=B.Sno;St(Sn
