数据库上机习题及答案.docx
《数据库上机习题及答案.docx》由会员分享,可在线阅读,更多相关《数据库上机习题及答案.docx(14页珍藏版)》请在冰点文库上搜索。
数据库上机习题及答案
RevisedonNovember25,2020
数据库上机习题及答案
数据库及应用复习题
一、设计题
有一个[学生课程]数据库,数据库中包括三个表:
学生表Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成,记为:
Student(Sno,Sname,Ssex,Sage,Sdept),Sno为关键字。
课程表Course由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成,记为:
Course(Cno,Cname,Cpno,Ccredit)Cno为关键字。
成绩表SG由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成,记为:
SG(Sno,Cno,Grade)(SNO,CNO)为关键字。
用SQL语言实现下列功能:
1.建立学生表Student,其中学号属性不能为空,并且其值是唯一的。
2.向Student表增加“入学时间(Scome)”列,其数据类型为日期型。
3.查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列。
4.查询学习1号课程的学生最高分数、平均成绩。
5.查询与“李洋”在同一个系学习的学生。
6.将计算机系全体学生的成绩置零。
7.删除学号为05019的学生记录。
8.删除计算机系所有学生的成绩记录。
1.
CREATETABLEStudent
(SnoCHAR(5)NOTNULLUNIQUE,
SnameCHAR(20),
SsexCHAR
(2),
SageINT,
SdeptCHAR(15))
2.
ALTERTABLEStudentADDScomeDATETIME
3.
SELECTSno,Grade
FROMSG
WHERECno='3'
ORDERBYGradeDESC
4.
SELECTMAX(Grade),AVG(Grade)
FROMSC
WHERECno='1'
5.
SELECTSno,Sname,Sdept
FROMStudent
WHERESdeptIN
(SELECTSdeptFROMStudent
WHERESname='李洋')
6.UPDATESG
SETGrade=0
WHERESnoin
(SELECTSnoFROMStudent
WHERESdept='计算机系')
7.DELETEFROMStudent
WHERESno='05019'
8.DELETEFROMSG
WHERESnoin
(SELECTSnoFROMStudent
WHERESdept='计算机系')
二、设计题
现有关系数据库如下:
数据库名:
教师数据库
教师表(编号char(6),姓名,性别,民族,职称,身份证号)
课程表(课号char(6),名称)
任课表(ID,教师编号,课号,课时数)
用SQL语言实现下列功能的sql语句代码:
1.创建上述三表的建库、建表代码(14分);
要求使用:
主键(教师表.编号,课程表.课号)、外键(任课表.教师编号,任课表.课号)、默认(民族)、非空(民族,姓名)、唯一(身份证号)、检查(性别、课时数),自动编号(ID)
2.将下列课程信息添加到课程表的代码(6分)
课号课程名称
100001SQLServer数据库
100002数据结构
100003VB程序设计
修改课号为100003的课程名称:
VisualBasic程序设计
删除课号为100003的课程信息
3.写出创建[任课表视图](教师编号,姓名,课号,课程名称,课时数)的代码;(4分)
4.写出创建[某门课任课教师]内嵌表值函数以及检索的代码;(6分)
检索:
所有代'SQLServer数据库'这门课程的老师姓名;
5.写出创建[统计课时数]:
输出最大课时数、最低课时数、平均课时的存储过程以及执行代码;(6分)
6.写出创建:
计算某教师代课总课时,并将值返回的存储过程以及执行代码。
(6分)
执行:
计算“郭老师”的总课时。
(6分)
7.检索有一门或一门以上课程课时数大于90的所有教师的信息,包括编号、姓名。
(4分)
8.建一个规则,并将其绑定到教师表的职称列上,规定取值为('教授','副教授','讲师','助教')之一。
(4分)
1.
createdatabase[教师数据库]--(2分)
use[教师数据库]
go
createtable教师表--(6分)
([编号]char(6)primarykey,
[姓名]nchar(4)notnull,
[性别]nchar
(1)check([性别]in('男','女')),
[民族]nchar(8)default'汉族'notnull,
[职称]nchar(12),
[身份证号]char(18)unique
)
createtable课程表--(2分)
([课号]char(6)primarykey,
[名称]char(40)notnull
)
createtable任课表--(4分)
(IDIDENTITY(1,1),
[教师编号]char(6)references学生表(学号),
[课号]char(6)references课程表(课号),
[课时数]integercheck([课时数]between0and200)
)
2.
insert课程表values('100001','SQLServer数据库')
insert课程表values('100002','数据结构')
insert课程表values('100003','VB程序设计')
update课程表set名称='VisualBasic程序设计'where课号='100003'
delete课程表where课号='100003'
3.
createview[任课表视图]as
select教师编号,姓名,课号,课程名称,课时数from教师表,任课表
where教师表.编号=任课表.教师编号
4.
createfunction[某门课任课教师](@课程名varchar(15))
returnstableas
return(select课程名称,课时数,教师姓名=姓名from任课表视图
where课程名=@课程名)
go
select*from[某门课任课教师]('SQLServer数据库')
5.
createprocedure[统计课时数]
as
select最大课时数=max(课时),最小课时数=min(课时),平均课时数=avg(课时)from任课表
go
execute[统计课时]
6.
createprocedure[统计课时]
@教师名nchar(16),
as
begin
declare@总课时int
select@总课时=sum(课时)from任课表视图
where姓名=@教师名
end
go
execute[统计课时]'郭老师'
7.
select编号,姓名from教师表
where编号in(selectdistinct教师编号from任课表where课时数>=90)
8.
createrulezhicheng_rule
as@zhichengin('教授','副教授','讲师','助教')
go
sp_bindrulezhicheng_rule,'教师表.职称'
三、设计题(共50分)
现有关系数据库如下:
数据库名:
医院数据库
医生表(编号,姓名,性别,出生日期,职称)
病人表(编号,姓名,性别,民族,身份证号)
病历表(ID,病人编号,医生编号,病历描述)
用SQL语言实现下列功能的sql语句代码:
1.创建上述三表的建库、建表代码;
要求使用:
主键(师医生表.编号,病人表.课号)、外键(病历表.医生编号,病历表.病人课号)、非空(职称,姓名)、检查(性别),自动编号(ID)(16分)
2.将下列医生信息添加到医生表的代码
编号姓名性别出生日期职称
100001杜医生男1963-5-18副主任医师
100002郭医生女1950-7-26副主任医师
100003刘医生男1973-9-18医师
修改编号为100002的医生职称为‘主任医师’
删除编号为100003的医生信息(8分)
3.写出创建:
医疗表视图(医生编号,姓名,病人姓名,病历)的代码;(4分)
4.写出所有病人编号、姓名、病历、以及病人所对应的医生编号的查询语句;(4分)
7.写出创建:
输出某医生(根据医生编号即可)看病人数存储过程以及执行过程(要求输入医生姓名的参数,输出病人数)(6分)。
8.写出查询1970年以前出生的医生(4分)。
7.检索有病人的医生信息。
(4分)
8.创建一个默认,并将其绑定到医生表的成绩职称列上,默认值为“医师”。
(4分)
1.
createdatabase[医院数据库]
use[医院数据库]
go
createtable医生表
([编号]char(6)primarykey,
[姓名]nchar(4)notnull,
[性别]nchar
(1)check([性别]in('男','女')),
[出生日期]datetime,
[职称]char(18)notnull
)
createtable病人表
([课号]char(6)primarykey,
[姓名]nchar(4)notnull,
[性别]nchar
(1)check([性别]in('男','女')),
[民族]nchar(10),
[身份证号]char(18)notnull
)
createtable病历表
(IDIDENTITY(1,1),
[病人编号]char(6)references病人表(学号),
[医生编号]char(6)references医生表(编号),
[病历描述]varchar(1000)
)
2.
insert医生表values('100001','杜医生','男','1963-5-18','副主任医师')
insert医生表values('100002','郭医生','女','1950-7-26','副主任医师')
insert医生表values('100003','刘医生','男','1973-9-18','医师')
update医生表set职称='主任医师'where编号='100002'
delete医生表where编号='100003'
3.
createview[医疗表视图]as
selectbr.姓名,ys.姓名,yl.病历from病人表asbrjoin医疗表asyl
onbr.编号yl.病人编号
join医生表asys
onyl.医生编号=ys.编号
4.
Selectbr.编号,br.姓名,br.病历,yl.医生编号
from病人表brleftjoin医疗表yl
onbr.编号=yl.病人编号
5.
createprocedure[某医生看病人数]
@医生编号nchar(16),@人数intoutput
as
select@人数=count(病人编号)
from病人表
where医生编号=@医生编号
go
declare@TOTALint
execute[某医生看病人数]'100001',@TOTAL
6
select*from医生表where出生日期<'1970-01-01'
9.
select编号,姓名,性别,出生日期,职称from医生表
where编号in(select医生编号from医疗表)
8.
createdefaultzhichengAS'医师'
go
execsp_bindefault'zhicheng','医生表.职称'
四、设计题(共50分)
现有关系数据库如下:
数据库名:
学生选课数据库
学生表(学号,姓名,性别,年龄,专业)
课程表(课程号,课程名,学分,先行课程号)
选课表(ID,学号,课程号,分数)
用SQL语言实现下列功能的sql语句代码:
1.创建数据库[学生选课数据库]代码(2分);
2.创建[课程表]代码(2分);
课程表(课程号char(6),课程名,学分,先行课程号)
要求使用:
主键(课程号)、非空(课程名,学分)
3.创建[学生表]代码(6分);
学生表(学号char(6),姓名,性别,年龄,专业)
要求使用:
主键(学号)、非空(姓名,专业)、检查(性别)
4.创建[选课表]代码(4分);
选课表(ID,学号,课号,分数)
要求使用:
外键(选课表.学号,选课表.课程号)、检查(分数),自动编号(ID)
5.将下列课程信息添加到课程表的代码(8分)
课程号课程名学分先行课程号
100001C语言2
100002数据结构2100001
100003数据库原理2
修改课程号为100003的课程名:
SQL数据库
删除课程号为100002的课程信息
6.写出创建:
选课表视图(学号,姓名,课程号,课程名,学分,分数)的代码;(4分)
7.写出创建:
某门课程成绩内嵌表值函数以及检索的代码;(6分)
检索:
所有修SQL数据库这门学生的成绩;
8.写出创建:
某门课程高低均分计算某门课程成绩最高分、最低分、平均分存储过程以及执行的代码;(6分)
执行:
所有修SQL数据库这门学生的最高分、最低分、平均分;
9.检索姓张的女同学的情况:
姓名、学号、专业。
(2分)
10.检索有一门或一门以上课程成绩小于60分的所有学生的信息,包括学号、姓名。
(4分)
1.
createdatabase[学生选课数据库]
go
use[学生选课数据库]
go
2.
createtable学生表
([学号]char(6)primarykey,
[姓名]nchar(4)notnull,
[性别]nchar
(1)check([性别]in('男','女')),
[年龄]numeric
(2),
[专业]char(18)notnull
)
3.
createtable课程表
([课程号]char(6)primarykey,
[课程名]char(40)notnull,
[学分]numeric
(2)notnull,
[先行课程号]char(6)
)
4.
createtable选课表
(IDIDENTITY(1,1),
[学号]char(6)references学生表(学号),
[课程号]char(6)references课程表(课程号),
[分数]integercheck([分数]between0and100)
)
5.
insert课程表values('100001','C语言',2)
insert课程表values('100002','数据结构',2,'100001')
insert课程表values('100003','数据库原理',2)
update课程表set课程名='SQL数据库'where课程号='100003'
delete课程表where课程号='100002'
6.createview[选课表视图]as
select选课表.学号,姓名,选课表.课程号,课程名,学分,分数
from选课表,学生表,课程表
where选课表.学号=学生表.学号
and选课表.课程号=课程表.课程号
--或
createview[选课表视图]as
select选课表.学号,姓名,选课表.课程号,课程名,学分,分数
from选课表join学生表on选课表.学号=学生表.学号
join课程表on选课表.课程号=课程表.课程号
7.
createfunction[某门课程成绩](@课程名varchar(40))
returnstableas
return(select学号,姓名,课程名,学分,分数from选课表视图
where课程名=@课程名)
go
select*from[某门课程成绩]('SQL数据库')
8.
createprocedure[某门课程高低均分]
@课程名varchar(40)as
select课程名,最高分=max(分数)、最低分=min(分数)、平均分=avg(分数)
from选课表视图
where课程名=@课程名
go
execute[某门课程高低均分]'SQL数据库'
9.
select姓名,学号,专业from学生表
where姓名like'张%'and性别='女'
10.
select学号,姓名,专业from学生表where学号in
(selectdistinct学号from选课表where分数<60)