实验五 查询数据库
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