Sql语句综合练习.ppt

2010-10-11,SQL语句综合练习,信息工程学院计算机专业教研室,2010-10-10,2010-10-11,St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表,2010-10-11,selectcount(distinctSno)fromSCourse;,St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表,1、求选了课程的学生人数,2010-10-11,selectSno,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-11,selectSno,count(Cno),sum(score)fromSCoursegroupbySno;,3、查询选课同学的学号、选课数、总成绩,St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表,2010-10-11,selectcount(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-11,selectSname,count(*)fromStgroupbySnamehavingcount(*)1;,St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表,5、查询同名学生名单，并统计同名人数,2010-10-11,selectCno,avg(score)fromSCoursegroupbyCnoorderbyavg(score),Cnodesc;,St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表,6、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列,2010-10-11,selectSno,avg(score)fromSCoursewhereSnoin(selectSnofromSCoursewherescore2)groupbySno;,St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表,7、查询两门以上不及格课程的同学的学号及其平均成绩,2010-10-11,selectSno,avg(score)fromSCoursewherescore2;,St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表,7+、查询两门以上不及格课程的同学的学号及其平均成绩,2010-10-11,selectcount(Tname)fromTeacherwhereTnamelike李%;,8、查询姓“李”的老师的个数,St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表,2010-10-11,selectdistinctA.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-11,selectSt.Sname,scorefromSt,SCourse,C,TeacherwhereSt.Sno=SCourse.So=oandC.Tno=Teacher.TnoandTeacher.Tname=张山andscore=(selectmax(score)fromSCourse,C,To=oandC.Tno=Teacher.TnoandTeacher.Tname=张山);,St(Sno,Sname,Sage,Ssex)学生表C(Cno,Cname,Tno)课程表SCourse(Sno,Cno,score)成绩表Teacher(Tno,Tname)教师表,10、查询选修“张山”老师所授课程的学生中，成绩最高的学生姓名及其成绩,2010-10-11,selectSt.Sname,scorefromSt,SCourse,C,TeacherwhereSt.Sno=SCourse.So=oandC.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-11,selectSt.Sname,max(score)fromSt,SCourse,C,TeacherwhereSt.Sno=SCourse.So=oandC.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-11,selectSnofromSCoursegroupbySnohavingcount(Cno)400;,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,8.查出供给关系不在同一个城市的供、需双方地址。,selectdistinctS.CITY,J.CITYfromS,J,SPJwhereS.SNO=SPJ.SNOandJ.JNO=SPJ.JNOandS.CITY!=J.CITY;,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,9.求至少用了供应商000l所供应的全部零件的工程号,SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESNO=0001ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.PNO=SPJY.PNOANDSPJZ.JNO=SPJX.JNO);,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,10.授权用户刘星对供应情况表SPJ有修改表的结构的权力、有SELECT权力、对供应数量（QTY）字段具有更新权力。并具有给其他用户授权的权力,GRANTALTER,SELECT,UPDATE(QTY)ONSPJTO刘星WITHGRANTOPTION;,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,11.把全部红色零件的颜色改为蓝色。,UPDATEPSETCOLOR=蓝WHERECOLOR=红;,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),2010-10-11,12.从供应商关系中删除S2的纪录，并从供应情况关系中删除相应的纪录。,DELETEFROMSPJWHERESNO=S2;DELETEFROMSWHERESNO=S2;,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEI)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),