数据库上机习题及答案

数据库及应用复习题

一、设计题

有一个[学生课程]数据库，数据库中包括三个表：

学生表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='计算机系')

7．DELETE 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([课时数]between0and200)

)

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@zhicheng in('教授','副教授','讲师','助教')

go

sp_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[医院数据库]

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．

Select br.编号,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.

create default zhicheng AS'医师'

go

exec sp_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.

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([分数]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.create view[选课表视图]as

select选课表.学号,姓名,选课表.课程号,课程名,学分,分数

from选课表,学生表,课程表

where选课表.学号=学生表.学号

and选课表.课程号=课程表.课程号

--或

create view[选课表视图]as

select选课表.学号,姓名,选课表.课程号,课程名,学分,分数

from选课表join学生表on选课表.学号=学生表.学号

join课程表on选课表.课程号=课程表.课程号

7.

create function[某门课程成绩](@课程名varchar(40))

returns table as

return(select学号,姓名,课程名,学分,分数from选课表视图

where课程名=@课程名)

go

select*from[某门课程成绩]('SQL数据库')

8.

create procedure[某门课程高低均分]

@课程名varchar(40)as

select课程名,最高分=max(分数)、最低分=min(分数)、平均分=avg(分数)

from选课表视图

where课程名=@课程名

go

execute[某门课程高低均分]'SQL数据库'

9.

select姓名,学号,专业from学生表

where姓名like'张%'and性别='女'

10.

select学号,姓名,专业from学生表where学号in

(select distinct学号from选课表where分数<60)