SQL练习题-课程

SQL练习题答案一、补充作业一、设有三个关系：S(SNO,SNAME,AGE,SEX,Sdept)SC(SNO,CNO,GRADE)C(CNO,CNAME,TEACHER)试用关系代数表达式表示下列查询：1、查询学号为S3学生所学课程的课程名与任课教师名。)(('3',CSCSSNOTEACHERCNAME2、查询至少选修LIU老师所教课程中一门课的女生姓名。))((''''CSCSLIUTEACHERSEXsname女3、查询WANG同学不学的课程的课程号。))(()(''SCSSCWANGSNAMECNOCNO4、查询至少选修两门课程的学生学号。)((52412,1SCSCS5、查询选修课程中包含LIU老师所教全部课程的学生学号。))(()('',CSCLIUTEACHERCNOCNOSNO补充作业二、三个关系同上，试用SQL语言表示下列查询：1、查询门门课程都及格的学生的学号方法1：提示：根据学号分组，就得到每个学生所有的课程成绩，在某个学生这一组成绩里，如果他所有的课程成绩都大于60分则输出该组学生的学号Selectsnofromescgroupbysnohaving(min(grade)=60)2、查询既有课程大于90分又有课程不及格的学生的学号自身连接：Selectsnofromscwheregrade90andsnoin(selectsnofromscwheregrade60)3、查询平均分不及格的课程号和平均成绩Selectcno,avg(GRADE)fromscgroupbycnohavingavg(grade)60查询平均分及格的课程号和课程名SelectC.cno,CnamefromSC,CwhereC.cno=SC.cnogroupbyC.cnohavingavg(grade)=604、找出至少选修了2号学生选修过的全部课程的学生号提示:不存在这样的课程y，学生2选修了y，而学生x没有选。SELECTDISTINCTSnoFROMSCasSCXWHERENOTEXISTS(SELECT*FROMSCasSCYWHERESCY.Sno=‘2’ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))5、求各门课程去掉一个最高分和最低分后的平均分第一步，求所有成绩的平均分（去掉一个最高分和最低分）selectavg(GRADE)fromSCwhereGRADEnotin(selecttop1GRADEfromSCorderbyGRADE)andGRADEnotin(selecttop1GRADEfromSCorderbyGRADEdesc)第二步，将所有成绩按各门课程的课程号CNO分组SELECTCNOavg(GRADE)fromSCwhereGRADEnotin(selecttop1GRADEfromSCorderbyGRADE)andGRADEnotin(selecttop1GRADEfromSCorderbyGRADEdesc)groupbyCNO1、查询7号课程没有考试成绩的学生学号。Selectsnofromscwherecno='7'andgradeisnull2、查询7号课程成绩在90分以上或60分以下的学生学号。Selectsnofromscwherecno='7'andgradenotbetween60and903、查询课程名以“数据”两个字开头的所有课程的课程号和课程名。Selectcno,cnamefromcwherecnamelike'数据%'4、查询每个学生所有课程的平均成绩，输出学生学号和平均成绩。Selectsno,avg(grade)fromscgroupbysno5、查询每门课程的选修人数，输出课程号和选修人数。Selectcno,count(*)fromscgroupbycno6、查询选修7号课程的学生的学号、姓名、性别。Selects.sno,sname,ssexfroms,scwheres.sno=sc.snoandcno='7'或：Selectsno,sname,ssexfromswheresnoin(Selectsnofromscwherecno='7')7、查询选修7号课程的学生的平均年龄。Selectavg(sage)froms,scwheres.sno=sc.snoandcno='7'或：Selectavg(sage)fromswheresnoin(Selectsnofromscwherecno='7')8、查询有30名以上学生选修的课程号。Selectcnofromscgroupbycnohavingcount(*)309、查询至今没有考试不及格的学生学号。Selectdistinctsnofromscwheresnonotin(Selectsnofromscwheregrade60)或：Selectsnofromscgroupbysnohavingmin(grade)=6010、查询所有考试成绩的平均分相同的学生学号分组二、1、找出选修课程号为C2的学生学号与成绩。Selectsno,gradefromscwherecno='C2'2、找出选修课程号为C4的学生学号与姓名。Selects.sno,snamefroms,scwheres.sno=sc.snoandcno='C4'注意本题也可以用嵌套做思考本题改为“找出选修课程号为C4的学生学号、姓名与成绩”后还能用嵌套做吗？3、找出选修课程名为Maths的学生学号与姓名。Selects.sno,snamefroms,sc,cwheres.sno=sc.snoandc.cno=sc.cnoandcname='Maths'注意本题也可以用嵌套做4、找出选修课程号为C2或C4的学生学号。Selectdistinctsnofromscwherecnoin('C2','C4')或：Selectdistinctsnofromscwherecno='C2'orcno='C4'5、找出选修课程号为C2和C4的学生学号。Selectsnofromscwherecno='C2'andsnoin(Selectsnofromscwherecno='C4')注意本题也可以用连接做思考：Selectdistinctsnofromscwherecno='C2'andcno='C4'正确吗？6、找出不学C2课程的学生姓名和年龄。Selectsname,sagefromswheresnonotin(Selectsnofromscwherecno='C2')或：Selectsname,sagefromswherenotexists(Select*fromscwheresno=s.snoandcno='C2')7、找出选修了数据库课程的所有学生姓名。(同3)Selectsnamefroms,sc,cwheres.sno=sc.snoandc.cno=sc.cnoandcname='数据库'8、找出数据库课程不及格的女生姓名。连接：Selectsnamefroms,sc,cwheres.sno=sc.snoandc.cno=sc.cnoandcname='数据库'andgrade60andssex='女'嵌套：Selectsnamefromswheressex='女'andsnoin(Selectsnofromscwheregrade60andcnoin(Selectcnofromcwherecname='数据库'))9、找出各门课程的平均成绩，输出课程名和平均成绩。Selectcname,avg(grade)fromsc,cwherec.cno=sc.cnogroupbyc.cno,cname思考本题也可以用嵌套做吗？10、找出各个学生的平均成绩，输出学生姓名和平均成绩。Selectsname,avg(grade)froms,scwheres.sno=sc.snogroupbys.sno,sname思考本题也可以用嵌套做吗？11、找出至少有30个学生选修的课程名。Selectcnamefromcwherecnoin(Selectcnofromscgroupbycnohavingcount(*)=30)注意本题也可以用连接做12、找出选修了不少于3门课程的学生姓名。Selectsnamefromswheresnoin(Selectsnofromscgroupbysnohavingcount(*)=3)注意本题也可以用连接做13、找出各门课程的成绩均不低于90分的学生姓名。Selectsnamefroms,scwheres.sno=sc.snogroupbys.sno,snamehavingmin(grade)=90方法二：Selectsnamefromswheresnonotin(Selectsnofromscwheregrade90)只要有一门不小于90分就会输出该学生学号14、找出数据库课程成绩不低于该门课程平均分的学生姓名。Selectsnamefroms,sc,cwheres.sno=sc.snoandsc.cno=c.cnoandcname='数据库'andgrade(Selectavg(grade)fromsc,cwheresc.cno=c.cnoandcname='数据库')15、找出各个系科男女学生的平均年龄和人数。Selectsdept,ssex,avg(sage),count(*)fromsgroupbysdept,ssex16、找出计算机系(JSJ)课程平均分最高的学生学号和姓名。Selects.sno,snamefroms,scwheres.sno=sc.snoandsdept='JSJ'groupbys.sno,snamehavingavg(grade)=ALL(Selectavg(grade)froms,scwheres.sno=sc.snoandsdept='JSJ'groupbys.sno)17、(补充)查询每门课程的及格率。本题可以分三步做：第1步：得到每门课的选修人数createviewv_all(cno,cnt)asselectcno,count(*)fromscgroupbycno第2步：得到每门课及格人数createviewv_pass(cno,cnt_pass)asselectcno,count(*)fromscwheregrade=60groupbycno第3步：每门课的及格人数/每门课选修人数selectv_all.cno,cnt_pass*100/cntfromv_all,v_passwherev_all.cno=v_pass.cno18、查询平均分不及格的学生的学号，姓名，平均分。Selectsc.sno,sname,avg(grade)fromstudent,scwherestudent.sno=sc.snogroupbysc.sno,snamehavingavg(grade)60思考本题也可以用嵌套做吗？19、查询平均分不及格的学生人数。Selectcount(*)fromstudentwheresnoin(selectsnofromscgroupbysnohavingavg(grade)60)下面是一个典型的错误Selectcount(*)fromscgroupbysnohavingavg(grade)60这是每个学生有几门不及格的数目三、1、查询工资在1000到3000元之间的男性业务员的姓名和办公室编号。SelectYname,OnofromYWYwhereSalarybetween1000and3000andYsex='男'2、查询各个办公室的业务员人数，输出办公室编号和对应的人数。SelectOno,count(*)fromYWYgroupbyOno3、查询每个客户在2002