项目3 学生管理系统的创建与管理

1.使用SQL语句创建“班级表（class）”。
use studentmanager
go
create table class
(
c_id char(10) primary key,
c_name varchar(10) not null,
c_mentor varchar(10),
dep_id char(10) foreign key references department(dep_id)
)
2.使用SQL语句创建“学生表（student）”。
create table student
(
s_id char(10) primary key,
s_name varchar(10) ,
s_sex char(2) check (s_sex=’男’or s_sex=’女’),
s_borndate datetime,
s_enrolldate datetime,
s_telephone char(11),
s_address varchar(30),
c_id char(10) foreign key references class(c_id)
)
3.使用SQL语句创建“选课表（s_c）”。
create table s_c
(
s_id char(10) references student(s_id)
course_id char(10) references course(course_id),
result decimal(3,1) check (result between 0 and 100),
primary key(s_id,course_id)
)
4.添加“身份证号”字段cardid，字符型，长度为18。
alter table teacher
add cardid char(18)
5.将“教师姓名”字段t_name的长度修改为20。
alter table teacher
alter column t_name varchar(20)
6.设置“职称”字段t_professor的取值为“教授”，“副教授”，“讲师”，“助教”。
alter table teacher
add constraint c_1 check(t_professor in(‘教授’,‘副教授’,‘讲师’,‘助教’))
7.设置“职称”字段的默认值为“助教”。
alter table teacher
add constraint d_1 default ‘助教’ for t_professor

项目4 学生管理系统表中数据的操作

1.插入班级表class的数据。
insert class
values(‘20160101’,‘计算机16-1’,‘张振’，‘01’)
insert class
values(‘20160102’,‘计算机16-2’,‘王丽娜’，‘02’)
insert class
values(‘20160103’,‘计算机16-3’,‘于林’，‘03’)
insert class
values(‘20160104’,‘计算机16-4’,‘王伟’，‘04’)
insert class
values(‘20160105’,‘计算机16-5’,‘张静’，‘05’)
2.插入学生表student的数据。
insert student
value(‘2016010101’,‘白沧溟’,‘男’,‘1995-04-06’,
‘13778906789’,‘辽宁省鞍山市’，‘20160101’)
insert student
value(‘2016010102’,‘孔薇亚’,‘女’,‘1995-05-06’,
‘13778906789’,‘辽宁省鞍山市’，‘20160101’)
insert student
value(‘2016010201’,‘王丽’,‘女’,‘1995-12-06’,
‘13778906789’,‘辽宁省鞍山市’，‘20160102’)
insert student
value(‘2016010202’,‘田园’,‘女’,‘1995-10-06’,
‘13778906789’,‘辽宁省鞍山市’，‘20160102’)
insert student
value(‘2016010203’,‘刘晓’,‘男’,‘1995-04-08’,
‘13778906789’,‘辽宁省鞍山市’，‘20160102’)
3.将学号为2016010101的学生的姓名修改为“白沧溟”。
update student
set s_name=‘白沧溟’
where s_id=‘2016010101’
4.将所有教师的工资降低500元。
update teacher
set t_salary=t_salary-500
5.删除学号“2016010203”同学的选课信息。
delete s_c
where s_id=‘2016010203’
6.删除学号“2016010203”同学的基本信息。
delete student
where s_id=‘2016010203’

项目5 检索学生管理系统表中的数据

1.检索学生基本信息。
select * from student
2.检索学生的学号，姓名，电话号码。
select s_id,s_name,s_telphone
from student
3.检索学生的学号，姓名，电话号码，结果中要显示列名学号，姓名，电话号码。
select s_id as 学号，s_name as 姓名，s_telephone as 电话号码
from student
4.检索学生的学号，姓名及年龄。
select s_id,s_name,year(getdate())-year(s_borndate) as age
from student
5.检索学生所属的班级。
select distinct c_id from student
6.检索所有男生的学号，姓名，出生日期。
select s_id,s_name,year(getdate())-year(borndate)
from student
where s_sex=‘男’
7.检索所有年龄大于22岁的学生的学号和姓名。
select s_id,s_name
from student
where year(getdate())-year(borndate)>22
8.检索没有填写家庭住址的学生的学号和姓名。
select s_id,s_name
from student
where s_address is null
9.检索所有姓“张”的学生的学号和姓名。
select s_id,s_name
from student
where s_name like ‘张%’
10. 检索不姓“张”也不姓“孙”的学生的学号和姓名。
select s_id,s_name
from student
where s_name not like ‘[张孙]%’
11. 检索年龄大于22岁的男生的学号和姓名。
select s_id,s_name
from student
where s_sex=’男’and year(getadte())-year(borndate)>22
12. 检索性别为男或职称为副教授的教师姓名，性别和职称。
select t_name,t_sex,t_professor
from teacher
where s_sex=‘男’or t_professor=‘副教授’
13. 检索年龄在22~25岁的学生的学号和姓名。
select s_id,s_name
from student
where year(getdate())-year(borndate) between 22 and 25
14. 检索学号为2016010101,2016010201,2016020201的学生的姓名。
select s_name
from student
where s_id in(‘2016010101’,’2016010201’,’2016020201’)
15. 检索学生的学号，姓名和出生日期，结果按出生日期降序排列。
select s_id,s_name,s_borndate
from student
order by s_borndate desc
16. 检索0003号课程成绩排在前两名的学生的学号和成绩。
select top s_id,result
from s_c
where course_id=’0003’
order by result desc
17. 检索每个学生的总成绩，结果显示学号和总成绩。
select s_id,sum(result) as 总成绩
from s_c
group by s_id
18. 检索每门课程的平均分，结果显示课程号和平均分。
select course_id 课程号，avg(result) as 平均分
from s_c
group by course_id
19. 检索0003号课程的最高成绩和最低成绩。
select max(result) 最高成绩，min(result) 最低成绩
from s_c
where course_id=’0003’
20. 检索男，女生人数。
select s_sex 性别，count() 人数
from student
group by s_sex
21. 检索平均分大于85分的课程的课程号和平均分。
select course_id 课程号，avg(result) 平均分
from s_c
group by course_id having avg(result)>85
22. 检索学生的学号，姓名和所在班级名。
select s_id,s_name,c_name
from student join class on student.c_id=class.c_id
23. 检索选修“数据库原理及应用”课程的学生的姓名和成绩。
①select s_name,result
form s join s_c on student.s_id=s_c.s_id
jion course on s_c.course_id=course.course_id
where course_name=’数据库原理及应用’
②select s_name,result
from student,s_c,course
where student.s_id=s_c.s_id and s_c.course_id=course.course_id
and course_name=’数据库原理及应用’
24. 查询每门课程的课程号，任课老师姓名及其选课人数
①select a. course_id 课程号，t_name 教师姓名，count()选课人数
from s_c a join t_c b on a.course_id=b.course_id join teacher c on b.t_id=c.t_id
group by a.course_id,t_name
② select a. course_id 课程号，t_name 教师姓名，count(*)选课人数
from s_c a,t_c b, teacher c
where a.course_id= b.course_id and b.t_id=c.t_id
group by a.course_id,t_name
25. 检索与“王丽”同一班级的学生的姓名
select b.s_name
from student a join student b on a.c_id=b.c_id
where a,s_name=’王丽’ and b.s_name<>’王丽’
26. 检索所有学生的成绩情况
select * from student left join s_c on student.s_id=s_c.s_id
27. 检索所有选修课的选修情况
select * from s_c right join course on s_c.course_id=course.course_id
28. 交叉连接检索教师表和部门表
select * from teacher cross join department
29. 查询选修0003 号课程且成绩低于该门课程平均成绩的学生的学号
select s_id
from s_c
where course_name=’0003’and result<(select avg(result) from s_c
where course_id=’0003’)
30. 查询年龄高于20160101班所有学生年龄的其他班的学生的学号和姓名。
select s_id,s_name
from student
where c_id<>’20160101’ and s_borndate<all(select s_borndate from student
where c_id=’20160101)
31. 查询所有成绩大于90分的学生的姓名
select s_name
from student
where s_id in( select s_id
from s_c
where result>90)
32. 查询没有选修“大学英语”的学生的学号和姓名
select s_id,s_name
from student
where s_id not in(select s_id
from s_c
where course_name=’大学英语’)
33. 查询成绩高于平均分的学生的学号和课程号
select s_id，course_id
from s_c a
where result>(select avg(result) from s_c b
where a.course_id=b.course_id)
34. 查询未选修任何课程的学生的姓名。
select s_name
from student
where not exist ( select *
from s_c
where s_c.s_id=student.s_id)
35. 检索出20160101班学生的学号，课程号和成绩并插入表sc20160101
insert sc20160101
select s_c.s_id,course_id.result
from s_c,student
where s_c.s_id=student.s_id and c_id=’20160101’
36. 将20160101班学生的成绩全部提高5分。
update s_c
set result=result+5
where s_id in(select s_id from student
where c_id=’20160101’)
37. 将教师“刘清华”的工资修改为平均工资
update teacher
set t_salary=(select avg(t_salary) from teacher
where t_name=’刘清华’)
38. 删除没有选课的学生的基本信息
update from student
where s_id not in(select s_id
from s_c)

项目6 学生管理系统数据的快速检索

1.使用select into语句实现表的复制，创建数据表class,department,teacher,t_c的副本，完成聚集索引的创建。
select * into class2 from class
select * into department2 from department
select * into teacher2 from teacher
select * into t_c2 from t_c
2.在数据表class2的c_id列上创建聚集索引IX_class2。
create unique clustered index IX_class2
on class2(c_id)
3.在数据表t_c2的t_id列和course_id列上创建复合聚集索引IX_t_c2。
create unique clustered index IX_t_c2
on t_c2(t_id,course_id)
4.在数据表class2的c_name列上创建非聚集索引IX_class2_name。
create notclustered index IX_class2_name
on class2(c_name)
5.在数据表teacher2的t_id列上创建带填充因子的聚集索引IX_teacher2,填充因子值为70。
create unique clustered index IX_teacher2
on teacher2(t_id)
with pad_index,fillfactor=70
6.使用SQL语句对数据表student2更新统计信息，扫描碎片，并进行碎片整理。
update statistics student2 IX_student2
dbcc showcontig(student2, IX_student2)
dbcc indexdefrag(studentmanmger,student2,IX_student2)
7.使用SQL语句删除数据表class2中的非聚集索引IX_class2_name。
drop index class2.IX_class2_name 或
drop index IX_class2_name on class2
8.查看表中索引。
exec sp_helpindex class2

项目7 学生管理系统中视图的操作

1. 创建视图V_teac，存放教师的基本信息，包括教师编号，教师姓名，性别，入职日期，职称，并进行加密处理。
   create view V_teac(教师编号,教师姓名,性别,入职日期,职称)
   with encryption
   as
   select t_id,t_nmae,t_sex,t_entrydate,t_professor
   from teacher
2. 创建视图V_dep，存放职称为“讲师” 的教师基本信息，包括教师编号，性别，职称，基本工资，系部主任。
   create view V_dep(教师编号,性别,职称,基本工资,系部主任)
   as
   select t.t_id,t_sex,t_professor,t_salary,dep_head
   from teacher t,department d
   where t.dep_id=d.dep_id
3. 创建视图V_tc,存放教师授课基本信息，包括教师编号，教师姓名，职称，课程号，课程名称。
   create view V_tc(教师编号,教师姓名,职称,课程号,课程名称)
   as
   select t.t_id,t_name,t_professor,c.course_id,course_name
   from teacher t,t_c tc,course c
   where t.t_id=tc.t_id and c.course_id=tc.course_id
4. 修改视图V_dep，在已有教师基本信息的基础上增加教师姓名，入职日期，西部名称列。
   alter view V_dep(教师编号,教师姓名,性别,入职日期,职称,基本工资,系 部名称,系部主任)
   selectt.t_id,t_name,t_sex,t_entrydate,t_professor,t_salary,dep_name,dep_head
   from teacher t,department d
   where t.dep_id=d.dep_id
5. 修改视图V_tc，在已有教师授课基本信息的基础上增加开学学期列，删除职称列。
   alter view V_tc(教师编号,教师姓名,课程号,课程名称,开课学期)
   as
   select t.t_id,t_name,c.course_id,course_name,term
   from teacher t,t_c tc,course c
   where t.t_id=tc.t_id and c.course_id=tc.course_id
6. 在视图V_dep的基础上创建视图V_dep2,存放“电子与信息工程系”教师的基本信息。
   create view V_dep2
   as
   select * from V_dep
   where 系部名称=‘电子与信息工程系’
7. 在视图V_tc的基础上创建视图V_tc2,存放第一学期开课的教师的授课信息。
   create view V_tc2
   as
   select * from V_tc
   where 开课学期=‘1’
8. 在视图V_teac上查询“副教授”，和“教授”职称的教师信息。
   select * from V_teac where 职称 in(‘副教授’,‘教授’)
9. 在视图V_teac上录入一条记录。
   insert into V_teac values(‘0301’,‘王思宇’,‘男’,‘2005-9-1’,‘副教授’)
   10.在视图V_dep上录入一条记录。
   insert into V_dep values(‘0302’,‘赵阳’,‘女’,‘2010-9-1’,‘讲师’,‘5000’,‘建筑工程 系’,‘王天’)
   11.在视图V_teac上修改记录，将教师“张振”的职称修改为“副教授”。
   update V_teac set 职称=‘副教授’where 教师姓名=‘张振’
   12.在视图V_dep上删除教师“陈平”的信息。
   delect from V_dep where 教师姓名=‘陈平’

项目8 学生管理系统数据库中的存储过程

1. 班导师经常会查询学生的学号，姓名，电话号码和家庭住址等信息（需设置别名），创建存储过程PD1_s。
   create procedure PD1_s1
   as
   begin
   select s_id‘学号’,s_name‘姓名’,s_telephone‘电话号码’,s_address
   ‘家庭住址’
   from student
   end
2. 系主任要查询“计算机16-1”班的学生姓名，班级，课程名称，成绩等信息（需设置别名），创建存储过程PD1_s2。
   create procedure PD1_s2
   as
   begin
   select s_name ‘姓名’,c_name,course_name,result
   from student s,course c,s_c sc,class cl
   where s.s_id=sc.s_id and c.course_id=sc.course_id and
   s.c_id=cl.c_id and c_name=‘计算机 16-1’
   end
3. 创建存储过程PD1_s3，若存在学号为“2016010203”的学生记录，则删除此学生的基本信息及其选课信息，同时显示学生表及成绩表中的信息，若不存在此学生，则“显示没有这个学生！”。
   create procedure PD1_s3
   as
   begin
   if exists(select * from student where s_id=‘2016010203’)
   begin
   delete from s_c where s_id=‘2016010203’
   delete from student where s_id=‘2016010203’
   select * from student
   select * from s_c
   end
   else
   print‘没有这个学生！’
   end
4. 使用系统存储过程sp_helptext 查看存储过程PD3_s2的定义文本。
   exec sp_helptext PD1_s3
5. 创建存储过程PD1_s4，检索学生的学号，姓名，性别，出生日期等信息（需设置别名），对定义语句进行加密处理，再使用系统存储过程查看其定义文本。
   create procedure PD1_s4
   with encryption
   as
   begin
   select s_id ‘学号’,s_name‘姓名’,s_sex‘性别’,s_borndate‘出生
   日期’
   from student
   end
6. 查询指定学生姓名的学生学号，姓名，电话号码和家庭住址等信息，创建存储过程PD2_s1,使用参数“王丽”，执行存储过程。
   create procedure PD2_s1
   @s_name char(10)
   as
   begin
   select s_id ‘学号’，s_name ‘姓名’,s_telephone‘电话号
   码’,s_address ‘家庭住址’
   from student
   where s_name=@sname
   end
   7.创建存储过程PD2_s2,检索指定学生姓名的学生学号，姓名，课程号，成绩信息，使用参数“王丽”，执行存储过程。
   create procedure PD2_s2
   @sname char(10)
   as
   begin
   select s.s_id ‘学号’,s_name ‘姓名’,course_name ‘课程号’,result ‘成绩’
   from student s,s_c sc
   where s.s_id=sc.s_id and s_name=@sname
   end
7. 创建存储过程PD3_s1，向student 表中插入记录，性别默认值为“女”，若没有指定家庭住址，则默认为“辽宁大连”，执行存储过程。
   create procedure PD3_s1
   @sno char(10),@s_name varchar (10),@s_sex char(2)=‘女’，
   @sborndate datetime,@senrolldate datetime,@ stelephone char(11),
   @saddress varchar(30)=‘辽宁大连’,@cid char(10)
   as
   begin
   insert into student
   values(@sno,@sname,@ssex,@sborndate,@senrolldate,@stelephone,@sa
   ddress,@cid)
   end
8. 创建存储过程PD3_s2，根据某学生学号输出该学生的姓名，联系方式和家庭住址，使用2016010203作为实参，执行存储过程。
   create procedure PD3_s2
   (
   @sno char(10),
   @sname varchar(10) output
   @telephone char(11) output
   @address varchar (30) output
   )
   as
   select @sname=s_name,@telephone=s_telephone,@address=s_address
   from student
   where s_id=@sno
   select ‘姓名’=@sname,‘联系方式’=@telephone,‘家庭住址’=@address

项目9 学生管理系统数据库中的触发器

1.创建触发器tr1_s1,在录入学生信息时同时显示表中所有记录，并测试其语句。
create trigger tr1_s1
on student
for insert
as
begin
select * from student
end
2.创建触发器tr1_s2，在录入学生信息的时同时将此学生的学号及大学英语课程号录入到成绩表中（大学英语是公共必修课），同时显示录入的记录信息，并测试语句。
create trigger tr1_s2
on student
for insert
as
begin
declare @sno char(10),@ con char(10)
select @sno=s_id from inserted
select @cno=course_id from course where course_name=‘大学英语’
insert into s_c values(@sno.@con ,null)
select * from s_c where s_id=@sno
end
3.创建触发器tr1_s3，在删除一个毕业学生信息的同时删除这个学生的成绩记录，若没有此学生，则给出提示信息，并执行测试语句。
create trigger tr1_s3
on student
for delete
as
begin
if exists(select s_id from deleted)
begin
delect from s_c where s_id=(select s_id from deleted)
select * from student
end
else
print‘不存在这个学生的信息’
end
4.创建触发器tr1_s4，当更新某个学生时更新这个学生的选课信息，并执行测试语句。
create trigger tr1_s4
on student
for update
as
begin
if exists(select s_id from deleted)
begin
update s_c set s_id=(select s_id from inserted)
where s_id=(select s_id from deleted)
select s.s_id,s_name,result
from student s,s_c sc
where s.s_id=sc.s_id and s.s_id=(select s_id from inserted)
end
else
print‘没有这个学生的选课记录’
end
5.创建触发器tr1_s5，在向学生表插入，更新记录时检测出生日期与注册日期的合法性，即出生日期应早于注册日期，若不合法给出提示，并撤销此操作，执行测试语句。
create trigger tr1_s5
on student
after insert,update
as
begin
declare @date0 smalldatetime
declare @date1 smalldatetime
set @date0=(select s_borndate from inserted)
set @date1=(select s_enrolldate from inserted)
if @date0>@date1
print‘出生日期>注册日期，student表禁止此操作’
rollback transaction
end
6.创建触发器tr2_s，在成绩插入到s_c表时验证是否有效，并作出相应处理，执行测试语句。
create trigger tr2_s
on s_c
instead of insert
as
begin
declare @score int
select @score=result from inserted
if @score<0 or @score>100
begin
–rollback transaction
print‘成绩必须在0到100之间’
end
else
begin
print‘已经成功插入记录’
insert into s_c select * from inserted
select * from s_c where s_id=(select s_id from inserted)
end
end
7.创建触发器tr3_DDL1,防止对studentmanager数据库中任何表的修改或删除操作，并执行测试语句。
create trigger tr3_DDL1
on database
for drop_table,alter_table
as
print‘不允许修改或删除数据库内的表’
rollback transaction
8.创建触发器tr3_DDL2,禁止在SQLserver 服务器中删除数据库，并执行测试语句。
create trigger tr3_DDL2
on all server
for drop database
as
print‘对不起，您不能删除数据库，请联系dba’
rollback transaction