天津理工大学数据库实验复杂SQL数据操作.docx
《天津理工大学数据库实验复杂SQL数据操作.docx》由会员分享,可在线阅读,更多相关《天津理工大学数据库实验复杂SQL数据操作.docx(11页珍藏版)》请在冰点文库上搜索。
天津理工大学数据库实验复杂SQL数据操作
实验报告
学院(系)名称:
计算机与通信工程学院
姓名
xx
学号
x
专业
信息安全
班级
2班
实验名称
复杂SQL数据操作
课程名称
数据库系统
课程代码
0668026
实验时间
2014.10.2210:
00-11:
30
实验地点
7-219
批改意见
成绩
教师签字:
1.实验目的
1)熟悉各数据库的交互式SQL工具;
2)熟悉通过SQL语言对数据库进行操作
3)熟悉通过SQL语言对数据进行控制(授权和权力回收)
2.实验环境
MSSQLserver
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分。
updatesc
setgrade=grade+5
wherecno=(selectcnofromcoursewherecname='数据库');
select*fromsc
(2)将计算机系学习了2号课程的学生成绩置0。
updatesc
setgrade=0
where'2'=(selectCnofromstudent
wheresdept='CS'ANDStudent.Sno=SC.Sno);
select*fromsc
(3)将李勇的数据库成绩改为85。
updatesc
setgrade=85
wheresno=(selectsnofromstudentwheresname='李勇')
andcno=(selectcnofromcoursewherecname='数据库');
select*fromsc
(4)将选修了2号课程且成绩为空的选课记录删除。
delete
fromsc
wherecno=2andgrade=NULL;
select*fromsc
(5)从课程表中删除在选课表中没有选课记录的课程记录。
delete
fromcourse
wherenotexists
(select*fromscwhereo=o)
select*fromcourse
(6)删除计算机系学生选修了数据库课程的选课记录。
deletefromsc
wherecno=(selectcnofromcoursewherecname='数据库')and
snoin(selectsnofromstudentwheresdept='CS')
select*fromSC
(7)求各系的系名及男女生人数并将结果保存到另一个表中。
createtables1(sdeptchar(20),ssexchar
(2),c1int)
insertintos1
selectsdept,ssex,count(ssex)
fromstudent
groupbysdept,ssex;
select*froms1
(8)将平均成绩80分以上的学生的学号,选学的课程数和平均成绩保存到另一个表中。
createtables2(snointNotNull,cint,avgageint);
insertintos2
selectsno,count(cno),avg(grade)fromsc
groupbysno
havingavg(grade)>80
select*froms2
(9)创建一个视图,查询没有选修课程的学生学号、姓名和所在系,并利用该视图查询所有没有选修课程的学生信息。
createviewNo_sc_student
as
selectsno,sname,ssex,sage,sdeptfromstudent
wheresnonotin(selectdistinctSnofromSC)
select*fromNo_sc_student
(10)创建一个给出学生的姓名、课程名和成绩的视图,并利用该视图查询某个学生学习的课程名和成绩。
createviewsname_cname_grade
as
selectsname,cname,gradefromStudent,Course,SC
whereStudent.Sno=SC.SnoandSC.Cno=Course.Cno
select*fromsname_cname_grade
11)创建一个视图,求各门课程的课程号、选课人数、平均分、最高分,并利用该视图查询1号课程的选课人数、平均分、最高分。
createviewCCAM
as
selectcno,COUNT(sno)count1,AVG(grade)avge,MAX(grade)max1fromSC
groupbyCno
select*fromccam
selectcount1,avge,max1fromccam
wherecno='1'
(12)创建一个视图,求选修了2号课程且成绩高于该门课程平均分的学生学号和成绩。
createviewCno_2(sno,Grade)
as
selectSno,GradefromSC
whereCno='2'andgrade>(selectAVG(Grade)fromSCwhereCno='2')
select*fromCno_2
(13)创建用户user1,将学生表的select权限赋给用户user1,然后以user1登录,检查权限情况。
createroleuse1/*创建角色use1*/
grantselectonStudenttouse1
(14)收回用户user1的对学生表的select权限,然后以user1登录,检查权限情况。
revokeselectonstudentfromuse1;