华工数据库实验题

1,创建Student数据库，包括Students,Courses,SC表，表结构如下：

Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT) Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER) SC(SNO,CNO,GRADE)

(注：下划线表示主键，斜体表示外键)，并插入一定数据。 答：

create table Students (

SNO varchar(100) primary key , SNAME varchar(100) null, SEX varchar(100) null, BDATE datetime null, HEIGHT decimal null,

DEPARTMENT varchar(100) null ) go

create table Courses (

CNO varchar(100) primary key , CNAME varchar(100) null, LHOUR int null, CREDIT int null,

SEMESTER varchar(100) null ) go

CREATE TABLE [dbo].[SC]( (

[SNO] ASC, [CNO] ASC

[SNO] varchar(100) NOT NULL, [CNO] varchar(100) NOT NULL, [GRADE] [int] NULL,

CONSTRAINT [PK_SC] PRIMARY KEY CLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

ALTER TABLE [dbo].[SC] WITH CHECK ADD CONSTRAINT [FK_SC_Courses] FOREIGN KEY([CNO])

REFERENCES [dbo].[Courses] ([CNO]) GO

ALTER TABLE [dbo].[SC] CHECK CONSTRAINT [FK_SC_Courses] GO

ALTER TABLE [dbo].[SC] WITH CHECK ADD CONSTRAINT [FK_SC_Students] FOREIGN KEY([SNO])

REFERENCES [dbo].[Students] ([SNO]) GO

ALTER TABLE [dbo].[SC] CHECK CONSTRAINT [FK_SC_Students]

2．完成如下的查询要求及更新的要求。

（1）查询身高大于1.80m的男生的学号和姓名； 答：

select SNO,SNAME from Students where HEIGHT>1.8

（2）查询计算机系秋季所开课程的课程号和学分数； 答：

select CNO,CREDIT from Courses where SEMESTER='秋季'

（3）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩； 答：

select s.SNAME,SC.CNO,c.CREDIT,SC.GRADE from students s inner join SC on sc.SNO=s.SNO

inner join Courses c on sc.CNO=c.CNO

where s.DEPARTMENT='计算机系' and s.SEX='男' and c.SEMESTER='秋季'

（4）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头）；

答：

select distinct s.sname from Students s,sc where s.sno=sc.sno and s.sex='女' and sc.cno like 'EE%'

（5）查询每位学生已选修课程的门数和总平均成绩； 答：

select count(c.CNO) as 课程门数,avg(SC.GRADE) as 总平均成绩from students s inner join SC on sc.SNO=s.SNO

inner join Courses c on sc.CNO=c.CNO group by s.SNO

（6）查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩； 答：

select cname,count(cno),max(grade),min(grade),avg(grade)

from students natural join sc natural join courses group by chane;

（7）查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列；

答：select sname,sno from students natural join (select sno.min(grade)as

mini from sc group by sno)where mini>80 order by(sno);

（8）查询缺成绩的学生的姓名，缺成绩的课程号及其学分数； 答：

select sname,courses.cno,credit from students,courses,sc

where students.sno=sc.sno and courses.cno=sc.cno and grade is null;

（9）查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名； 答：

select sname from students,courses,sc

where students.sno=sc.sno and courses.cno=sc.cho and credit>=3 and grade<70;

（10）查询1984年~1986年出生的学生的姓名,总平均成绩及已修学分数。 答：

select smane,avg(grade),sun(credit)

from students natural join sc natural join courses where bdate between '1984-00-00' and '1987-00-00' group by sname;

（11） 在STUDENT和SC关系中，删去SNO以’01’开关的所有记录。 答：

delete sc where SNO like '
%' delete Students where SNO like '
%'

（12）在ＳＴＵＤＥＮＴ关系中增加以下记录： <0409101 何平 女 1987-03-02 1.62> <0408130 向阳 男 1986-12-11 1.75> 答：

insert into students values('0409101','何平','女','1987-03-02',1.62,'') insert into students values('0408130','向阳','男','1986-12-11',1.75,'')

（13）将课程CS-221的学分数增为３，讲课时数增为６０ 答：

update courses set credit=3 where cno='GS-221' update courses set credit=60 where cno='GS-221'

3．补充题：

(1) 统计各系的男生和女生的人数。 答：

select department,

sum(case wgen sex='男' then 1 else 0 end),

sum(case wgen sex='女' then 1 else 0 end),

count(sno) from students group by department order by department;

(2) 列出学习过‘编译原理’，‘数据库’或‘体系结构’课程，且这些课程的成绩之一在90分以上的学生的名字。 答：

select sname

from students natural join sc natural join courses

where cname='编译原理' or cname='数据库' or cname='体系结构' and grade>90;

(3) 列出未修选‘电子技术’课程，但选修了‘数字电路’或‘数字逻辑’课程的学生数。 答：

select count(distinci sc.sno) from courses,sc.sno not in (select sc.sno from courses.cno=sc.cno and cname='电子技术')

and sc.cno in (select cno from courses where cname='数字逻辑' or cname='数字电路')

(4) 按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。 答：

select distinct courses.cno,courses.cname,sno,grade from courses left join sc on (courses.cno-sc.cno)

group by courses.cno,courses.cname,sno,grade order by courses.cno,courses.cname,sno,grade;

(5) 列出平均成绩最高的学生名字和成绩。(SELECT句中不得使用TOP n子句) 答：

select sname,r

from(select sname,avg(grade)as from students,sc where

students.sno=sc.sno group by sname,students.sno order by r desc) where rownum=1;

4．选做题：对每门课增加?先修课程?的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：

1) 修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。 2) 设计并插入必要的测试数据，完成以下查询：

列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)

注意：须设计每个查询的测试数据，并在查询之前用INSERT语句插入表中。

实验二：数据库的安全和完整性约束

实验要求：

1．采用实验一的建库脚本和数据插入脚本创建Student数据库,并完成以下操作：

1）新增表Credits(SNO,SumCredit,NoPass)，表示每学生已通过选修课程的合计学分数，以及不及格的课程数。 答：create table Credits

(SNO varchar(100), SumCredit int, NoPass int)

2）创建视图Student_Grade(Sname,Cname,Grade)，表示学生选修课程及成绩的详细信息。

答：create view Student_Grade

as

select s.SNAME,c.CNAME,SC.GRADE from students s inner join SC on sc.SNO=s.SNO

inner join Courses c on sc.CNO=c.CNO

2. 在数据库中创建以下触发器：

1） Upd_Credit 要求：当在SC表中插入一条选课成绩，自动触发Upd_Credit，完成在Credits表中修改该学生的合计学分数和不及格的课程数。 答：create trigger Upd_Credit on SC for insert

as

declare @SNO varchar(100),@CNO varchar(100),@GRADE int ,@NoPass int ,@CREDIT int

select @SNO=SNO,@CNO=CNO,@GRADE=GRADE,@NoPass=(case when GRADE<60 then 1 else 0 end) from inserted

select @CREDIT=CREDIT from Courses where CNO=@CNO update Credits

set SumCredit=SumCredit+@CREDIT, NoPass=NoPass+@NoPass

where SNO =@SNO

2） Upd_StuView （Instead of触发器）

要求：当对视图Student_Grade作插入数据项操作时，自动触发Upd_StuView，完成对SC表的插入操作。 如：当执行Insert into Student_Grade values(‘王刚’，’数据库’，54) 则触发器完成另一插入操作：Insert into SC values(‘980201’，’CS-110’，54)

另外，需要检查当前插入的学生和课程是否已在Students，和Courses表中存在，如不存在，不执行任何操作，并提示用户错误信息。 答：create trigger Upd_StuView on Student_Grade for insert

as

declare @SNAME varchar(100),@CNAME varchar(100),@GRADE int select @SNAME=SNAME,@CNAME=CNAME,@GRADE=GRADE from inserted

IF(EXISTS(SELECT * FROM Students WHERE SNAME=@SNAME) AND EXISTS (SELECT * FROM Courses WHERE CNAME=@CNAME)) BEGIN

Insert into SC

select (SELECT TOP 1 SNO FROM Students WHERE SNAME=@SNAME), (SELECT TOP 1 CNO FROM Courses WHERE CNAME=@CNAME), @GRADE

END

3） PK_SC,FK_SC_SNO,FK_SC_CNO) （选做）

要求：首先删除SC中所有主键和外键定义，用触发器实现表SC上的主键（SNO，CNO）和外键SNO，CNO的约束定义。

3．为Student数据库设计安全机制。

要求：在该数据库系统中，有三类用户：

1) 学生，权限包括：查询所有的课程信息，根据学号和课程号来查询成绩。

但不允许修改任何数据。(必做)

只能查询自己的成绩，不能查询别人的成绩。（选做）

2）老师：权限包括：查询有关学生及成绩的所有信息，有关课程的所有信息，但不允许修改任何数据。

答：

CREATE TRIGGER secure_student BEFORE UPDATE OR DELETE ON database BEGIN

IF((select user from dual)='老师') THEN

RAISE_APPLICATION_ERROR(-20506,

'您没有权限对学生表进行修改.')

3）教务员：权限包括：查询和修改任何有关学生和课程的信息，但不允许查询和修改数据库中其它任何表，视图等数据库对象。 答：CREATE TRIGGER secure_student

BEFORE UPDATE OR DELETE ON database EXCEPT Students BEGIN

IF((select user from dual)='教务员') THEN

RAISE_APPLICATION_ERROR(-20506,

'您没有权限进行修改.')

要求：安全控制必须仅由数据库一端来实现，不考虑由应用程序来控制。 为此，需要创建三个用户，登录时密码验证；分别授予各类权限，并测试权限的控制是否有效。

实验三：SQL编程 实验要求：

1．采用实验一的建库脚本和数据插入脚本创建Student数据库。 2．在数据库中创建以下存储过程：

1) Add_Student (SNO,SNAME,SEX,BIRTHDAY,HEIGHT,DEPT) 要求：根据输入参数，插入一条学生记录。

答：create procedure Add_Student @SNO varchar(100), @SNAME varchar(100), @SEX varchar(10), @BIRTHDAY datetime, @HEIGHT decimal, @DEPT varchar(100) as

insert into Students values( @SNO, SNO, @SNAME, @SEX, @BIRTHDAY, @HEIGHT, @DEPT

)

2) Upd_Grade (SNO, CNO, GRADE)

要求：根据输入参数，修改某学生选课的成绩。 答：create procedure Upd_Grade

@SNO varchar(100), @CNO varchar(100), @GRADE INT as

UPDATE SC set SNO=@SNO, CNO=@CNO, GRADE=@GRADE

where SNO=@SNO and CNO=@CNO

3) Disp_Student (SNO，SUM_CREDIT output，AVG_GRADE output) 要求：根据SNO参数显示该学生的有关信息，包括：

a)学号，姓名，性别，年龄，身高，系别，所有选修的课程及成绩； b)显示输出参数SUM_CREDIT(表示选修课程的总学分)及AVG_GRADE (表示3学分以上的课程的平均成绩)。

答：create procedure Upd_Grade

@SNO varchar(100), @SUM_CREDIT INT output, @AVG_GRADE int output

as

select * from Students s inner join SC on sc.SNO=s.SNO

inner join Courses c on sc.CNO=c.CNO where s.SNO=@SNO

select @SUM_CREDIT=SumCredit from Credits where SNO=@SNO

select @AVG_GRADE=avg(SC.GRADE) from Students s inner join SC on sc.SNO=s.SNO where s.SNO=@SNO and SC.GRADE>3 group by s.SNO

4) CAL_GPA (SNO，GPA output)

要求：根据SNO参数, 输出并显示该学生的GPA值。计算方法如下： GRADE(G) GRADEPOINT(GP) G>=85 4 85>G>=75 3 75>G>=60 2 60>G 1

GPA= (∑GP*CREDIT)/ ∑CREDIT) 答：create procedure CAL_GPA

@SNO varchar(100), @GPA decimal output as

declare @SUM_CREDIT int,@AVG_GRADE int,@All_CREDIT int select @SUM_CREDIT=SumCredit from Credits where SNO=@SNO

select @AVG_GRADE=avg(case when SC.GRADE>=85 then 4 when 85>SC.GRADE and SC.GRADE>=75 then 3 when 75>SC.GRADE and SC.GRADE>=60 then 2 when 60>SC.GRADE then 1 end) from Students s inner join SC on sc.SNO=s.SNO where s.SNO=@SNO and SC.GRADE>3 group by s.SNO

select @GPA=(@AVG_GRADE*@SUM_CREDIT)/@All_CREDIT

3．选做题：

使用其它程序设计语言编程，连接数据库并提交SQL语句，显示查询结果。 要求：实现上题中的第3)小题要求，设计一个图形界面来输入查询的参数SNO，及显示查询的结果。(如采用VC++，VB等)

实验四：事务的管理（选作） 实验要求：

1． 采用实验一的建库脚本和数据插入脚本创建Student数据库。 2． 测试事务隔离级别，要求：

分别设置不同的隔离级别,包括：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL READ ONLY;

两个并发事务交错执行的程序，能分别显示每种隔离级别下，是否出现丢失更新，脏读，读值不可复现以及幻象四种情况。 3．备份与恢复

? 备份数据库 答：

BACKUP DATABASE test TO disk = 'c:\\test'

? 删除sc表

答：DROP TABLE SC

? 恢复到删除之前 答:

USE master GO

RESTORE DATABASE test_wt FROM disk = 'c:\\test_wt'

GO

本文来源：https://www.bwwdw.com/article/02ah.html