数据库实验指导书答案

实验五 查询数据库

1） 启动SQL Server查询分析器，打开“SQL查询分析器”窗口。

2） 在“SQL查询分析器”窗口中选择要操作的数据库，如“XSCJ”数据库。 3） 在KC表中查询学分低于3的课程信息，并按课程号升序排列。 select *

From KC

Where 学分<=3

4） 在XS_KC表中按学号分组汇总学生的平均分，并按平均分的降序排列。

Select 学号,avg(成绩) From XS_KC Group by 学号 Order by 学号

5） 在XS_KC表中查询选修了3门以上课程的学生学号。

Select 学号 From XS_KC

Group by 学号

Having count(*)>=3

6） 按学号对不及格的成绩记录进行明细汇总。 Select 学号,成绩

From XS_KC Where 成绩<60

7） 分别用子查询和连接查询，求107号课程不及格的学生信息。

Select * From XSQK Where 学号in (select 学号 From XS_KC

Where 课程号= '107' and 成绩<60 )

8） 用连接查询在XSQK表中查询与杨颖住在同一寝室的学生，即其联系电话相同

Select * From XSQK Where 联系电话 in (Select 联系电话 From XSQK

Where 姓名='杨颖')

9） 查询XSQK表中所有的系名。 10）查询有多少同学选修了课程。 select count(distinct 学号) From XS_KC

11） 查询有多少同学没有选课。

select count(*)

from xsQK left join xs_KC on xs_KC.学号=xsQK.学号 where 成绩is null

12）查询与杨颖同一个系的同学姓名。 select 姓名

From xsQK Where

13） 查询选修了课程的学生的姓名、课程名与成绩。

14） 统计每门课程的选课人数和最高分。 select 课程号,count(学号),max(成绩) from xs_kc

group by 课程号

15） 统计每个学生的选课门数和考试总成绩，并按选课门数的降序排列。 select 学号,count(课程号),max(成绩) from xs_kc group by 学号 order by count(课程号)

实验七 创建和使用索引

1) 给KC表的课程号属性上创建聚集索引K1，在XS_KC表的[学号，课程号]上创建复合索引IX_XS_KC。

create clustered index k1 on [KC](课程号)

create clustered index IX_XS_KC on [XS_KC](学号,课程号)

2)强制使用索引进行查询 select *

from kc with(index (k1))

实验八 创建并使用约束和默认值对象

1）建立一个默认值对象“DFO_出生日期”，其值为1980-1-1 Create default DFO_出生日期

As '1980-1-1'

2）将默认值对象绑定到XSQK表的出生日期列。 exec sp_bindefault DFO_出生日期,'xsqk.出生日期'

实验九 实现数据完整性

1)在XSQK表中创建一个规则X＿R，只能接收8位的数字，并邦定到电话号码列中。

create rule x_R as

@a like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

exec sp_bindrule x_r,'xsqk.电话号码 '

2）XSQK表再插入数据，电话号码为“7768971”，“34ar”,检查完整性 参照完整性

1）给XSQK，KC，XS＿KC建立参照完整性，确定主码，外码。

alter table KC

add constraint skc primary key(课程号)

alter table xsqk

add constraint skc1 primary key(学号)

alter table xs_kc

add constraint skc2 primary key(学号,课程号) alter table xs_kc

add constraint xs foreign key(学号) references xsqk(学号) on

delete cascade

on update cascade alter table xs_kc

add constraint xs1 foreign key(课程号) references kc(课程号)

on delete no action on update noaction

2）设置SXQK表对于删除和更新操作是级连，KC表对于删除更新操作是拒绝。 3）在XS＿Kc表中分别插入两条记录，学号分别为“020208”和“020201”检查完整性。

4）更新XS＿KC，更改学号“020201”为“020206”，检查完整性。

5）删除XSQK表中的学号为“020201”的记录，更改学号为“020201”的记录为“020208”

实验十 用户自定义函数

1）创建一个函数，输入学生学号，要求出此学生所选课程的门数 create function n1

(@id int ) -- 参数

returns int -- 返回值类型 begin

declare @nu int select @nu=count(*) from xs_kc

where 学号=@id

return @nu -- 返回值 end

select dbo.n1('020101')

2）创建一个函数，输入教师姓名，要求输入此教师所授课程被选修的情况

create function table1 (@tname varchar(50)) returns table return (select * from kc

where 教师=@tname and 课程号 in (select 课程号 from xs_kc

))select *

from t('陈红')

3）创建一个函数，输入学号，输出这个学生选修的情况以及成绩的等级。 小于60 不及格 60－70 及格 70－80 中等 80－90 良好 90以上 优秀

create function m6 (@sno nchar(10))

returns @my_table table (

sno nchar(10), cno nchar(10), grade nchar(10), l nchar(10) ) as begin

insert @my_table select 学号,课程号, 成绩,l= case

when 成绩 <60 then '不及格'

when 成绩>=60 and 成绩<70 then '及格' when 成绩>=70 and 成绩<80 then '中等' when 成绩>80 and 成绩<=90 then '良好' else '优秀' end

from XS_kc

where 学号=@sno return end

select *

from m6('020101')

实验十一 实现存储过程

1）建立一个存储过程，要求输入一个课程号，查询该课成绩不及格的学生的基本信息，包括学号、姓名、性别和联系电话信息，最后输出。 create proc mp4 @cno varchar(10) as

select xsqk.学号,姓名,性别,联系电话 from xsqk,xs_kc

where xsqk.学号=xs_kc.学号 and 成绩<60 and 课程号=@cno exec mp4 ‘001’

2)创建一个存储过程，要求输入教师号，输出该教师所教课程的课程的被选修的人数

create proc mp5 @tno varchar(10) as

select count(*) from kc,xs_kc

where kc.课程号=xs_kc.课程号 and 教师=@tno exec mp5 '陈红'

3)执行所创建的二个存储过程 4) 删除新建的存储过程

Drop proc mp5