数据库上机习题及答案

数据库及应用复习题

一、设计题

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

学生表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分)

课号课程名称

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 [教师数据库]

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 [统计课时数]