数据库5版SQL例题解答1.docx
《数据库5版SQL例题解答1.docx》由会员分享，可在线阅读，更多相关《数据库5版SQL例题解答1.docx（24页珍藏版）》请在冰豆网上搜索。
数据库5版SQL例题解答1
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）∨…]∧
[…
升级会员 