<SQL SERVER 数据库技术> 自测题
请写出命令代码（自己提出改善要求）并完成实操
1、创建数据库[成绩管理]代码(3分)；
create database 成绩管理
go
例：在D盘的HXDB目录下，创建 【HX成绩管理】数据库。
代码：
2、创建[课程表]代码(5分)；
课程表(课程号 char(6)，课程名称，课时，学分)
要求使用：主键(课号)、非空(课程名称)
create table 课程表
(课程号 char(6) primary key ,
课程名称 VARchar(12) not null,
课时 int,
学分 real )
3、创建[学生表]代码(5分)；
学生表(学号 char(6)，姓名，性别，民族，身份证号)
要求使用：主键(学号)、默认(民族为汉族)、非空(民族，姓名)、唯一(身份证号)、检查(性别)
create table 学生表
(学号 char(6) primary key ,
姓名char(8) not null ,
性别nchar(1) check([性别] in ('男', '女')),
民族nchar(8) default '汉族' not null,
身份证号char(18) unique)
4、创建[成绩表]代码(5分)；
成绩表(ID,学号，课号，分数)
要求使用：外键(成绩表.学号，成绩表.课号)、检查(分数),自动编号(ID)
create table 成绩表
(ID int identity(1,1),
学号 CHAR(6) FOREIGN KEY([学号])REFERENCES[dbo].[学生表] ([学号]),
课程号 CHAR(6) FOREIGN KEY([课程号])REFERENCES [dbo].[课程表] ([课程号]) ,
成绩 real check (成绩>=0 and 成绩<=100 ),
PRIMARY KEY CLUSTERED ( 学号 ASC, 课程号 ASC )
)
5、将下列课程信息添加到课程表的代码(9分)
课号 课程名称
100001 大学语文
100002 大学英语
100003 西班牙语
修改 课号为100002的课程名称：实用英语
删除 课号为100003的课程信息
insert课程表values('100001', '大学语文')
insert课程表values('100002', '大学英语')
insert课程表values('100003', '西班牙语')
update课程表set 名称='实用英语' where 课号='100002'
delete课程表where 课号='100003'
6、建视图：成绩表视图(学号，姓名，课号，课程名称，分数)的代码；(8分)
create view [成绩表视图] as
select学号,姓名,课号,课程名称,分数from成绩表,学生表
where成绩表.编号=学生表.编号
7、写出创建：某门课程成绩内嵌表值函数以及检索的代码；(8分)
检索：所有修 实用英语 这门学生的成绩；
create function [某门课程成绩](@课程名varchar(15))
returns table as
return (select 课程名称,课时数,学生姓名=姓名from成绩表视图
where实用英语=@课程名称)
go
select * from [某门课程成绩]('SQL Server数据库')
8、写出创建：某门课程高低均分 计算某门课程成绩最高分、最低分、平均分 存储过程以及执行的代码；(8分)
执行：所有修 实用英语 这门学生的最高分、最低分、平均分；
create procedure [某门课程分数]
as
select 最高分=max(分数) ,最低分=min(分数),平均分=avg(分数) from 成绩表
go
begin
declare @平均分int
select @平均分=sum (课时) from 成绩表视图
where 课程名= @实用英语
end
execute [某门课程分数]
9、检索姓李的女同学的情况：姓名、性别、民族。(6分)
select姓名,性别,民族from学生表
where姓名like '李%'
10、检索有一门或一门以上课程成绩大于等于90分的所有学生的信息，包括学号、姓名。(6分)
select学号,姓名from成绩表
where学号in (select distinct 学生学号from成绩表where课程成绩>=90)
11查询学生表中的男生记录 (6分)
select *
from学生表
where性别='男'
12、从学生表与课程表和成绩表中查询学生的学号、姓名、课程名和分数(6分)
SELECT学生表.学号,学生表.姓名,课程表.名称,成绩表.分数
from学生表,课程表,成绩表
where学生表.学号=成绩表.学号and课程表.课号=成绩表.课号
13、从课程表中统计第二学期的总学时。(6分)
执行结果为：
课程号 课程名 开课学期 学时
… … … …
… … … …
sum
============
14、创建一个触发器，当修改学生表中的姓名时，显示“学生姓名已被修改”。 (6分)
create trigger
on学生
after alter
as
print '学生姓名已被修改'
15、设置一SQL身份验证的账户：登录名：U领导，密码：888，数据库用户名：U读者，权限：可查询查询本数据库中所有表、视图、内嵌表值函数的数据。请写出账户、权限设置的T_SQL脚本。(13分)
use学生成绩数据库
exec sp_addlogin 'U领导', '888', '学生成绩数据库'
exec sp_grantdbaccess 'U领导', 'U读者'
exec sp_addrolemember 'db_datareader', 'U读者'
