数据库及应用复习题一、设计题有一个[学生课程]数据库，数据库中包括三个表：学生表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 DATETIME3．SELECT Sno, GradeFROM SGWHERE Cno='3'ORDER BY Grade DESC4．SELECT MAX(Grade), AVG(Grade)FROM SCWHERE Cno='1'5．SELECT Sno, Sname, SdeptFROM StudentWHERE Sdept IN(SELECT Sdept FROM StudentWHERE Sname='李洋')6．UPDATE SGSET Grade=0WHERE Sno in( SELECT Sno FROM StudentWHERE Sdept = '计算机系')7．DELETE FROM StudentWHERE Sno='05019'8．DELETE FROM SGWHERE Sno in( SELECT Sno FROM StudentWHERE Sdept = '计算机系')二、设计题现有关系数据库如下：数据库名：教师数据库教师表(编号 char(6)，姓名，性别，民族，职称，身份证号)课程表(课号 char(6)，名称)任课表(ID，教师编号,课号，课时数)用SQL语言实现下列功能的sql语句代码：1. 创建上述三表的建库、建表代码(14分)；要求使用：主键(教师表.编号，课程表.课号)、外键(任课表.教师编号，任课表.课号)、默认(民族)、非空(民族，姓名)、唯一(身份证号)、检查(性别、课时数),自动编号(ID)2. 将下列课程信息添加到课程表的代码(6分)课号课程名称100001 SQL Server数据库100002 数据结构100003 VB程序设计修改课号为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 [教师数据库]gocreate 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 [任课表视图] asselect 教师编号,姓名,课号, 课程名称,课时数 from 教师表,任课表where 教师表.编号=任课表.教师编号4.create function [某门课任课教师](@课程名 varchar(15))returns table asreturn (select 课程名称, 课时数, 教师姓名=姓名 from 任课表视图where 课程名=@课程名)goselect * from [某门课任课教师]('SQL Server数据库')5.create procedure [统计课时数]asselect 最大课时数=max(课时) ,最小课时数=min(课时),平均课时数=avg(课时) from 任课表goexecute [统计课时]6.create procedure [统计课时]@教师名 nchar(16),asbegindeclare @总课时 intselect @总课时=sum (课时) from 任课表视图where 姓名 = @教师名endgoexecute [统计课时] '郭老师'7.select 编号, 姓名 from 教师表where编号 in (select distinct 教师编号 from 任课表 where课时数>=90)8.create rule zhicheng _ruleas @zhicheng in ('教授','副教授','讲师', '助教')gosp_bindrule zhicheng_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.create database [医院数据库]use [医院数据库]gocreate 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 [医疗表视图] asselect br.姓名,ys.姓名,yl.病历 from 病人表 as br join 医疗表 as ylon br.编号yl.病人编号join 医生表 as yson yl.医生编号=ys.编号4．Select br.编号, br.姓名, br.病历, yl.医生编号from 病人表 br left join 医疗表 ylon br.编号 = yl.病人编号5．create procedure [某医生看病人数]@医生编号 nchar(16), @人数 int outputasselect @人数=count(病人编号)from 病人表where 医生编号 = @医生编号godeclare @TOTAL intexecute [某医生看病人数] '100001', @TOTAL6select * from 医生表 where 出生日期<'1970-01-01'9.select 编号, 姓名, 性别, 出生日期, 职称 from 医生表where 编号 in (select 医生编号 from 医疗表)8.create default zhicheng AS '医师'goexec sp_bindefault 'zhicheng', '医生表.职称'四、设计题(共50分)现有关系数据库如下：数据库名：学生选课数据库学生表（学号，姓名，性别，年龄，专业）课程表（课程号，课程名，学分，先行课程号）选课表（ID, 学号，课程号，分数）用SQL语言实现下列功能的sql语句代码：1. 创建数据库[学生选课数据库]代码(2分)；2. 创建[课程表]代码(2分)；课程表(课程号 char(6)，课程名，学分，先行课程号)要求使用：主键(课程号)、非空(课程名, 学分)3. 创建[学生表]代码(6分)；学生表(学号 char(6)，姓名，性别，年龄，专业)要求使用：主键(学号)、非空(姓名,专业)、检查(性别)4. 创建[选课表]代码(4分)；选课表(ID,学号，课号，分数)要求使用：外键(选课表.学号，选课表.课程号)、检查(分数),自动编号(ID) 5. 将下列课程信息添加到课程表的代码(8分)课程号课程名学分先行课程号100001 C语言 2100002 数据结构 2 100001100003 数据库原理 2修改课程号为100003的课程名：SQL数据库删除课程号为100002的课程信息6. 写出创建：选课表视图(学号，姓名，课程号，课程名，学分，分数)的代码；(4分)7. 写出创建：某门课程成绩内嵌表值函数以及检索的代码；(6分)检索：所有修 SQL数据库这门学生的成绩；8. 写出创建：某门课程高低均分计算某门课程成绩最高分、最低分、平均分存储过程以及执行的代码；(6分)执行：所有修 SQL数据库这门学生的最高分、最低分、平均分；9. 检索姓张的女同学的情况：姓名、学号、专业。