数据库实验题平时作业.doc

1、数据库实验题实验一：交互式实验一：交互式 SQL 的使用的使用实验要求：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 var

2、char(100) null,BDATE datetime null,HEIGHT decimal null,DEPARTMENT varchar(100) null)gocreate table Courses(CNO varchar(100) primary key ,CNAME varchar(100) null,LHOUR int null,CREDIT int null,SEMESTER varchar(100) null)goCREATE TABLE dbo.SC(SNO varchar(100) NOT NULL,CNO varchar(100) NOT NULL,GRADE i

3、nt NULL,CONSTRAINT PK_SC PRIMARY KEY CLUSTERED (SNO ASC,CNO ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOALTER TABLE dbo.SC WITH CHECK ADD CONSTRAINT FK_SC_Courses FOREIGN KEY(CNO)REFERENCES dbo.Co

4、urses (CNO)GOALTER TABLE dbo.SC CHECK CONSTRAINT FK_SC_CoursesGOALTER TABLE dbo.SC WITH CHECK ADD CONSTRAINT FK_SC_Students FOREIGN KEY(SNO)REFERENCES dbo.Students (SNO)GOALTER TABLE dbo.SC CHECK CONSTRAINT FK_SC_Students2完成如下的查询要求及更新的要求。（1）查询身高大于 1.80m 的男生的学号和姓名；答：select SNO,SNAME from Students whe

5、re HEIGHT1.8（2）查询计算机系秋季所开课程的课程号和学分数；答：select CNO,CREDIT from Courses where SEMESTER=秋季（3）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；答：select s.SNAME,SC.CNO,c.CREDIT,SC.GRADE from students sinner join SC on sc.SNO=s.SNOinner join Courses c on sc.CNO=c.CNOwhere s.DEPARTMENT=计算机系 and s.SEX=男 and c.SEMESTER=秋季（4）查

6、询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以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 sinner join SC on sc.SNO=s.SNOinner join Courses c on sc.CNO=c.CNOgroup by s.SNO

7、（6）查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩；答：（7）查询所有课程的成绩都在 80 分以上的学生的姓名、学号、且按学号升序排列；答：（8）查询缺成绩的学生的姓名，缺成绩的课程号及其学分数；答：（9）查询有一门以上 (含一门)三个学分以上课程的成绩低于 70 分的学生的姓名；答：（10）查询 1984 年1986 年出生的学生的姓名,总平均成绩及已修学分数。答：（11） 在 STUDENT 和 SC 关系中，删去 SNO 以01 开关的所有记录。答：delete sc where SNO like %01%delete Students where SNO like %01%

8、（12）在关系中增加以下记录：答：（13）将课程 CS-221 的学分数增为，讲课时数增为答：3补充题：(1) 统计各系的男生和女生的人数。答：(2) 列出学习过编译原理， 数据库或体系结构课程，且这些课程的成绩之一在90 分以上的学生的名字。答：(3) 列出未修选电子技术课程，但选修了数字电路或 数字逻辑课程的学生数。答：(4) 按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用 NULL 表示。答：(5) 列出平均成绩最高的学生名字和成绩。(SELECT 句中不得使用 TOP n 子句)答：4选做题： 对每门课增加 “先修课程”的属性，用来表示某一门课程的先修课

9、程，每门课程应可记录多于一门的先修课程。要求：1) 修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。2) 设计并插入必要的测试数据，完成以下查询：列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)注意：须设计每个查询的测试数据，并在查询之前用 INSERT 语句插入表中。实验二：数据库的安全和完整性约束实验二：数据库的安全和完整性约束实验要求：1采用实验一的建库脚本和数据插入脚本创建 Student 数据库,并完成以下操作：1）新增表 Credits(SNO,SumCredit,NoPass)，表示每学生已通过选修课程的合计学分数，以及

10、不及格的课程数。答：create table Credits(SNO varchar(100),SumCredit int,NoPass int)2）创建视图 Student_Grade(Sname,Cname,Grade)，表示学生选修课程及成绩的详细信息。答：create view Student_Gradeas select s.SNAME,c.CNAME,SC.GRADE from students sinner join SC on sc.SNO=s.SNOinner join Courses c on sc.CNO=c.CNO2. 在数据库中创建以下触发器：1） Upd_Credi

11、t要求：当在 SC 表中插入一条选课成绩，自动触发 Upd_Credit，完成在Credits 表中修改该学生的合计学分数和不及格的课程数。答：create trigger Upd_Credit on SC for insertas declare SNO varchar(100),CNO varchar(100),GRADE int ,NoPass int ,CREDIT intselect SNO=SNO,CNO=CNO,GRADE=GRADE,NoPass=(case when GRADE3group by s.SNO4) CAL_GPA (SNO，GPA output)要求：根据 SNO 参数, 输出并显示该学生的 GPA 值。计算方法如下：GRADE(G) GRADEPOINT(GP)G=85 485G=75 375G=60 260G 1