华工数据库实验题.doc

华工数据库实验题
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 o 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,o,credit from students,courses,sc
where students.sno=sc.sno and o=o and grade is null;

（9）查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名； 答：
select sname from students,courses,sc
where students.sno=sc.sno and o=sc.cho and credit>=3 and grade 答：
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 o=o and cname='电子技术')
and o in (select cno from courses where cname='数字逻辑' or cname='数字电路')

(4) 按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。 答：
select distinct o,ame,sno,grade from courses left join sc on (o-o)
group by o,ame,sno,grade order by o,ame,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
5 / 5