数据库上机习题及答案

数据库及应用复习题

一、设计题

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

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