天津理工大学数据库实验复杂SQL数据操作.doc

实验报告
学院（系）名称：计算机与通信工程学院
姓名
xx
学号
x
专业
信息安全
班级
2班
实验名称
复杂SQL数据操作
课程名称
数据库系统
课程代码
0668026
实验时间
2014.10.22 10:00-11:30
实验地点
7-219
批改意见
成绩

教师签字：
1. 实验目的
1) 熟悉各数据库的交互式SQL工具;
2) 熟悉通过SQL语言对数据库进行操作
3) 熟悉通过SQL语言对数据进行控制（授权和权力回收）

2. 实验环境
MS SQL server

3. 实验要求
(1) 将学习了数据库课程的学生成绩加5分。
(2) 将计算机系学习了2号课程的学生成绩置0。
(3) 将李勇的数据库成绩改为85。
(4) 将选修了2号课程且成绩为空的选课记录删除。
(5) 从课程表中删除在选课表中没有选课记录的课程记录。
(6) 删除计算机系学生选修了数据库课程的选课记录。
(7) 求各系的系名及男女生人数并将结果保存到另一个表中。
(8) 将平均成绩80分以上的学生的学号，选学的课程数和平均成绩保存到另一个表中。
(9) 创建一个视图，查询没有选修课程的学生学号、姓名和所在系，并利用该视图查询所有没有选修课程的学生信息。
(10) 创建一个给出学生的姓名、课程名和成绩的视图，并利用该视图查询某个学生学习的课程名和成绩。
11）创建一个视图，求各门课程的课程号、选课人数、平均分、最高分，并利用该视图查询1号课程的选课人数、平均分、最高分。
(12) 创建一个视图，求选修了2号课程且成绩高于该门课程平均分的学生学号和成绩。
(13)创建用户user1，将学生表的select权限赋给用户user1，然后以user1登录，检查权限情况。
(14)收回用户user1的对学生表的select权限，然后以user1登录，检查权限情况。

4. 实验过程记录（源程序、测试用例、测试结果及心得体会等）
(1) 将学习了数据库课程的学生成绩加5分。
update sc
set grade=grade+5
where cno=( select cno from course where cname='数据库');
select *from sc

(2) 将计算机系学习了2号课程的学生成绩置0。
update sc
set grade=0
where '2' =(select Cno from student
where sdept='CS' AND Student.Sno = SC.Sno);
select *from sc

(3) 将李勇的数据库成绩改为85。
update sc
set grade=85
where sno=(select sno from student where sname='李勇')
and cno=(select cno from course where cname='数据库');
select *from sc

(4) 将选修了2号课程且成绩为空的选课记录删除。
delete
from sc
where cno=2 and grade=NULL;
select * from sc

(5) 从课程表中删除在选课表中没有选课记录的课程记录。
delete
from course
where not exists
(select *from sc where o=o)
select *from course

(6) 删除计算机系学生选修了数据库课程的选课记录。
delete from sc
where cno=( select cno from course where cname='数据库')and
sno in(select sno from student where sdept='CS')
select * from SC

(7) 求各系的系名及男女生人数并将结果保存到另一个表中。
create table s1(sdept char (20),ssex char(2),c1 int)
insert into s1
select sdept, ssex,count(ssex)
from student
group by sdept,ssex;
select * from s1

(8) 将平均成绩80分以上的学生的学号，选学的课程数和平均成绩保存到另一个表中。
create table s2(sno int Not Null,c int, avgage int);
insert into s2
select sno,count(cno),avg(grade)from sc
group by sno
having avg(grade)>80
select * from s2

(9) 创建一个视图，查询没有选修课程的学生学号、姓名和所在系，并利用该视图查询所有没有选修课程的学生信息。
create view No_sc_student
as
select sno,sname,ssex,sage,sdept from student
where sno not in(select distinct Sno from SC )
select * from No_sc_student

(10) 创建一个给出学生的姓名、课程名和成绩的视图，并利用该视图查询某个学生学习的课程名和成绩。
create view sname_cname_grade
as
select sname,cname,grade from Student ,Course ,SC
where Student.Sno=SC.Sno and SC.Cno =Course.Cno
select * from sname_cname_grade

11）创建一个视图，求各门课程的课程号、选课人数、平均分、最高分，并利用该视图查询1号课程的选课人数、平均分、最高分。
create view CCAM
as
select cno,COUNT(sno) count1,AVG(grade) avge,MAX(grade) max1 from SC
group by Cno
select * from ccam
select count1,avge,max1 from ccam
where cno='1'

(12) 创建一个视图，求选修了2号课程且成绩高于该门课程平均分的学生学号和成绩。
create view Cno_2(sno,Grade)
as
select Sno,Grade from SC
where Cno='2' and grade >(select AVG(Grade)from SC where Cno='2' )
select * from Cno_2

(13)创建用户user1，将学生表的select权限赋给用户user1，然后以user1登录，检查权限情况。
create role use1 /*创建角色use1*/

grant select on Student to use1

(14)收回用户user1的对学生表的select权限，然后以user1登录，检查权限情况。
revoke select on student from use1;

第12页 共12页