数据库5版SQL例题解答.docx
- 文档编号:11355473
- 上传时间:2023-05-31
- 格式:DOCX
- 页数:28
- 大小:24.73KB
数据库5版SQL例题解答.docx
《数据库5版SQL例题解答.docx》由会员分享,可在线阅读,更多相关《数据库5版SQL例题解答.docx(28页珍藏版)》请在冰点文库上搜索。
数据库5版SQL例题解答
SQL例题
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-Sage
FROMS
例SELECTSnameasNAME,‘YearofBirth:
'asBIRTH,2009-SageasBIRTHTYEAR,ISLOWER(Sdept)asDEPARTMENTFROMS
例SELECTSnoFROMSC
例SELECTallSnoFROMSC
例SELECTdistinctSnoFROMSC
例查计算机系全体学生的姓名。
SELECTSnameFROMSWHERESdept=‘CS’
例查所有年龄在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‘刘%'
例查DB_Design课程的课程号和学分。
SELECTCno,CcreditFROMCourseWHERECnameLIKE‘DB\_Design'ESCAPE'\';
例查以“DB_”开头,且倒数第2个字符为i的课程的详细情况。
SELECT*FROMCourse
WHERECnameLIKE'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'
例查询各个课程号与相应的选课人数。
SELECTCno,COUNT(Sno)FROMSCGROUPBYCno
例查询信息系选修了3门以上课程的学生的学号。
SELECTSnoFROMS,SCWHERESdept=”IS”ands.sno=sc.sno
GROUPBYsc.SnoHAVINGCOUNT(*)>3;
例查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列。
SELECTSno,GradeFROMSCWHERECno=”3“ORDERBYGradeDESC;
例查询全体学生情况,查询结果按所在系升序排列,对同一系中的学生按年龄降序排列。
SELECT*FROMSORDERBYSdept,SageDESC;
例查询男学生超过50人的年龄组,查询结果首先按人数升序,然后按年龄降序输出。
SELECTsage,count(sno)FROMS
wheressex=”男”
groupbysagehavingcount(*)>50
ORDERBY2,SageDESC
三.多表连接查询
例查询学生情况及选修课程情况。
SELECTS.*,SC.*FROMS,SC
WHERES.Sno=SC.Sno
例卡氏积连接
SELECTS.*,SC.*FROMS,SC;
例自然连接
SELECTS.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMS,SC
WHERES.Sno=Sc.Sno;
例查询每一门课的间接先修课号。
解:
SELECTFIRST.Cno,SECOND.Pcno
FROMCourseasFIRST,CourseasSECOND
WHEREFIRST.Pcno=SECOND.Cno;
例查找至少选修了2号和4号课程的学生的学号
解:
SELECTFIRST.sno
FROMSCasFIRST,SCasSECOND
WHEREFIRST.Sno=SECOND.SnoandFIRST.cno=’2’andSECOND.cno=’4’
例查询选修2号课程且成绩在90分以上的所有学生。
SELECTS.Sno,Sname
FROMS,SC
WHERES.Sno=SC.SnoANDSC.Cno='2'ANDSC.Grade>90
例查询每个学生选修的课程名及成绩。
SELECTS.Sno,Sname,Cname,Grade
FROMS,SC,Course
WHERES.Sno=SC.SnoandSC.Cno=Course.Cno;
例查询与“刘晨”同一个系的学生。
解:
SELECTSno,Sname,SdeptFROMS
WHERESdeptIn(SELECTSdeptFROMSWHERESname='刘晨')
等价:
SELECTSno,Sname,SdeptFROMSasS1,SasS2
WHERES1.Sdept=S2.SdeptANDS2.Sname='刘晨';
例查询选修了课程为“信息系统”的学生学号和姓名。
SELECTSno,SnameFROMSWHERESnoIN
(SELECTSnoFROMSCWHERECnoIN
(SELECTCnoFROMCourseWHERECname=’信息系统’));
等价:
SELECTSno,SnameFROMS,SC,Course
WHERES.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=’信息系统’;
例查找至少选修2号和4号两门课程的学生学号
SELECTX.SnoFROMSCasX
WHERE(‘2’,’4’)in(SELECTcnoFROMSCasY
WHEREX.sno=Y.sno)
(‘2’,’4’)作为一个集合1,(SELECTcno…)作为集合2,
对于in操作:
集合1中每个元素都在集合2中,结果为真,否则为假;对于notin操作:
集合1中某个元素不在集合2中,结果为真,否则为假;
例查找至少不选修2号或4号课程的学生学号
SELECTX.SnoFROMSCasX
WHERE(‘2’,’4’)notin(SELECTcnoFROMSCasY
WHEREX.sno=Y.sno)
若为属性组,比较按字符串比较方式进行,例
(a1,a2)<=some{(b1,b2),(b3,b4),…}
(a1,a2)分别与(b1,b2),(b3,b4),…比较,每个比较为:
(a1,a2)<=some(b1,b2)比较等同于(a1 例刘晨所在系的结果是一个唯一值: SELECTSno,Sname,SdeptFROMS WHERESdept=(SELECTSdeptFROMSWHERESname='刘晨'); 例查询选修了课程为“信息系统”的学生学号和姓名。 SELECTSno,SnameFROMS WHERESnoIN(SELECTSnoFROMSC WHERECno=(SELECTCnoFROMCourse WHERECname='信息系统')) 例查找选修了2号课程的学生姓名 SELECTSnameFROMS WHEREsno=some(SELECTsnoFROMSCWHEREcno='2') 等价: SELECTSnameFROMS WHEREsnoin(SELECTsnoFROMSCWHEREcno='2') 例查询比IS系某一学生年龄小的学生姓名。 SELECTSname,SageFROMS WHERESage<some(SELECTSageFROMS WHERESdept='IS') ANDSdept<>'IS'ORDERBYSageDESC; 等价: SELECTSname,SageFROMS WHERESage<(SELECTMAX(Sage)FROMS WHERESdept='IS') ANDSdept<>'IS'ORDERBYSageDESC 例查询比IS系所有学生年龄都小的学生姓名。 SELECTSname,SageFROMS WHERESage<ALL(SELECTSageFROMS WHERESdept='IS') ANDSdept<>'IS'ORDERBYSageDESC; 等价: SELECTSname,SageFROMS WHERESage<(SELECTMIN(Sage)FROMSWHERESdept='IS') ANDSdept<>'IS'ORDERBYSageDESC 例查找平均成绩最高的学生学号 SELECTsnoFROMSC GROUPBYsno HAVINGavg(grade)>=ALL(SELECTavg(grade)FROMSC GROUPBYsno) 例4-45查询所有选修了1号课程的学生姓名。 SELECTSnameFROMS WHEREEXISTS(SELECT*FROMSC WHERESno=S.SnoANDCno='1') 等价: SELECTSnameFROMS WHERE‘1’in(SELECTcnoFROMSCWHERESno=S.Sno) 等价: SELECTSnameFROMS WHEREsnoin(SELECTsnoFROMSCWHEREcno=’1’) 等价: SELECTSnameFROMS,SC WHERESC.Sno=S.Snoandcno=‘1’ 例查询所有未修1号课程的学生姓名。 SELECTSnameFROMS WHERENOTEXISTS(SELECT*FROMSC WHERESno=S.SnoANDCno='1') 等价: SELECTSnameFROMS WHEREsnonotin(SELECTsnoFROMSCWHERECno='1') 例查询与“刘晨”同一个系的学生。 SELECTSno,Sname,SdeptFROMSasS1 WHEREEXISTS(SELECT*FROMSasS2WHERES2.Sdept=S1.SdeptANDS2.Sname='刘晨'); 例对全称量词和蕴函运算的处理,给定集合A={a1,a2,…,an} x(p(x))≡┑x(┑(p(x)) x(P(x))=P(a1)∧P(a2)∧…∧P(an) x(P(x))=P(a1)∨P(a2)∨…∨P(an) 例查询选修了全部课程的学生姓名。 关系代数: Πsname((Πsno,cno(SC)÷Πcno(course))∞s) 查询这样的学生姓名,不存在一门课程是他不选的。 SELECTSnameFROmS WHERENOTEXISTS(SELECT*FROMCourse WHERENOTEXISTS(SELECT*FROMSC WHERESno=S.SnoANDCno=Course.Cno)) 分析: ┑x((p(x))〈=〉┑[P(a1)∨P(a2)∨…∨P(an)] 〈=〉┑p(a1)∧┑p(a2)∧…∧┑p(an) 对于下面的样表,SC为集合,p(x)谓词为: Sno=S.SnoANDCno=Course.Cno。 s course SC sno sname ssex sage sdept cno cname cpno Ccredit sno cno grade 98001 张平 男 19 计算机 1 数据库 5 4 98001 1 90 98002 王欣 女 19 计算机 2 数学 6 98001 2 86 98003 李华 女 20 数学 3 软件工程 5 4 98001 5 92 98004 赵岩 男 18 外语 4 操作系统 7 4 98001 6 80 5 数据结构 7 4 98001 4 89 6 网络 4 3 98001 3 67 7 C语言 3 98001 7 87 98002 5 80 98002 1 77 98002 3 94 s sno sname ssex sage sdept 98001 张平 男 19 计算机 98002 王欣 女 19 计算机 98003 李华 女 20 数学 98004 赵岩 男 18 外语 course cno cname cpno Ccredit 1 数据库 5 4 2 数学 6 3 软件工程 5 4 4 操作系统 7 4 5 数据结构 7 4 6 网络 4 3 7 C语言 3 SC sno cno grade 98001 1 90 98001 2 86 98001 5 92 98001 6 80 98001 4 89 98001 3 67 98001 7 87 98002 5 80 98002 1 77 98002 3 94 (1)当s定位在第一个元组时,sno=“98001”,course定位在第一个记录,cno=“1”,内层的NOTEXISTS对SC的所有10个记录,形成谓词表达式: [第一门课,数据库]= ┑(s.98001=SC.98001andcourse.1=SC.1)∧ ┑(s.98001=SC.98001andcourse.1=SC.2)∧ ┑(s.98001=SC.98001andcourse.1=SC.5)∧ ┑(s.98001=SC.98001andcourse.1=SC.6)∧ ┑(s.98001=SC.98001andcourse.1=SC.4)∧ ┑(s.98001=SC.98001andcourse.1=SC.3)∧ ┑(s.98001=SC.98001andcourse.1=SC.7)∧ ┑(s.98001=SC.98002andcourse.1=SC.5)∧ ┑(s.98001=SC.98002andcourse.1=SC.1)∧ ┑(s.98001=SC.98002andcourse.1=SC.3) course定位在第二个记录,形成谓词表达式: [第二门课,数学]= ┑(s.98001=SC.98001andcourse.2=SC.1)∧ ┑(s.98001=SC.98001andcourse.2=SC.2)∧ ┑(s.98001=SC.98001andcourse.2=SC.5)∧ ┑(s.98001=SC.98001andcourse.2=SC.6)∧ ┑(s.98001=SC.98001andcourse.2=SC.4)∧ ┑(s.98001=SC.98001andcourse.2=SC.3)∧ ┑(s.98001=SC.98001andcourse.2=SC.7)∧ ┑(s.98001=SC.98002andcourse.2=SC.5)∧ ┑(s.98001=SC.98002andcourse.2=SC.1)∧ ┑(s.98001=SC.98002andcourse.2=SC.3) [第三门课,。 。 。 ]=。 。 。 [第四门课,。 。 。 ]=。 。 。 [第五门课,。 。 。 ]=。 。 。 [第六门课,。 。 。 ]=。 。 。 [第七门课,。 。 。 ]=。 。 。 (2)外层的NOTEXISTS,形成谓词表达式: 学生‘98001’选中=┑[第一门课,。 。 。 ]∧┑[第二门课,。 。 。 ]∧┑[第三门课,。 。 。 ]∧┑[第四门课,。 。 。 ]∧┑[第五门课,。 。 。 ]∧┑[第六门课,。 。 。 ]∧┑[第七门课,。 。 。 ]= [(s.98001=SC.98001andcourse.1=SC.1)∨ (s.98001=SC.98001andcourse.1=SC.2)∨ (s.98001=SC.98001andcourse.1=SC.5)∨ (s.98001=SC.98001andcourse.1=SC.6)∨ (s.98001=SC.98001andcourse.1=SC.4)∨ (s.98001=SC.98001andcourse.1=SC.3)∨ (s.98001=SC.98001andcourse.1=SC.7)∨ (s.98001=SC.98002andcourse.1=SC.5)∨ (s.98001=SC.98002andcourse.1=SC.1)∨ (s.98001=SC.98002andcourse.1=SC.3)]∧ [(s.98001=SC.98001andcourse.2=SC.1)∨ (s.98001=SC.98001andcourse.2=SC.2)∨ (s.98001=SC.98001andcourse.2=SC.5)∨ (s.98001=SC.98001andcourse.2=SC.6)∨ (s.98001=SC.98001andcourse.2=SC.4)∨ (s.98001=SC.98001andcourse.2=SC.3)∨ (s.98001=SC.98001andcourse.2=SC.7)∨ (s.98001=SC.98002andcourse.2=SC.5)∨ (s.98001=SC.98002andcourse.2=SC.1)∨ (s.98001=SC.98002andcourse.2=SC.3)]∧ […(s.98001=SC.98001andcourse.3=SC.3)∨…]∧
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 SQL 例题 解答