华工数据库实验题

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

搜索“diyifanwen.net”或“第一范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，第一范文网，提供最新幼儿教育华工数据库实验题 全文阅读和word下载服务。