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

综合题一、1．使用Transact—SQL语言创建教学管理数据库，创建两个表学生信息（学号，姓名，性别，籍贯，班级编号）和成绩表（学号，课程编号，成绩，是否重修）。

其中，学生信息表中学号为主键，其他信息都不允许为空。

通过学号与成绩表形成一个一对多的关系，成绩表中的成绩的默认值为0，但必须在0～100之间。

注：将学号设置为主键，主键名为pk_xh；为成绩添加默认值约束，约束名为df_xb,其值为“0”；为成绩添加check约束，约束名为诚恳ck_xb, 在0～100之间。

CREATE DATABASE 教学管理use 教学管理gocreate table 学生信息（学号 varchar(20) NOT NULL PRIMARY KEY,姓名 varchar(20) NOT NULL ,性别 varchar(10) NOT NULL,籍贯 varchar(20) NOT NULL,班级编号 varchar(20) NOT NULL )gocreate 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 )go2．写出实现各查询功能的SELECT语句。

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

（2）查询课程编号为“003”的课程成绩高于该门课程平均分的学生信息。

（3）在“学生信息”表中找出姓“王”的学生信息。

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

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

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

create view stud_ view2asselect count(*) as 课程门数,avg(grade) as 平均成绩 from成绩表 group by 学号select * from stud_view24．（1）在“教学管理”数据库中创建一个带参数的存储过程proc_score。

该存储过程的作用是：当输入某门课程的课程名称时，检索该门课程成绩，并将查询结果以“学号”，“姓名”，“课程名称”，“成绩等级”输出（成绩划分成四个等级：高于或等于85分者为优秀，高于或等于75分者为良好，高于或等于60分者为及格，低于60分者为不及格）。

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

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

表结构如下：“学生”表结构课程（课号，课名，学分，先修课）“学期成绩”表结构选课（学号，课号，成绩）根据各题功能要求写出相应的T-SQL语句。

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

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

USE 学生信息GOCREATE 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 学生.学号,姓名,课程名称,成绩等级=CASEWHEN 成绩>=85 THEN '优秀'WHEN 成绩>=75 THEN '良好'WHEN 成绩>=60 THEN '及格'WHEN 成绩<60 THEN '不及格' （或：ELSE '不及格'）ENDFROM 学生 JOIN 学期成绩 ON 学生.学号=学期成绩.学号JOIN 课程注册 ON 课程注册.课程代码=学期成绩.课程代码WHERE 课程名称=@KCMCGO（2）执行存储过程proc_score，查询“计算机网络”课程的成绩信息。

EXEC proc_score‘计算机网络’5．定义一事务tran_stu，在学生“学期成绩”表中，为所有成绩高于50分的同学的成绩增加10分，并提交事务。

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

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

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

USE 学生信息GOCREATE 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．完成以下查询。

(1)统计学生表中各班级的学生人数。

SELECT 班级,COUNT(*) AS 人数 FROM 学生GROUP BY 班级(2)查询“软件工程”课程成绩高于该门课程平均分的学生信息。

SELECT 学号,课程名称,成绩 FROM 学期成绩 JOIN 课程注册ON 学期成绩.课程代码=课程注册.课程代码WHERE 课程名称='软件工程' AND 成绩> (SELECT A VG(成绩)FROM 学期成绩 JOIN 课程注册ON 学期成绩.课程代码=课程注册.课程代码WHERE 课程名称='软件工程')(3)查询男同学选修的课程号。

(4)查询至少选修课程号为“C01”和“C02”的学生学号。

将所有学生的“01”课程的成绩增加10分。

3．在“学生信息”数据库中创建一个带参数的存储过程st_xsxx。

该存储过程的作用是：当输入某门课程的课程名称时，检索该门课程成绩，并将查询结果以“学号”，“姓名”，“课程名称”，“成绩等级”输出（成绩划分成四个等级：高于或等于85分者为优秀，高于或等于75分者为良好，高于或等于60分者为及格，低于60分者为不及格）。

CREATE PROC[EDURE] st_xsxx （1分）@KCMC CHAR(20) （1分）ASSELECT 学生.学号,姓名,课程名称,成绩等级=CASEWHEN 成绩>=85 THEN '优秀'WHEN 成绩>=75 THEN '良好'WHEN 成绩>=60 THEN '及格'WHEN 成绩<60 THEN '不及格' （或：ELSE '不及格'）ENDFROM 学生 JOIN 学期成绩 ON 学生.学号=学期成绩.学号JOIN 课程注册 ON 课程注册.课程代码=学期成绩.课程代码WHERE 课程名称=@KCMC （1分）GO四、现有关系数据库如下：数据库名：教师数据库教师表(编号char(6)，姓名，性别，民族，职称，身份证号)课程表(课号char(6)，名称)任课表(ID，教师编号,课号，课时数)用SQL语言实现下列功能的sql语句代码：1．写出创建数据库代码，创建教师表代码；要求使用：编号(主键)、默认(民族“汉”)、民族和姓名(非空)、身份证号(唯一)、性别(检查约束：只能是男或女)。

create database [教师数据库] --（2分）use [教师数据库]gocreate table 教师表 --（6分）([编号] char(6) primary key,[姓名] nchar(4) not null,[性别] nchar(1) check([性别] in ('男', '女')),[民族] nchar(8) default '汉族' not null,[职称] nchar(12),[身份证号] char(18) unique )create table 课程表 --（2分）([课号] char(6) primary key,[名称] char(40) not null )create table 任课表 --（4分）(ID IDENTITY(1, 1),[教师编号] char(6) references 学生表(学号),[课号] char(6) references 课程表(课号),[课时数] integer check([课时数] between 0 and 200) )2．写出下列课程记录添加到课程表的代码课号课程名称100001 SQL Server数据库100002 数据结构100003 VB程序设计修改：把课号为100003的课程名称改为“Visual Basic程序设计”；删除：课号为100003的课程信息insert 课程表 values('100001', 'SQL Server数据库')insert 课程表 values('100002', '数据结构')insert 课程表 values('100003', 'VB程序设计')update 课程表 set 名称='Visual Basic程序设计' where 课号='100003'delete 课程表 where 课号='100003'3．创建视图：任课表(教师编号，姓名，课号，课程名称，课时数)；create view [任课表视图]asselect 教师编号,姓名,课号, 课程名称,课时数 from 教师表,任课表where 教师表.编号=任课表.教师编号4．查询：所有教“SQL Server数据库”这门课程的老师姓名；create function [某门课任课教师] (@课程名 varchar(15))returns tableasreturn (select 课程名称, 课时数, 教师姓名=姓名 from 任课表视图where 课程名=@课程名)goselect * from [某门课任课教师]('SQL Server数据库')5．查询：最大课时数、最低课时数、平均课时的；create procedure [统计课时数]asselect 最大课时数=max(课时) ,最小课时数=min(课时),平均课时数=avg(课时) from 任课表go6．检索有一门或一门以上课程课时数大于90的所有教师的信息，包括编号、姓名；select 编号, 姓名 from 教师表where编号 in (select distinct 教师编号 from 任课表 where课时数>=90)7．修改教师表结构：教师表的职称列上，规定取值为（'教授'，'副教授'，'讲师'，'助教'）之一。