任务6 教务数据库的索引设计
课堂实践1
1． 在major表的majorid列上创建唯一性聚集索引index_majorid。
create unique clustered index index_majorid on major(majorid)
2． 在class表的classroom列上创建唯一性非聚集索引index_classroom。
create unique clustered index index_classroom on class(classroom)
3． 在学生表的studentname列上创建非聚集索引index_studentname。
crecreate nonclustered index index_studentname on student(student_name)
4． 在教师表的profession列和teachername列上创建复合非聚集索引
index_profession_teachername。
create nonclustered index index_profession_tezchername on teacher(profession,teacher_name)
5． 创建学生表的全文索引并进行查询(可上网查找采用SQL语句进行创建全文索引的
相关资料，用SQL语句进行操作)。
6． 根据教务管理的查询需要自行设计并创建3-5个索引。 7． 认真记录各题操作后的结果情况。
课堂实践2
8. 将teacher表的索引文件index_teacher_phone改名为index_t_p。
create nonclustered index index_teacher_phone on teacher(telephone)
sp_rename 'teacher.index_teacher_phone','teacher.index_t_p'
9. 删除上面的index_t_p索引。
drop index teacher.index_t_p
10. 查看课堂实践1中所创建的索引的基本信息。
sp_helpindex student
exec sp_helpindex teacher
11. 选择其中的几个进行重命名和删除操作。
drop index teacher.index_profession_tezchername sp_rename 'student.index_studentname','student.name'
12. 选择一个索引，查看它的数据和索引的碎片信息。 dbcc showcontig(student,index_student_id) 13. 选择一个索引进行重建。
dbcc dbreindex ('book',index_book_name)
14. 选择一个表或一个索引更新索引的分布统计页。 update statistics book
15. 认真记录各题操作后的结果情况。
任务7 教务数据库查询设计
课堂实践1
1． 查询教师的所有信息。
use jwgl go
select * from teacher
2． 查询课程表的课程代号和课程名称。
select course_id,course_name from course
3． 显示书名，出版社，作者及价格。
select book_name,publish_company,author,price from book
4． 将所有书的价格打九折显示。
select book_name , price*0.9 from book
5． 查询87年以后出生的学生的姓名、性别、班级及出生日期。
select student_name,sex,class_id,birth from student where year(birth)>1983
6． 查询j0601班的男生的所有信息
select * from student
where sex=1 and class_id='g99402'
7． 按出生日期排序显示学生的姓名、性别、出生日期及住址。 select * from student order by birth 8． 查询姓?王?的名字是三个字的同学信息。
select * from student
where student_name like '张%'
9． 查询班级为j0602的同学成绩并按从高到低排列。
select course_id,student.student_id,grade,class_id from student_course,student
where student.class_id ='g99402' order by grade desc
10.查询女生的信息并生成一张新表。
select * into #temp_girl from student where sex=0 use tempdb go
select *from #temp_girl
11.根据需要及课程知识自行设计几个查询并写出SQL查询语句。 12.认真记录各题操作后的结果情况。
课堂实践2
1． 查询 每个学生的学号、姓名、班级、课程名称、成绩及总分。
select student.student_id,student.student_name, class_id,course_name,grade
from student,course,student_course
where student.student_id=student_course.student_id and student_course.course_id=course.course_id order by student.student_id
compute sum(grade) by student.student_id
2． 查询 各班的各门功课的最高分、最低分和平均分。
select substring(student_id,1,6),course_name,'最高分'=max(grade),'最低分'=min(grade),'平均分'=avg(grade) from student_course,course
group by substring(student_id,1,6),course_name order by course_name
3． 检索86年、87年出生的学生姓名、班级、性别、年龄和出生日期。
create function js_old
(@vardate datetime , @curdate datetime) returns tinyint as begin
return datediff(yyyy,@vardate,@curdate)
end
select student_name,class_id,sex,birth,
'年龄'= dbo.js_old(birth,getdate()) from student where year(birth)=1983 or year(birth)=1984
4． 查询 各位教师的姓名、任教课程及相应班级及上课地点。
select teacher_name,course_id,course_classroom from teacher,teacher_course_class
where teacher.teacher_id=teacher_course_class.teacher_id
5． 统计不同职称的教师的人数，并查询 教师姓名、职称。
select teacher_name,profession from teacher order by profession
compute count(profession)by profession
6． 查询 每个学生的学号,总分。
select student_id,sum(grade) from student_course group by student_id
7． 按学号排序查询 每个学生的学号,姓名,课程号及成绩。
select student.student_id,student_name,course_id,grade from student,student_course
where student.student_id=student_course.student_id order by student.student_id
8． 查询 每个学生的学号,姓名,课程名称及成绩。
select student.student_id,student_name,course_name,grade from student,student_course,course
where student.student_id=student_course.student_id and student_course.course_id=course.course_id
9． 查询 成绩不及格,及80分以上的学生学号，姓名，课程名称及成绩。
select A.student_id,student_name,course_id,grade from student A,student_course B where grade < 60 or grade > 80
11.根据需要及课程知识自行设计几个查询并写出SQL查询语句。 12.认真记录各题操作后的结果情况。
任务8 教务数据库的视图设计
课堂实践1
1. 创建一个只包含j0602班同学的基本信息的视图。
create view student_class_view2 as
select * from student where class_id ='g99402'
2. 创建一个视图，包含表teacher中教师的姓名、性别、出生日期、职称信息。
create view teacher_view as
select teacher_name,sex,birth,profession from teacher
3. 在上一题视图的基础上创建包含职称为副教授职称的老师的视图。
create view teacher_view1 as
select * from teacher_view where profession='副教授'
4. 创建一个包含不及格同学及90分以上同学的学号、姓名、课程名称及成绩的视图。
create view student_score as
select student.student_id,student_name,course_name,grade from student join student_course on
student.student_id = student_course.student_id join course
on course.course_id =student_course.course_id
where grade <60 or grade>90
5. 创建一个视图，是由表course、book及majorclasscourse创建的一个显示“j0601”班所开课程的课程名称，所用教材的教材名、出版社及作者的视图。
create view g99402_view as
select distinct substring(student_id,1,6) as '班级', course_name,book_name,publish_company,author from book,course,student_course
where (substring(student_id,1,6)='g99402' and book.book_id=course.book_id
and course.course_id=student_course.course_id)
6．根据需要自行选择创建几个视图。 7. 认真记录各题操作后的结果情况。
课堂实践2
1. 选择一个上面创建的视图，查看视图的信息。
sp_help student_class_view2
sp_helptext student_class_view2
2. 选择一个上面创建的视图，修改删除其中的一列 3. 选择一个上面创建的视图，将其修改成加密视图
alter view student_score with encryption as
select student.student_id,student_name,course_name,grade from student join student_course on
student.student_id = student_course.student_id join course
数据库复习1
