数据库上机习题及答案.pdf

数据库及应用复习题数据库及应用复习题 一、设计题一、设计题 有一个学生课程数据库，数据库中包括三个表： 学生表 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 CREATETABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20), Ssex CHAR(2), Sage INT, Sdept CHAR(15) 2 ALTER TABLE Student ADD Scome DATETIME 3 SELECT Sno, Grade FROM SG WHERE Cno=3 ORDER BY Grade DESC 4 SELECT MAX(Grade), AVG(Grade) FROM SC WHERE Cno=1 5 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname=李洋) 6 UPDATE SG SET Grade=0 WHERE Sno in ( SELECT Sno FROM Student WHERE Sdept = 计算机系) 7DELETE FROM Student WHERE Sno=05019 8 DELETE FROM SG WHERE Sno in ( SELECT Sno FROM Student WHERE Sdept = 计算机系) 二、设计题二、设计题 现有关系数据库如下： 数据库名：教师数据库 教师表(编号 char(6)，姓名，性别，民族，职称，身份证号) 课程表(课号 char(6)，名称) 任课表(ID，教师编号,课号，课时数) 用 SQL 语言实现下列功能的 sql 语句代码： 1. 创建上述三表的建库、建表代码(14 分)； 要求使用：主键(教师表.编号，课程表.课号)、外键(任课表.教师编号， 任课表.课号)、 默认(民族)、 非空(民族， 姓名)、 唯一(身份证号)、 检查(性 别、课时数),自动编号(ID) 2. 将下列课程信息添加到课程表的代码(6 分) 课号课程名称 100001SQL Server 数据库 100002数据结构 100003VB 程序设计 修改课号为 100003 的课程名称：Visual Basic 程序设计 删除课号为 100003 的课程信息 3. 写出创建任课表视图(教师编号，姓名，课号，课程名称，课时数)的代 码；(4 分) 4. 写出创建某门课任课教师内嵌表值函数以及检索的代码；(6 分) 检索：所有代SQL Server 数据库这门课程的老师姓名； 5. 写出创建统计课时数：输出最大课时数、最低课时数、平均课时的存储 过程以及执行代码；(6 分) 6. 写出创建：计算某教师代课总课时，并将值返回的存储过程以及执行代码。 (6 分) 执行：计算“郭老师”的总课时。(6 分) 7. 检索有一门或一门以上课程课时数大于 90 的所有教师的信息，包括编号、 姓名。(4 分) 8.建一个规则，并将其绑定到教师表的职称列上，规定取值为（教授, 副教授,讲师, 助教）之一。(4 分) 1. create database 教师数据库-（2 分） use 教师数据库 go create table 教师表-（6 分） (编号 char(6) primary key, 姓名 nchar(4) not null, 性别 nchar(1) check(性别 in (男, 女), 民族 nchar(8) default 汉族 not null, 职称 nchar(12), 身份证号 char(18) unique ) create table 课程表-（2 分） (课号 char(6) primary key, 名称 char(40) not null ) create table 任课表-（4 分） (ID IDENTITY(1, 1), 教师编号 char(6) references 学生表(学号), 课号 char(6) references 课程表(课号), 课时数 integer check(课时数 between 0 and 200) ) 2. insert 课程表 values(100001, SQL Server 数据库) insert 课程表 values(100002, 数据结构) insert 课程表 values(100003, VB 程序设计) update 课 程 表set 名 称 =Visual Basic 程 序 设 计 where 课 号 =100003 delete 课程表 where 课号=100003 3. create view 任课表视图 as select 教师编号,姓名,课号, 课程名称,课时数 from 教师表,任课表 where 教师表.编号=任课表.教师编号 4. create function 某门课任课教师(课程名 varchar(15) returns table as return (select 课程名称, 课时数, 教师姓名=姓名 from 任课表视图 where 课程名=课程名) go select * from 某门课任课教师(SQL Server 数据库) 5. create procedure 统计课时数 as select最大课时数=max(课时) ,最小课时数=min(课时),平均课时数 =avg(课时) from 任课表 go execute 统计课时 6. create procedure 统计课时 教师名 nchar(16), as begin declare 总课时 int select 总课时=sum (课时) from 任课表视图 where 姓名 = 教师名 end go execute 统计课时 郭老师 7. select 编号, 姓名 from 教师表 where 编号 in (select distinct 教师编号 from 任课表 where 课时 数=90) 8. create rule zhicheng _rule as zhichengin (教授,副教授,讲师, 助教) go sp_bindrule zhicheng_rule, 教师表.职称 三、设计题三、设计题( (共共 5050 分分) ) 现有关系数据库如下： 数据库名：医院数据库 医生表(编号，姓名，性别，出生日期，职称) 病人表(编号，姓名，性别，民族，身份证号) 病历表(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. create database 医院数据库 use 医院数据库 go create table 医生表 (编号 char(6) primary key， 姓名 nchar(4) not null， 性别 nchar(1) check(性别 in (男, 女)， 出生日期datetime ， 职称 char(18) not null ) create table 病人表 (课号 char(6) primary key, 姓名 nchar(4) not null, 性别 nchar(1) check(性别 in (男, 女)， 民族 nchar(10), 身份证号 char(18) not null ) create table 病历表 (ID IDENTITY(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. create view 医疗表视图 as select br.姓名,ys.姓名,yl.病历 from 病人表 as br join 医疗表 as yl on br.编号 yl.病人编号 join 医生表 as ys on yl.医生编号=ys.编号 4 Selectbr.编号, br.姓名, br.病历, yl.医生编号 from 病人表 br left join 医疗表 yl on br.编号 = yl.病人编号 5 create procedure 某医生看病人数 医生编号 nchar(16), 人数 int output as select 人数=count(病人编号) from 病人表 where 医生编号 = 医生编号 go declare TOTAL int execute 某医生看病人数 100001, TOTAL 6 select * from 医生表 where 出生日期1970-01-01 9. select 编号, 姓名, 性别, 出生日期, 职称 from 医生表 where 编号 in (select 医生编号 from 医疗表) 8. createdefaultzhicheng AS 医师 go exec sp_bindefault zhicheng, 医生表.职称 四、设计题四、设计题( (共共 5050 分分) ) 现有关系数据库如下： 数据库名：学生选课数据库 学生表（学号，姓名，性别，年龄，专业） 课程表（课程号，课程名，学分，先行课程号） 选课表（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. create database 学生选课数据库 go use 学生选课数据库 go 2. create table 学生表 (学号 char(6) primary key， 姓名 nchar(4) not null， 性别 nchar(1) check(性别 in (男, 女)， 年龄 numeric(2) ， 专业 char(18) not null ) 3. create table 课程表 (课程号 char(6) primary key， 课程名 char(40) not null， 学分 numeric(2) not null， 先行课程号 char(6) ) 4. create table 选课表 (ID IDENTITY(1, 1), 学号 char(6) references 学生表(学号), 课程号 char(6) references 课程表(课程号), 分数 integer check(分数 between 0 and 100) ) 5. insert 课程表 values(100001, C 语言，2) insert 课程表 values(100002, 数据结构, 2, 100001) insert 课程表 values(100003, 数据库原理, 2) update 课程表 set 课程名=SQL 数据库 where 课程号=100003