《SQL实验的题目和答案全解.doc》由会员分享，可在线阅读，更多相关《SQL实验的题目和答案全解.doc（21页珍藏版）》请在三一文库上搜索。
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 cname=c+)intersectselec
22、t 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=choices.cid and ame=java)select
23、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 对每个课程，求学生的选课人数和学生的平均成绩，并把结果存入数据库。使用INSERT INTO子查询的方法实现3 在STUDENTS表中使用SQL语句将姓名为
24、LiMing的学生的年级改为20024 在TEACHERS表中使用SQL与将所有教师的工资加500元5 将姓名为zapyv的学生的课程C的成绩加上5分6 在STUDENTS表中使用SQL语句删除姓名为LiMing的学生信息7 删除所有选修课程java的选课记录8 对COURSES 表做删去时间48的元组的操作，讨论其操作视图：1、 要求创建一个行列子集视图CS，给出选课成绩合格的序号、学生编号、所选课程号、及课程成绩。2、 创建基于多个基表的视图SCT，这个视图由学生姓名和他所选修的课程名及讲授该课程的教师姓名构成3、 创建带表达式的视图SCC，由学生姓名及所选课程名和所有课程成绩都比原来多5
25、分这几个属性组成4、 创建分组视图，将学生的学号及他的平均成绩定义为一个视图5、 创建一个基于视图的视图，基于（1）中建立的视图，定义一个包括学生编号、学生所选课程数目和平均成绩的视图6、 查询所有选修课程software engineering的学生姓名7、 插入元组（600000000,823069829，10010，59）到视图CS中。若是在视图的定义中存在WITH CHECK OPTION子句对插入操作有什么影响8、 将视图CS（包含定义WITH CHECK OPTION）中，所有课程编号为10010的课程的成绩都减去5分。这个操作数据库是否会正确，为什么？如果加上5分（原来95分以上
26、的不变）呢？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,1992);2对每个课程，求学生的选课人数和学生的平均成绩，并把结果存入数据库。使用INSERT INTO子查询的方法实现create
27、 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;4在TEACHERS表中使用SQL与将所有教师的工资加500元update teachersset salary=salary+500
28、;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+5where sid in(select sidfrom studentswhere sname=zapyv)and cid in(se
29、lect 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的元组的操作，讨论其操作delete from courseswhere hour=60with check option2、创建基于多个基表的视图SCT，这个视图由
30、学生姓名和他所选修的课程名及讲授该课程的教师姓名构成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、创建带表达式的视图SCC，由学生姓名及所选课程名和所有课程成绩都比原来多5分这几个属性组成create view scc(sname,cnam
31、e, 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 choicesgroup by sid5、创建一个基于视图的视图，基于（1）中建立的视图，定义一个包括学生编号、学生所选课程数目和平均成绩
32、的视图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）到视图CS中。若是在视图的定义中存在WITH CHECK OPTION子句对插入操作有什么影响insertinto csvalues(6
33、00000000,823069829,10010,59);服务器: 消息 550，级别 16，状态 1，行 1试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束的条件。语句已终止。8、将视图CS（包含定义WITH CHECK OPTION）中，所有课程编号为10010的课程的成绩都减去5分。这个操作数据库是否会正确，为什么？如果加上5分（原来95分以上的不变）呢？update csset score=score-5where cid=10010;服务器: 消息 55
34、0，级别 16，状态 1，行 1试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束的条件。语句已终止。update choicesset score=score-5where cid=10010;（所影响的行数为 6027 行）update csset score=score+5where cid=10010;（所影响的行数为 4170 行）update choicesset score=score+5where cid=10010;（所影响的行数为 6027 行）
35、update csset score=score+5where cid=10010and score95;（所影响的行数为 3586 行）总结：因为cs中的检查条件是成绩=60，故当score-5时会出现score60的情况，所以不能执行，而对choices的操作是直接在表中操作，就不用检查视图中的条件，而在视图上增删改数据就要检查，视图定义中的条件。9、在视图CS（包含定义WITH CHECK OPTION）删除编号804529880学生的记录，会产生什么结果？delete from cswhere sid=804529880;（所影响的行数为 5 行）10、取消视图SCT和视图CSdrop
36、 view SCTdrop view cs实验 2 数据库安全性控制一、 实验目的本实验的目的是通过实验加深对数据安全性的理解，并掌握SQL Server中有关用户登录认证以及管理的方法。同时能够使用SQL语句来向用户授予和收回权限，熟悉角色管理。二、 实验内容A、在SQL Server 企业管理器中，设置SQL Server 的安全认证模式操作：在企业管理器窗口总展开服务器组，用鼠标右击需要设置的SQL服务器，在弹出的菜单中选择“属性”项，则出现SQL Server属性对话框，选择“安全性”选项卡，在“身份验证”一栏选择“SQL Server 和Windows”单选按钮。之后，在企业管理器展
37、开服务组后，用鼠标单击“安全性”文件夹右侧的+，用鼠标右击“登录”，在弹出的菜单中选择“新建登录”项，则出现新建登录对话框。新建登录对话框有常规，服务器角色和数据库访问三个选项卡进行设置：在常规选项卡中输入用户名为“USER1”，选择SQL Server安全验证，输入用户口令在服务器角色选项卡中，需要确定用户所属的服务器角色，在本例中采用默认在数据库访问选项卡中，需要指定次登录可以访问的数据库（School）和Public的数据库角色单击“确定”按钮，则完成了登录用户的工作B、在数据库School中建立三个用户USER1、USER2、USER3，它们在数据库中的角色是PUBLIC。请按以下要求
38、，分别以管理员身份或这三个用户的身份登录到数据库中，进行操作。（1） 授予所有用户对表COURSES的查询权限（2） 授予用户USER1对表STUDENTS插入和更新的权限，但不授予删除权限，并且授予用户USER1传播这两个权限的权利。（3） 允许用户USER2在表CHOICES中插入元组，更新SCORE列，可以查询除了sid以外的所有列（4） 用户USER1授予用户USER2 对表 STUDENTS插入和更新的权限，并且授予用户USER2传播插入和更新操作的权利（5） 收回对用户USER1对表COURSES查询权限的授予（6） 由上面（2）和（4）的授权，再由用户USER2对用户USER3授
39、予表STUDENTS插入和更新的权限，并且授予用于USER3传播插入和更新操作的权利。这个时候如果由USER3对USER1授予表STUDENTS的插入和更新的权利是否能够成功？如果由DBA取消USER1的权限，对USER2有什么影响。（7） 通过SQL Server企业管理器，创建一个数据库角色OP_of_students，它代表一个可以对STUDENTS表进行操作的操作员，对角色的权限进行设置，并将用户USER1添加到这个角色中。（8） 创建在选课表CHOICES上的视图CS_View，授权给计算机系的开计算机科学这门课程（课程号：10010）的数据库用户USER1，让他具有视图上的sele
40、ct权限。（9） 对视图上的score属性列的update权限授予用户USER1，让他可以修改学生的成绩，但是不能对学生的基本信息，如学号、选课号进行修改。实验3 数据库完整性控制一、实验目的本实验的目的是学习建立外键，以及利用FOREIGNREFERENVES子句以及各种约束保证参照完整性；学习用户自定义约束，并实践用户自定义完整性；创建和使用触发器。二、实验内容(打为选做题)(1)建立表Stu_Union(sno CHAR(5)非空唯一,sname CHAR(8),ssex CHAR(1),sage INT, sdept CHAR(20), CONSTRAINT PK_Stu_UnionP
41、RIMARY KEY(sno)；建立表Course(cno CHAR(4)非空唯一,cname CHAR(50) 非空, cpoint INT, CONSTRAINT PKPRIMARY KEY(cno)，并在Stu_Union中插入数据。(2) 建立表SC，令sno和cno分别为参照表Stu_Union和Course的外键，设定为级联删除，并令（sno，cno）为其主键，属性grade INT类型。在不违反参照完整性的前提下，插入数据。 (3) 演示违反参照完整性的插入数据。(4) 在Stu_Union中删除数据，演示级联删除。(5) 在Course中删除数据，演示级联删除。(6) 为了演示
42、多重级联删除，建立Stu_Card，令stu_id为参照Students表的外键, 令card_id 为其主键,属性restored_money为decimal（10，2）类型, 并插入数据。(7) 为了演示多重级联删除，建立ICBC_Card，令stu_card_id为参照Stu_Card表的外键, 令 bank_id 为其主键,属性remained_money为decimal（10，2）类型, 并插入数据。(8) 通过删除Students表中的一条记录,演示三个表的多重级联删除用户。（先修改Choices）(9) 创建worker表，并自定义2个约束U1以及U2，其中U1规定Name字段唯
43、一，U2规 定sage（级别）字段的上限是28。(10) 在worker表中插入一条合法记录。(11) 演示插入违反U2约束的例子，U2规定元组的sage属性的值必须=28。(12) 去除U2约束。(13) 重新插入（3）中想要插入的数据，由于去除了U2约束，所以插入成功。(14) 例子：为worker表建立触发器T1，当插入或是更新表中数据时，保证所操作的记录的sage值大于0。 use schoolgocreate trigger T1 on workerfor insert , updateas if (select sage from inserted)1beginprint Sage must be a integer more than zero! Tansaction failRol