数据库原理及应用期末考试..

综合题

一、

1．使用Transact—SQL语言创建教学管理数据库，创建两个表学生信息（学号，姓名，性别，籍贯，班级编号）和成绩表（学号，课程编号，成绩，是否重修）。其中，学生信息表中学号为主键，其他信息都不允许为空。通过学号与成绩表形成一个一对多的关系，成绩表中的成绩的默认值为0，但必须在0～100之间。注：将学号设置为主键，主键名为pk_xh；

为成绩添加默认值约束，约束名为df_xb,其值为“0”；

为成绩添加check约束，约束名为诚恳ck_xb, 在0～100之间。

CREATE DATABASE 教学管理

use 教学管理

go

create table 学生信息（

学号 varchar(20) NOT NULL PRIMARY KEY,

姓名 varchar(20) NOT NULL ,

性别 varchar(10) NOT NULL,

籍贯 varchar(20) NOT NULL,

班级编号 varchar(20) NOT NULL )

go

create table 成绩表

( 学号 varchar(20) NOT NULL FOREIGN KEY REFERENCES 学生信息（学号）,

课程编号 varchar(20) NOT NULL,

成绩 numeric(5,2) DEFAULT (0) CHECK(成绩)=0 and 成绩<=100) ,

是否重修 varchar(8) NOT NULL )

go

2．写出实现各查询功能的SELECT语句。

（1）统计班级编号为1002的学生人数。

（2）查询课程编号为“003”的课程成绩高于该门课程平均分的学生信息。（3）在“学生信息”表中找出姓“王”的学生信息。

（4）将“成绩”表中的课程编号为“001”的成绩增加10分。

（5）列出每个同学的学号、姓名及选修课程的平均成绩情况，没有选修的同学也列出。

3．创建一个名为stud_view2的视图，该视图可以用来查看每个学生的选修课程

的门数和平均成绩。

create view stud_ view2

as

select count(*) as 课程门数,avg(grade) as 平均成绩 from成绩表 group by 学号select * from stud_view2

4．（1）在“教学管理”数据库中创建一个带参数的存储过程proc_score。该存储过程的作用是：当输入某门课程的课程名称时，检索该门课程成绩，并将查询结果以“学号”，“姓名”，“课程名称”，“成绩等级”输出（成绩划分成四个等级：高于或等于85分者为优秀，高于或等于75分者为良好，高于或等于60分者为及格，低于60分者为不及格）。

（2）执行存储过程proc_score，查询“003”课程的成绩信息。

二、

假设现有数据库“学生信息”，其中包含“学生”、“课程”、“学期成绩”三个表。表结构如下：

“学生”表结构

课程（课号，课名，学分，先修课）

“学期成绩”表结构

选课（学号，课号，成绩）

根据各题功能要求写出相应的T-SQL语句。

1．写出在“学生信息”数据库中创建“学生”表的T-SQL语句，并在建表的同时创建所需约束。约束要求如下：

将学号设置为主键，主键名为pk_xh；为姓名设置唯一约束，约束名为uk_xm；为性别添加默认约束，约束名为df_xb,其值为“男”。

USE 学生信息

GO

CREATE TABLE 学生

(学号 INT CONSTRAINT pk_xh PRIMARY KEY,

姓名 CHAR(10) NOT NULL CONSTRAINT uk_xm UNIQUE,

性别 CHAR(2) NULL CONSTRAINT df_xb DEFAULT '男',

专业 CHAR(20) NULL,

班级 CHAR(12) NULL,

备注 TEXT NULL)

2．写出实现各查询功能的SELECT语句。

（1）计算机专业不及格学生的学号和姓名。

（2）选修了数据库课程的学生的学号、姓名和成绩，查询结果按分数的降序排列。

（3）在“课程”表中找出“课程名”中包含“计算机”三个字的课程。（4）将“课程”表中的课程编号为“001”的学分增加1学分；

（5）列出每个同学的学号、姓名及选修课程的平均成绩情况，没有选修的同学也列出。

3．创建一个名为stud_view2的视图，该视图可以用来查看每个学生的选修课程的门数和平均成绩。

4．（1）在“学生信息”数据库中创建一个带参数的存储过程proc_score。该存储过程的作用是：当输入某门课程的课程名称时，检索该门课程成绩，并将查询结果以“学号”，“姓名”，“课程名称”，“成绩等级”输出（成绩划分成四个等级：高于或等于85分者为优秀，高于或等于75分者为良好，高于或等于60分者为及格，低于60分者为不及格）。

CREATE PROC[EDURE] proc_score

@KCMC CHAR(20)

AS SELECT 学生.学号,姓名,课程名称,成绩等级=

CASE

WHEN 成绩>=85 THEN '优秀'

WHEN 成绩>=75 THEN '良好'

WHEN 成绩>=60 THEN '及格'

WHEN 成绩<60 THEN '不及格' （或：ELSE '不及格'）

END

FROM 学生 JOIN 学期成绩 ON 学生.学号=学期成绩.学号

JOIN 课程注册 ON 课程注册.课程代码=学期成绩.课程代码WHERE 课程名称=@KCMC

GO

（2）执行存储过程proc_score，查询“计算机网络”课程的成绩信息。

EXEC proc_score‘计算机网络’

5．定义一事务tran_stu，在学生“学期成绩”表中，为所有成绩高于50分的同学的成绩增加10分，并提交事务。

三、

假设现有数据库“学生信息”，其中包含“学生”、“课程注册”、“学期成绩”

三个表。表结构如下：

“学生”表结构：

┋┋┋┋┋┋┋┋┋┋┋