38数据库5版SQL例题

1SQL例题4.1SQL背景一、样表结构学生表：S=(Sno,Sname,Ssex,Sage,Sdept)课程表：Course=(Cno,Cname,Pcno,Ccredit)选课表：SC=(Sno,Cno,Grade）二.单表查询例：查询全体学生的学号与姓名。SELECTSno,SnameFROMS例：查询全体学生的姓名、学号、所在系。SELECTSname,Sno,SdeptFROMS例：查询全体学生的详细记录。SELECT*FROMS例：查询全体学生的姓名及其出生年份。SELECTSname,2006-SageFROMS例查询全体学生的姓名、出生年份和所在系，小写字母表示所有系名。SELECTSname,‘YearofBirth:’,2006-SageFROMS例SELECTSnameasNAME,‘YearofBirth:'asBIRTH,2009-SageasBIRTHTYEAR,ISLOWER(Sdept)asDEPARTMENTFROMS例SELECTSnoFROMSC例SELECTallSnoFROMSC例SELECTdistinctSnoFROMSC例查计算机系全体学生的姓名。SELECTSnameFROMSWHERESdept=‘CS’2例查所有年龄在20岁以下的学生姓名及其年龄。SELECTSname,SageFROMSWHERESage＜20；或SELECTSname,SageFROMSWHERENOTSage＞=20例查考试成绩有不及格的学生的学号。SELECTDISTINCTSnoFROMSCWHEREGrade＜60；例查询年龄在20至23岁之间的学生的姓名、系别和年龄。SELECTSname,Sdept,SageFROMSWHERESageBETWEEN20and23例查询年龄不在20至23岁之间的学生姓名、系别和年龄。SELECTSname,Sdept,SageFROMSWHERESageNOTBETWEEN20AND23例查出信息系(IS)、数学系(MA)和计算机科学系(CS)的学生的姓名和性别。SELECTSname,SsexFROMSWHERESdeptIN(`IS',`MA',`CS')例查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname,SsexFROMSWHERESdeptNOTIN(`IS',`MA',`CS')例查询学号为95001的学生的详细情况。SELECT*FROMSWHERESnoLIKE‘95001'等价：SELECT*FROMSWHERESno=‘95001'例查所有姓刘的学生的姓名、学号和性别。SELECTSname,Sno,SsexFROMSWHERESnameLIKE‘刘%'例查姓“欧阳”且全名为3个汉字的学生的姓名。SELECTSnameFROMSWHERESnameLIKE‘欧阳__'；例查名字中第二个字为“阳”字的学生的姓名和学号。SELECTSname,SnoFROMSWHERESnameLIKE‘__阳%'；例查所有不姓刘的学生姓名。SELECTSname,FROMSWHERESnameNOTLIKE‘刘%'3例查DB_Design课程的课程号和学分。SELECTCno,CcreditFROMCourseWHERECnameLIKE‘DB\_Design'ESCAPE'＼'；例查以“DB_”开头，且倒数第2个字符为i的课程的详细情况。SELECT*FROMCourseWHERECnameLIKE'DB＼_%i_'ESCAPE'＼'例某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩，下面来查一下缺少成绩的学生的学号和相应的课程号。SELECTSno,CnoFROMSCWHEREGradeISNULL例查有成绩的学生学号和课程号。SELECTSno,CnoFROMSCWHEREGradeISNOTNULL例查CS系年龄在20岁以下的学生姓名。SELECTSnameFROMSWHERESdept='CS'ANDSage＜20例查出信息系(IS)、数学系(MA)和计算机科学系(CS)的学生的姓名和性别。SELECTSname,SsexFROMSWHERESdept='IS'ORSdept='MA'ORSdept='CS'等价：SELECTSname,SsexFROMSWHERESdeptIN(`IS',`MA',`CS')例查询学生总人数。SELECTCOUNT(*)FROMS例查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC例计算1号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno='1'例查询学习1号课程的学生最高分数。SELECTMAX(Grade)FROMSCWHERECno='1'4例查询各个课程号与相应的选课人数。SELECTCno,COUNT(Sno)FROMSCGROUPBYCno例查询信息系选修了3门以上课程的学生的学号。SELECTSnoFROMS，SCWHERESdept=”IS”ands.sno=sc.snoGROUPBYsc.SnoHAVINGCOUNT(*)＞3；例查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。SELECTSno,GradeFROMSCWHERECno=”3“ORDERBYGradeDESC；例查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。SELECT*FROMSORDERBYSdept,SageDESC；例查询男学生超过50人的年龄组，查询结果首先按人数升序，然后按年龄降序输出。SELECTsage,count(sno)FROMSwheressex=”男”groupbysagehavingcount(*)50ORDERBY2,SageDESC三.多表连接查询例查询学生情况及选修课程情况。SELECTS.*,SC.*FROMS,SCWHERES.Sno=SC.Sno例卡氏积连接SELECTS.*,SC.*FROMS,SC；例自然连接SELECTS.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMS,SCWHERES.Sno=Sc.Sno；例查询每一门课的间接先修课号。解:SELECTFIRST.Cno,SECOND.PcnoFROMCourseasFIRST,CourseasSECONDWHEREFIRST.Pcno=SECOND.Cno；5例查找至少选修了2号和4号课程的学生的学号解:SELECTFIRST.snoFROMSCasFIRST,SCasSECONDWHEREFIRST.Sno=SECOND.SnoandFIRST.cno=’2’andSECOND.cno=’4’例查询选修2号课程且成绩在90分以上的所有学生。SELECTS.Sno,SnameFROMS,SCWHERES.Sno=SC.SnoANDSC.Cno='2'ANDSC.Grade＞90例查询每个学生选修的课程名及成绩。SELECTS.Sno,Sname,Cname,GradeFROMS,SC,CourseWHERES.Sno=SC.SnoandSC.Cno=Course.Cno；例查询与“刘晨”同一个系的学生。解：SELECTSno,Sname,SdeptFROMSWHERESdeptIn(SELECTSdeptFROMSWHERESname='刘晨')等价：SELECTSno,Sname,SdeptFROMSasS1,SasS2WHERES1.Sdept=S2.SdeptANDS2.Sname='刘晨'；例查询选修了课程为“信息系统”的学生学号和姓名。SELECTSno,SnameFROMSWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname=’信息系统’))；等价：SELECTSno,SnameFROMS,SC,CourseWHERES.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=’信息系统’；例查找至少选修2号和4号两门课程的学生学号SELECTX.SnoFROMSCasXWHERE(‘2’,’4’)in(SELECTcnoFROMSCasYWHEREX.sno=Y.sno)(‘2’,’4’)作为一个集合1，（SELECTcno…）作为集合2，6对于in操作：集合1中每个元素都在集合2中，结果为真，否则为假；对于notin操作：集合1中某个元素不在集合2中，结果为真，否则为假；例查找至少不选修2号或4号课程的学生学号SELECTX.SnoFROMSCasXWHERE(‘2’,’4’)notin(SELECTcnoFROMSCasYWHEREX.sno=Y.sno)若为属性组，比较按字符串比较方式进行，例（a1,a2）＜=some{(b1,b2),(b3,b4),…}（a1,a2）分别与(b1,b2),(b3,b4)，…比较，每个比较为：（a1,a2）＜=some(b1,b2)比较等同于（a1b1）∨(（a1=b1）∧(a2=b2))例刘晨所在系的结果是一个唯一值：SELECTSno,Sname,SdeptFROMSWHERESdept=(SELECTSdeptFROMSWHERESname='刘晨')；例查询选修了课程为“信息系统”的学生学号和姓名。SELECTSno,SnameFROMSWHERESnoIN(SELECTSnoFROMSCWHERECno=(SELECTCnoFROMCourseWHERECname='信息系统'))例查找选修了2号课程的学生姓名SELECTSnameFROMSWHEREsno=some(SELECTsnoFROMSCWHEREcno='2')等价：SELECTSnameFROMSWHEREsnoin(SELECTsnoFROMSCWHEREcno='2')例查询比IS系某一学生年龄小的学生姓名。SELECTSname,SageFROMSWHERESage＜some(SELECTSageFROMSWHERESdept='IS')ANDSdept＜＞'IS'ORDERBYSageDESC;等价：7SELECTSname,SageFROMSWHERESage（SELECTMAX(Sage)FROMSWHERESdept='IS')ANDSdept＜＞'IS'ORDERBYSageDESC例查询比IS系所有学生年龄都小的学生姓名。SELECTSname,SageFROMSWHERESage＜ALL(SELECTSageFROMSWHERESdept='IS')ANDSdept＜＞'IS'ORDERBYSageDESC;等价：SELECTSname,SageFROMSWHERESage(SELECTMIN(Sage)FROMSWHERESdept='IS')ANDSdept＜＞'IS'ORDERBYSageDESC例查找平均成绩最高的学生学号SELECTsnoFROMSCGROUPBYsnoHAVINGavg(grade)=ALL(SELECTavg(grade)FROMSCGROUPBYsno)例4-45查询所有选修了1号课程的学生姓名。SELECTSnameFROMSWHEREEXISTS(SELECT*FROMSCWHERESno=S.SnoANDCno='1')等价：SELECTSnameFROMSWHERE‘1’in(SELECTcnoFROMSCWHERESno=S.Sno)等价：SELECTSnameFROMSWHEREsnoin(SELECTsnoFROMSCWHEREcno=’1’)等价：SELECTSnameFROMS,SCWHERESC.Sno=S.Snoandcno=‘1’例查询所有未修1号课程的学生姓名。SELECTSnameFROM