SQL实验的题目和答案全解

1、 数据库实验总汇二、使用SQL Server 工具在管理数据库系统和运行SQL查询的两个主要工具是企业管理器和查询分析器三、实验目的 1 熟悉数据库的交互式SQL工具。 2 熟悉通过SQL对数据库进行操作。 3 完成上机练习。四、实验内容 (打为选做题)Part A1使用CREATE语句创建数据库2. 使用CREATE语句创建基本表3更改基本表的定义，增加列，修改列的数据类型。4创建表的索引，取消表的索引1、数据库的建立 使用SQL 语句建立关系数据库schooldemo2、基本表的建立在数据库schooldemo下，使用SQL 语句建立关系数据库表：人员表PERSON（Pno，Pname,

2、Page）,房间表ROOM（Rno,Rname,Rarea）,表PR(Pno,Rno,Date).其中，表PERSON ：Pno（CHAR（8），主码，非空，唯一），Pname（CHAR（20），非空），Page（INT）；表ROOM：Rno（CHAR（8），主码，非空，唯一），Rname（CHAR（20），Rarea（FLOAT（10）；表PR：Pno（CHAR（8），非空，唯一），Rno（CHAR（8），非空，唯一），Date（DATETIME）；（三个表之间有联系）3、更改基本表的定义，增加列，删除列，修改列的数据类型。更改表PERSON，增加属性Ptype（类型是CHAR ，长度为10

3、），把表ROOM中的属性Rname的数据类型改为长度为40。4、创建表的索引，取消表的索引为ROOM表创建按Rno降序排列的索引创建PERSON表按Pname升序排列的唯一性索引取消PERSON表Pname升序索引5、删除表ROOM实验过程(1.a)1、数据库的建立 使用SQL 语句建立关系数据库schooldemocreate database schooldemoon(name=schooldemo,filename=e:123school_data.mdf,size=5mb,maxsize=15mb,filegrowth=10%)log on (name=schooldemo_log,f

4、ilename=e:123schooldemo.ldf,size=5mb,maxsize=15mb,filegrowth=4mb)2、基本表的建立在数据库schooldemo下，使用SQL 语句建立关系数据库表：人员表PERSON（Pno，Pname, Page）,房间表ROOM（Rno,Rname,Rarea）,表PR(Pno,Rno,Date).其中，表PERSON ：Pno（CHAR（8），主码，非空，唯一），Pname（CHAR（20），非空），Page（INT）；表ROOM：Rno（CHAR（8），主码，非空，唯一），Rname（CHAR（20），Rarea（FLOAT（10）；表P

5、R：Pno（CHAR（8），非空，唯一），Rno（CHAR（8），非空，唯一），Date（DATETIME）；（三个表之间有联系）create table person(pno char (8)primary key,pname char (20),page int);create table room(rno char (8) primary key,rname char(20),rarea float(10);create table pr(pno char (8),rno char (8),date datetime,primary key(pno,rno),foreign key(pn

6、o) references person(pno),foreign key(rno) references room(rno);3、更改基本表的定义，增加列，删除列，修改列的数据类型。更改表PERSON，增加属性Ptype（类型是CHAR ，长度为10），把表ROOM中的属性Rname的数据类型改为长度为40。alter table person add ptype char(10);alter table room alter column rname char (40);4、创建表的索引，取消表的索引为ROOM表创建按Rno降序排列的索引创建PERSON表按Pname升序排列的唯一性索引取

7、消PERSON表Pname升序索引create index rorno on room(rno desc);create unique index pepname on person(pname asc);drop index person.pepname5、删除表ROOMdrop table room(要先删约束条件)Part B对School 数据库为例，在该数据库中存在四张表格，分别为：l 表STUDENTS(sid,sname,email,grade);l 表TEACHERS(tid,tname,email,salary);l 表COURSES(cid,cname,hour);l 表C

8、HOICES(no,sid,tid,cid,score)。在数据库中，存在这样的关系：学生可以选择课程。在表CHOICES中保存学生的选课记录。要求对数据库进行查询操作：（1） 查询年级为2001的所有学生的名称并按照编号升序排列（2） 查询学生的选课成绩合格的课程的成绩，并把成绩换算成为积点（60分对应积点为1分，每增加1分，积点增加0.1）（3） 查询课时是48或64的课程的名称（4） 查询所有课程记录中含有data的课程编号（5） 查询所有选课记录的课程号（要求不重复显示）（6） 统计所有老师的平均工资（7） 查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列（8） 统计

9、各个课程的选课人数和平均成绩（9） 查询至少选修了三门课程的学生编号（10）查询编号800009026的学生所选的全部课程的课程名和成绩（11）查询所有选了database的学生的编号（12）求出至少被两个学生选修的课程编号（13）查询选修了编号800009026的学生所选的某个课程的学生编号（14）查询学生的基本信息及选修课程编号和成绩（15）查询学号850955252的学生的姓名和选修的课程名称及成绩（16）查询与学号850955252的学生同年级的所有学生资料（17）查询所有有选课的学生的详细信息（18）查询没有学生选的课程的编号（19）查询选修了课程名为C+的学生学号和姓名（20）找出

10、选修课程成绩最好的选课记录。（21）找出和课程UML或课程C的课时一样的课程名称（22）查询所有选修编号10001的课程的学生的姓名（23）查询选修了所有课程的学生姓名（24）利用集合运算，查询选修课程C+或课程Java的学生的学号（25）利用集合交运算，查询既选修课程C+又选修课程Java的学生的学号（26）利用集合减运算，查询选修课程C+而没有选修课程Java的学生的学号实验过程（1.b）（1）查询年级为2001的所有学生的名称并按照编号升序排列Select snamefrom studentswhere grade=2001order by sid;（2）查询学生的选课成绩合格的课程的成

11、绩，并把成绩换算成为积点（60分对应积点为1分，每增加1分，积点增加0.1）Select cid,score,1+0.1*(score-60) jidianfrom choiceswhere score=60;（3）查询课时是48或64的课程的名称select cnamefrom courseswhere hour=48or hour=64;（4）查询所有课程记录中含有data的课程编号select cidfrom courseswhere cname like%data%;（5）查询所有选课记录的课程号（要求不重复显示）select distinct cidfrom choices;（6）统

12、计所有老师的平均工资select avg(salary)from teachers;（7）查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列 答案：（241917）select tid,avg(score)from choicesGROUP BY tid ，cidorder by avg(score) desc;（8）统计各个课程的选课人数和平均成绩select cid,count(distinct sid),avg(score)from choicesgroup by cid;（9）查询至少选修了三门课程的学生编号select sidfrom choicesgroup by

13、sidhaving count(cid)=3;或having count(*)2；（10）查询编号800009026的学生所选的全部课程的课程名和成绩select distinct cname ,scorefrom courses,choiceswhere sid=800009026and courses.cid=choices.cid;（11）查询所有选了database的学生的编号select sidfrom choices ,courseswhere cname=databaseand choices.cid=courses.cid;（12）求出至少被两个学生选修的课程编号select

14、cidfrom choicesgroup by cidhaving count(distinct sid)=2;（13）查询选修了编号800009026的学生所选的某个课程的学生编号 答 案：（16481）select distinct sidfrom choices where cid in (select cidfrom choiceswhere sid=800009026)and sid800009026;（14）查询学生的基本信息及选修课程编号和成绩select students.sid,sname,email,grade,cid,scorefrom students,choicesw

15、here students.sid=choices.sid;（15）查询学号850955252的学生的姓名和选修的课程名称及成绩select sname,cname,scorefrom students,courses,choiceswhere choices.sid=850955252and students.sid=choices.sid andcourses.cid=choices.cid;（16）查询与学号850955252的学生同年级的所有学生资料法一：select *from studentswhere grade=(select grade from studentswhere

16、sid=850955252);法二：select students.*from students where grade in(select gradefrom studentswhere sid in(select sidfrom studentswhere sid=850955252);（17）查询所有有选课的学生的详细信息select students.sid,sname,email,grade,no ,cid,scorefrom students,choiceswhere cid is not null and students.sid=choices.sid;（18）查询没有学生选的

17、课程的编号 答案：（空）select cidfrom choicesgroup by cidhaving count(sid)=0;（19）查询选修了课程名为C+的学生学号和姓名法一：select students.sid,students.snamefrom students,courses,choiceswhere choices.sid in(select choices.sidfrom choiceswhere courses.cid=(select courses.cidfrom courseswhere cname=c+and students.sid=choices.sidand

18、 courses.cid=choices.cid);法二：select sid,snamefrom studentswhere sid in(select sidfrom choiceswhere cid in(select cidfrom courseswhere cname=c+)； （20）找出选修课程成绩最好的选课记录。(好)select *from choiceswhere score in(select max(score)from choices);（21）找出和课程UML或课程C的课时一样的课程名称答案：（7门）select cnamefrom courseswhere hou

19、r in(select hourfrom courseswhere cname=c+ or cname=c+)；（22）查询所有选修编号10001的课程的学生的姓名法一：select snamefrom studentswhere sid in(select sidfrom choiceswhere cid=10001)法二：select students.snamefrom choices,studentswhere cid=10001 and students.sid=choices.sid; & （23）查询选修了所有课程的学生姓名select snamefrom studentswhe

20、re not exists(select*from courseswhere not exists(select *from choiceswhere sid=students.sid and cid=courses.cid)；（24）利用集合运算，查询选修课程C+或课程Java的学生的学号select sidfrom choiceswhere sid in(select sidfrom courseswhere cname=c+)unionselect sidfrom choiceswhere sid in(select sidfrom courseswhere cname=java)（25

21、）利用集合交运算，查询既选修课程C+又选修课程Java的学生的学号 答案：（306）Select distinct sidFrom choices,coursesWhere courses.cid=choices.cidAnd ame=c+ And sid in(Select distinct sidFrom choices,coursesWhere courses.cid=choices.cid and ame=java)select sidfrom choiceswhere sid in(select sidfrom courseswhere cna

22、me=c+)intersectselect sidfrom choiceswhere sid in(select sidfrom courseswhere cname=java）（26）利用集合减运算，查询既选修课程C+而没有选修课程Java的学生的学号 答案：（5570）Select distinct sidFrom choices,coursesWhere courses.cid=choices.cidAnd ame=c+ And sid not in(Select distinct sidFrom choices,coursesWhere courses.cid=ch

23、oices.cid and ame=java)select sidfrom choiceswhere sid in(select sidfrom courseswhere cname=c+)exceptselect sidfrom choiceswhere sid in(select sidfrom courseswhere cname=java）Part C数据更新：1 使用语句向STUDENTS表里插入元组（编号：700045678，名字：LiMing。EMAIL： LX, 年级：1992）2 对每个课程，求学生的选课人数和学生的平均成绩，并把结果存入

24、数据库。使用INSERT INTO子查询的方法实现3 在STUDENTS表中使用SQL语句将姓名为LiMing的学生的年级改为20024 在TEACHERS表中使用SQL与将所有教师的工资加500元5 将姓名为zapyv的学生的课程C的成绩加上5分6 在STUDENTS表中使用SQL语句删除姓名为LiMing的学生信息7 删除所有选修课程java的选课记录8 对COURSES 表做删去时间48的元组的操作，讨论其操作视图：1、 要求创建一个行列子集视图CS，给出选课成绩合格的序号、学生编号、所选课程号、及课程成绩。2、 创建基于多个基表的视图SCT，这个视图由学生姓名和他所选修的课程名及讲授该

25、课程的教师姓名构成3、 创建带表达式的视图SCC，由学生姓名及所选课程名和所有课程成绩都比原来多5分这几个属性组成4、 创建分组视图，将学生的学号及他的平均成绩定义为一个视图5、 创建一个基于视图的视图，基于（1）中建立的视图，定义一个包括学生编号、学生所选课程数目和平均成绩的视图6、 查询所有选修课程software engineering的学生姓名7、 插入元组（600000000,823069829，10010，59）到视图CS中。若是在视图的定义中存在WITH CHECK OPTION子句对插入操作有什么影响8、 将视图CS（包含定义WITH CHECK OPTION）中，所有课程编号

26、为10010的课程的成绩都减去5分。这个操作数据库是否会正确，为什么？如果加上5分（原来95分以上的不变）呢？9、 在视图CS（包含定义WITH CHECK OPTION）删除编号804529880学生的记录，会产生什么结果？10、 取消视图SCT和视图CS实验过程（1.c）Part C数据更新：1 使用语句向STUDENTS表里插入元组（编号：700045678，名字：LiMing。EMAIL： LX, 年级：1992）insertinto students(sid,sname,email,grade)values(700045678,liming,LX,

27、1992);2对每个课程，求学生的选课人数和学生的平均成绩，并把结果存入数据库。使用INSERT INTO子查询的方法实现create table s_a(rs int,avg_scores float(10) primary key);insert into s_a(rs,avg_scores)select count(distinct sid),avg(score)from choicesgroup by cid;3在STUDENTS表中使用SQL语句将姓名为LiMing的学生的年级改为2002update studentsset grade=2002where sname=liming;

28、4在TEACHERS表中使用SQL与将所有教师的工资加500元update teachersset salary=salary+500;5将姓名为zapyv的学生的课程C的成绩加上5分update choicesset score=score+5where sid+cid in(select sid,cidfrom students,courses,choiceswhere sname=zapyvand cname=cand students.sid=choices.sid and courses.cid=choices.cid);update choicesset score=score+5

29、where sid in(select sidfrom studentswhere sname=zapyv)and cid in(select cidfrom courseswhere cname=c);6在STUDENTS表中使用SQL语句删除姓名为LiMing的学生信息delete from studentswhere sname=LiMing;7删除所有选修课程java的选课记录delete from choiceswhere cid in(select cidfrom courseswhere cname=java);8对COURSES 表做删去时间48的元组的操作，讨论其操作dele

30、te from courseswhere hour=60with check option2、创建基于多个基表的视图SCT，这个视图由学生姓名和他所选修的课程名及讲授该课程的教师姓名构成create view sct(sname,cname,tname)asselect sname,cname,tnamefrom courses,students,choices,teacherswhere courses.cid=choices.cid andstudents.sid=choices.sid and teachers.tid=choices.tidwith check option3、创建带表

31、达式的视图SCC，由学生姓名及所选课程名和所有课程成绩都比原来多5分这几个属性组成create view scc(sname,cname, chengji)asselect sname,cname,score+5 from students,courses,choiceswhere students.sid=choices.sid andcourses.cid=choices.cidwith check option4、创建分组视图，将学生的学号及他的平均成绩定义为一个视图create view s_g(sid,avg_score)as select sid,avg(score)from ch

32、oicesgroup by sid5、创建一个基于视图的视图，基于（1）中建立的视图，定义一个包括学生编号、学生所选课程数目和平均成绩的视图create view s_cs(sid,courses_shu,avg_score)asselect sid,count(cid),avg(score)from csgroup by sid6、查询所有选修课程software engineering的学生姓名select distinct snamefrom sctwhere cname=software engineering;7、插入元组（600000000,823069829，10010，59）到

33、视图CS中。若是在视图的定义中存在WITH CHECK OPTION子句对插入操作有什么影响insertinto csvalues(600000000,823069829,10010,59);服务器: 消息 550，级别 16，状态 1，行 1试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束的条件。语句已终止。8、将视图CS（包含定义WITH CHECK OPTION）中，所有课程编号为10010的课程的成绩都减去5分。这个操作数据库是否会正确，为什么？如果加上5

34、分（原来95分以上的不变）呢？update csset score=score-5where cid=10010;服务器: 消息 550，级别 16，状态 1，行 1试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束的条件。语句已终止。update choicesset score=score-5where cid=10010;（所影响的行数为 6027 行）update csset score=score+5where cid=10010;（所影响的行数为 417

35、0 行）update choicesset score=score+5where cid=10010;（所影响的行数为 6027 行）update csset score=score+5where cid=10010and score95;（所影响的行数为 3586 行）总结：因为cs中的检查条件是成绩=60，故当score-5时会出现score60的情况，所以不能执行，而对choices的操作是直接在表中操作，就不用检查视图中的条件，而在视图上增删改数据就要检查，视图定义中的条件。9、在视图CS（包含定义WITH CHECK OPTION）删除编号804529880学生的记录，会产生什么结果

36、？delete from cswhere sid=804529880;（所影响的行数为 5 行）10、取消视图SCT和视图CSdrop view SCTdrop view cs实验 2 数据库安全性控制一、 实验目的本实验的目的是通过实验加深对数据安全性的理解，并掌握SQL Server中有关用户登录认证以及管理的方法。同时能够使用SQL语句来向用户授予和收回权限，熟悉角色管理。二、 实验内容A、在SQL Server 企业管理器中，设置SQL Server 的安全认证模式操作：在企业管理器窗口总展开服务器组，用鼠标右击需要设置的SQL服务器，在弹出的菜单中选择“属性”项，则出现SQL Ser

37、ver属性对话框，选择“安全性”选项卡，在“身份验证”一栏选择“SQL Server 和Windows”单选按钮。之后，在企业管理器展开服务组后，用鼠标单击“安全性”文件夹右侧的+，用鼠标右击“登录”，在弹出的菜单中选择“新建登录”项，则出现新建登录对话框。新建登录对话框有常规，服务器角色和数据库访问三个选项卡进行设置：在常规选项卡中输入用户名为“USER1”，选择SQL Server安全验证，输入用户口令在服务器角色选项卡中，需要确定用户所属的服务器角色，在本例中采用默认在数据库访问选项卡中，需要指定次登录可以访问的数据库（School）和Public的数据库角色单击“确定”按钮，则完成了登

38、录用户的工作B、在数据库School中建立三个用户USER1、USER2、USER3，它们在数据库中的角色是PUBLIC。请按以下要求，分别以管理员身份或这三个用户的身份登录到数据库中，进行操作。（1） 授予所有用户对表COURSES的查询权限（2） 授予用户USER1对表STUDENTS插入和更新的权限，但不授予删除权限，并且授予用户USER1传播这两个权限的权利。（3） 允许用户USER2在表CHOICES中插入元组，更新SCORE列，可以查询除了sid以外的所有列（4） 用户USER1授予用户USER2 对表 STUDENTS插入和更新的权限，并且授予用户USER2传播插入和更新操作的权

39、利（5） 收回对用户USER1对表COURSES查询权限的授予（6） 由上面（2）和（4）的授权，再由用户USER2对用户USER3授予表STUDENTS插入和更新的权限，并且授予用于USER3传播插入和更新操作的权利。这个时候如果由USER3对USER1授予表STUDENTS的插入和更新的权利是否能够成功？如果由DBA取消USER1的权限，对USER2有什么影响。（7） 通过SQL Server企业管理器，创建一个数据库角色OP_of_students，它代表一个可以对STUDENTS表进行操作的操作员，对角色的权限进行设置，并将用户USER1添加到这个角色中。（8） 创建在选课表CHOICES上的视图CS_View，授权给计算机系的开计算机科学这门课程（课程号：10010）的数据库用户USER1，让他具有视图上的select权限。（9） 对视图上的score属性列的update权限授予用户USER1，让他可以修改学生的成绩，但是不能对学生的基本信息，