关系数据库操作语言SQL四真题含答案与解析交互.docx
《关系数据库操作语言SQL四真题含答案与解析交互.docx》由会员分享，可在线阅读，更多相关《关系数据库操作语言SQL四真题含答案与解析交互.docx（26页珍藏版）》请在冰豆网上搜索。
关系数据库操作语言SQL四真题含答案与解析交互
关系数据库操作语言SQL（四）
（总分100,做题时间90分钟）
设计题
在SQLServer2000中，有教师表Teachers（TeacherID，Name,LeaderID），其中TeacherID是主码，类型是长度为4的普通编码定长字符串，且每位是0～9的数字字符；Name的类型是长度为10的普通编码可变长字符串；LeaderID是每个教师的领导的TeacherID。
1.
写出创建Teachers表的SQL语句，要求语句中包含所有的约束。
分值:
2.5
答案:
createtableTeachers（
TeacherIDchar（4）check（TeacherIDlike"[0～9][0～9][0～9][0～9]"）,
Namevarchar（10）,
LeaderIDchar（4）,
primarykeyTeacherID,
foreignkeyLeaderIDreferencesTeachers（TeacherID）
）
[解析]创建表格使用CREATE语句。
2.
现要查询TeacherID为“1234”的教师的领导的领导的TeacherID，请给出相应的SQL语句，要求只使用一条SQL语句实现，且此语句中不允许包含子查询。
分值:
2.5
答案:
selectT2.LeaderIDfromTeachersasT1joinTeachersasT2onT1.
LeaderID=T2.TeacherIDwhereT1.TeacherID='1234'
3.
如下两个关系表：
Emp（eid,ename,age,did,salary），其各列含义为：
职工号，姓名，年龄，部门号，工资。
Dept（did,dname,mgr_id），其各列含义为：
部门号，部门名称，部门经理职工号。
写出一条SQL语句，查询工资大于10000，且与其所在部门的经理年龄相同的职工姓名。
分值:
2.5
答案:
selecta.*fromEmpa,Deptb,Empcwherea.did=b.didandb.mgr_id=c.eidanda.salary＞10000anda.age=c.age
4.
写出创建下述关系表的SQL语句。
Student表结构
列名
含义
数据类型
约束
Sno
学号
普通编码定长字符串，长度为7
主码
Sname
姓名
普通编码定长字符串，长度为10
非空
Ssex
性别
普通编码定长字符串，长度为2
取值范围为：
{男，女}
Sage
年龄
微整型
大于等于14
Sdept
所在系
普通编码不定长字符串，长度为20
Course表结构
列名
含义
数据类型
约束
Cno
课程号
普通编码定长字符串，长度为10
主码
Cname
课程名
普通编码不定长字符串，长度为20
非空
Periods
学时数
小整型
大于0
Property
课程性质
普通编码定长字符串，长度为4
取值范围为：
{必
修，选修}
分值:
2.5
答案:
创建Student表的SQL语句为：
CREATETABLEStudent（
Snochar（7）primarykey,
Snamechar（10）notnull,
Ssexchar
（2）check（SsexIn（'男','女'））,
Sagetinyintcheck（Sage＞=14）,
Sdeptvarchar（20）
）
创建Course表的SQL语句为：
CREATETABLECourse（
Cnochar（10）primarykey,
Cnamevarchar（20）notnull,
Periodssmallintcheck（Periods＞0）,
Propertychar（4）check（Propertyin（'必修','选修'））
）
[解析]本题考查的是SQL语句。
存在如下表结构：
Student表结构
列名
含义
数据类型
约束
Sno
学号
普通编码定长字符串，长度为7
主码
Sname
姓名
普通编码定长字符串，长度为10
非空
Ssex
性别
普通编码定长字符串，长度为2
取值范围为：
{男，女}
Sage
年龄
微整型
大于等于14
Sdept
所在系
普通编码不定长字符串，长度为20
Course表结构
列名
含义
数据类型
约束
Cno
课程号
普通编码定长字符串，长度为10
主码
Cname
课程名
普通编码不定长字符串，长度为20
非空
Periods
学时数
小整型
大于0
Property
课程性质
普通编码定长字符串，长度为4
取值范围为：
{必
修，选修}
SC表结构
列名
含义
数据类型
约束
Sno
学号
普通编码定长字符串，长度为7
主码，引用Student的外码
Cno
课程号
普通编码定长字符串，长度为10
主码，引用Course的外码
Grade
成绩
小整型
取值范围：
0～100
写出实现下述操作的SQL语句：
5.
查询选课门数超过2门的学生的平均成绩和选课门数。
分值:
2.5
答案:
selectsno,sum（grade）as/总成绩，avg（grade）as/平均成绩，count（*）as/选课门数fromsc
groupbysnohavingcount（*）＞2
[解析]本题考查的是SQL操作。
6.
列出总成绩超过200分的学生，要求列出学号、总成绩。
分值:
2.5
答案:
selectsno,sum（grade）总成绩fromsc
groupbysnohavingsum（grade）＞200
7.
查询选修了“C02”号课程的学生的姓名和所在系。
分值:
2.5
答案:
selectsname,sdeptfromStudentjoinSConStudent.Sno=SC.Sno
**o='C02'
8.
查询成绩在80分以上的学生的姓名、课程名和成绩，并将结果按成绩的降序排列。
分值:
2.5
答案:
selectsname,cno,grade
fromstudentsjoinscons.sno=sc.sno
wheregrade＞80
Orderbygradedesc
9.
查询计算机系男生修了“数据库基础”的学生的姓名、姓名和成绩。
分值:
2.5
答案:
selectsname，ssex,grade
fromstudentsjoinscons.sno=sc.sno
joincourseconc.cno=sc.cno
wheresdept='计算机系'andssex='男'
**ame='数据库基础'
10.
查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号和考试成绩。
分值:
2.5
答案:
selects.sno,sname,cno,gradefromStudentsleftjoinSC
ons.Sno=SC.Sno
11.
列出“数据库基础”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。
分值:
2.5
答案:
selecttop3s.sno,sname,sdept,grade
fromStudentsjoinscons.Sno=sc.Sno
joinCourseconc.Cno=sc.Cno
**ame='数据库基础'
orderbygradedesc
12.
查询哪些学生合选了一门课程，要求列出合选课程的学生的学号和课程号。
分值:
2.5
答案:
selectt1.sno,t2.sno,o
fromscast1joinscast2
ont1.cno=t2.cno
wheret1.sno＜t2.sno
13.
查询哪些课程没人选，要求列出课程号和课程名。
分值:
2.5
答案:
selectc.cno,cnamefromcoursecleftjoinsc
onc.cno= sc.cno
wheresc.cnoisnull
14.
查询计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程号和考试成绩。
分值:
2.5
答案:
selectsname,cname,grade
fromstudentsjoinscons.sno=sc.sno
joincoursecono=sc.cno
wheresdept='计算机系'
andgrade＞（selectavg（grade）fromsc）
15.
查询VB考试成绩最低的学生的姓名、所在系和VB成绩。
分值:
2.5
答案:
selectsname,sdept,grade
fromstudentsjoinscons.sno=sc.sno
joincourseconc.cno=sc.cno
wheregrade=（
selectmin（grade）fromsc
**oin（
**ofromcourse**ame='vb'））
**ame='vb'
16.
查询选修了VB课程的学生的学号、姓名、所在系和成绩，并对所在系进行如下处理：
当所在系为“计算机系”时，显示“CS”；当所在系为“信息系”时，显示“IS”；当所在系为“数学系”时，显示“MA”；对其他系，均显示“OTHER”。
分值:
2.5
答案:
selects.sno学号,sname姓名,
casesdept
when'计算机系'then'CS'
when'信息系'then'IS'
when'数学系'then'MA'
else'OTHER'
endas所在系,grade成绩
fromstudentsjoinscons.sno=sc.sno
joincourseconc.cno=sc.cno**ame='vb'
17.
查询每门课程的选课人数，如果选课人数超过了100人，显示“人多”；如果选课人数在40～100之间，显示“一般”；如果选课人数少于40人，显示“较少”；如果此门课程没人选，显示“无人选”。
分值:
2.5
答案:
selectc.cno,
case
whencount（sc.cno）＞100then'人多'
whencount（sc.cno）between40and100then'一般'
whencount（c.cno）＜40then'较少'
whencount（sc.cno）=0then'无人选'
endas
选课人数
fromscrightjoincourseconsc.cno=c.cnogroupbyc.cno
18.
在课程表中增加一条新记录：
课程号为“C100”，课程名为“Java”，学时数32，课程性质为“选修”。
分值:
2.5
答案:
insertintoCoursevalues（'C100','Java',32,'选修'）
19.
删除修课成绩小于50分的学生的修课记录。
分值:
2.5
答案:
deletefromscwheregrade＜50
20.
删除信息系修课成绩小于50分的学生的修课记录。
分值:
2.5
答案:
（a）用连接查询实现
deletefromscfromscjoinstudentsons.sno=sc.sno
wheresdept='信息系'andgrade＜50
（b）用子查询实现
deletefromscwheresnoin（
selectsnofromstudentwheresdept='信息系'）
andgrade＜50
21.
将所有选修了“C01”号课程的学生的成绩加10分。
分值:
2.5
答案:
updatescsetgrade=grade+10
**o='c01'
22.
将计算机系所有选修了“计算机文化学”课程的学生的成绩加10分。
分值:
2.5
答案:
（a）用子查询实现
updatescsetgrade=grade+10
wheresnoin（
selectsnofromstudentwheresdept='计算机系'）
**oin（
**ofromcourse**ame='计算机文化学'）
（b）用连接实现
updatescsetgrade=grade-10
fromstudentsjoinscons.sno=sc.sno
joincourseconc.cno=sc.cno
wheresdept='计算机系'**ame='计算机文化学'
有如下关系数据库：
学生（学号，姓名，性别，专业，奖学金）
课程（课程号，名称，学分）
学习（学号，课程号，分数）
用SQL语言完成下列操作：
23.
检索没有任何一门课程成绩在80分以下的所有学生的信息，包括学号、姓名和专业；
分值:
2.5
答案:
SELECT学号，姓名，专业FROM学生WHERE学号NOTIN（SELECT学号FROM学习WHERE分数＜80）；
24.
检索没有获得奖学金同时至少有一门课程成绩在95分以上的学生信息，包括学号、姓名和专业；
分值:
2.5
答案:
SELECT学号，姓名，专业FROM学生，学习WHERE学生.学号=学习.学号AND学习.课程号=课程.课程号AND学生.奖学金＜=0AND学习.分数＞95；
25.
对成绩得过100分的学生，如果没有获得奖学金的，将其奖学金设为1000元；
分值:
2.5
答案:
UPDATE学生SET奖学金=1000WHERE奖学金＜=0AND学号IN（SELECT学号FROM学习WHERE分数=100）；
26.
定义学生成绩得过满分（100分）的课程视图AAA，包括课程号、名称和学分。
分值:
2.5
答案:
CREATEVIEWAAA（课程号，名称，学分）ASSELECT课程号，名称，学分FROM课程WHERE课程号IN（SELECT课程号FROM学习WHERE分数=100）；
现有“学生-选课-课程”关系数据库如下：
学生表S（Sno,Sname,Sage,Ssex,Sdept），各属性含义依次为学号，姓名，年龄，性别，所在系；
选课表SC（Sno,Cno,Grade），各属性含义依次为学号，课程号，成绩；
课程表C（Cno,Cname,Cteacher），各属性含义依次为课程号，课程名，授课教师；
试用SQL语言完成以下操作：
27.
检索年龄在18到22之间（含18和22）的男生的学号、姓名及年龄；
分值:
2.5
答案:
方法1：
SELECTSno,Sname,Sage
FROMS
WHERESageBETWEEN18AND22
ANDSsex='男'
方法2：
SELECTSno,Sname,Sage
FROMS
WHERESage＞=18ANDSage＜=22
ANDSsex='男'
28.
检索选修了“王杰”老师所授课程的学生的学号和姓名。
分值:
2.5
答案:
方法1：
SELECTSno,Sname
FROMS,SC,C
WHERES.Sno=SC.Sno
ANDSC.Cno=C.Cno
ANDC.Cteacher='王杰'
方法2：
SELECTSno,SnameFROMSWHERESnoIN
（SELECTSnoFROMSCWHERECnoIN
（SELECTCnoFROMCWHERECteacher='王杰'））
学生管理数据库包括3个基本表，其结构为：
学生（学号，姓名，年龄，所在系）
课程（课程表，课程号）
选课（学号，课程号，成绩）
试用SQL语言完成操作：
29.
求数学系或计算机系姓张的学生信息；
分值:
2.5
答案:
select*from学生where所在系in（'数学系'，'计算机系'）and姓名like'张%'
30.
求学生人数；
分值:
2.5
答案:
selectcount（*）as学生人数from学生
31.
求选修高等数学的学生人数；
分值:
2.5
答案:
selectcount（*）from学生，课程，选课where学生.学号=选课.学号and选课.课程号=课程.课程号and课程.课程表='高等数学'
32.
求选修了高等数学的学生学号和姓名；
分值:
2.5
答案:
select学号，姓名from选课innerjoin课程
on（选课.课程号=课程.课程号）innerjoin学生
on（选课.学号=学生.学号）
where课程号='高等数学'
33.
求其他系中比计算机系学生年龄都小的学生；
分值:
2.5
答案:
select姓名，年龄from学生where所在系notin（'计算机系'）
and年龄＜（selectmin（年龄）from学生where所在系='计算机系'）
34.
求选修课程号为C1的课程且成绩为90分以上的学生学号、姓名及成绩。
分值:
2.5
答案:
select学号，姓名，成绩from选课innerjoin课程
on（选课.课程号=课程.课程号）innerjoin学生
on（选课.学号=学生.学号）
where课程号='C1'and成绩＞90
已知关系模式：
S（SNO,SNAME）学生关系，SNO为学号，SNAME为姓名；C（CNO,CNAME,CTEACHER）课程关系，CNO为课程号，CNAME为课程名，CTEACHER为任课教师；SC（SNO,CNO,SCGRADE）选课关系，SCGRADE为成绩。
35.
找出没有选修过“李明”老师讲授课程的所有学生姓名；
分值:
1.5
答案:
SELECTSNAMEFROMS
WHERENOTEXISTS（
SELECT*FROMSC,C
WHERESC.CNO=C.CNO
ANDCNAME='李明'
ANDSC.SNO=S.SNO）
36.
列出有两门以上（含两门）不及格课程的学生姓名及其平均成绩；
分值:
1.5
答案:
selects.sno,avg（scGrade）fromS,SC
wheres.snoin（
selectsc.snofromsc
innerJoinc
onc.cno=sc.cno
wheresc.scGrade＜60
groupbysno
havingcount（*）＞=2）
ands.sno=sc.sno
groupbys.sno
37.
列出既学过“1”号课程，又学过“2”号课程的所有学生姓名。
分值:
1.5
答案:
selectS.SNO,S.SNAMEfromsc,s
**o=1andsc.snoin（selectsnofromsc**o=2）ands.sno=sc.sno
38.
在SQLServer2000中，设某数据库中有商品表（商品号，商品名，进货价格），商品号为主码；销售表（商品号，销售时间，销售数量，销售价格，本次利润），商品号和销售时间为主码，销售价格为本次销售商品的单价。
现要求每当在销售表中插入前4列数据时（假设一次只插入一行数据），系统自动计算本次销售产生的利润，并将该利润赋给销售表的第5列“本次利润”。
请编写实现上述功能的后触发型触发器代码。
分值:
1.5
答案:
CREATETRIGGERupdate_sale
ON销售表FORUPDATE
FOREACHROW
ASBEGIN
UPDATETable_saleSET本次利润=销售价格-（SELECT进货价格FROM产品表）
END
CREATETRIGGERInsert_sale
ON销售表FORINSERT
FOREACHROW
升级会员 