sql数据库原理实验指导书及答案

1 数 据 库 原 理 SQLServer 实 验 指 导 书 2 数据库系统原理实验 一、基本操作实验 实验 1：数据库的定义实验 本实验的实验目的是要求学生熟练掌握和使用 SQL、T-SQL、SQL Server Enterpriser Manager Server 创建数据库、表、索引和修改表结构，并学会使用 SQL Server Query Analyzer,接收 TSQL 语句和进行结果分析。 本实验的内容包括： ）创建数据库和查看数据库属性。 ）创建表、确定表的主码和约束条件，为主码建索引。 ）查看和修改表结构。 ）熟悉 SQL Server Enterpriser Manager 和 Query Analyzer 工具的使用方法 具体实验任务如下。 基本提作实验 )使用 Enterpriser Manager 建立图书读者数据库 2)在 Enterpriser Manager 中查看图书读者数据库的属性，并进行修改，使之符合要求。 3）通过 Enterpriser Manager，在建好了图书借阅数据库中建立图书、读者和借阅个表， 其结构为： 图书（书号，类别，出版社，作者，书名，定价，版次） ； 读者（编号，姓名，单位，性别，电话）： 借阅（书号，读者编号，借阅日期人） 要求为属性选择合适的数据类型，定义每个表的主键，Allow Null（是否允许空值）和 Default Value（缺省值）等列级数据约柬。 ）在 Enterpriser Manager 建立图书、读者和借阅个表的表级约束：每个表的 3 PrimaryKey（主键）约束；借阅表与图书表间、借阅表与读者表之间的 （外码）约柬，要求按语义先确定外码约束表达式，再通过操作予以 实现；实现借阅表的书号和读者编号的 UNIQUE（惟一性）约束；实现读者性别只能是“男” 或“女”的 check（检查）约束。 提高操作实验 建立学生_课程库操作，在查询分析器中用 SQL 命令实现。库中表结构为： Student（sno，sname，sage，ssex，sdept）： course（cno，cname，cpno,ccredit） ； sc（sno，cno，grade） 。 要求： ）建库、建表、建立表间联系。 ）选择合适的数据类型。 ）定义必要的索引、列纽约束和表级约束。 选择操作实验 将自设计的数据库应用系统中的数据库、库中的表、索引和约束用 T-SQL 表达，并通过 Enterpriser Manager 或 Query Analyzer 实现建库、建表、建立表间联系和建立必要的索 引、列级约束和表级约束的操作。 实验 2：数据库的建立和维护实验 本实验的目的是要求学生熟练掌握使用 SQL、-SQL 和 SQL Server Enterpriser Manager 向数据库输入数据、修改数据和删除数据的操作。 4 基本操作实验 ）通过 Enterpriser Manager，在图书借阅数据库的图书、读者和借阅个表中各输入 10 条记录。要求记录不仅满足数据约束要求，还要有表间关联的记录。2）图书借阅数据库 的三个基本表为图书（书号，类别，出版社，作者，书名，定价, 版次） ；读者（编号，姓 名，单位，性别，电话） ；借阅（书号，读者编号，借阅日期） 。对通过 Enterpriser Manager 实现对图书借阅数据库的图书、读者和借阅个表中数据的插入、删除和修改操作。 ）通过 Enterpriser Manager 实现对学生选课库的数据增加、数据删除和数据修改操作。 要求学生、课程和选课表中各有条以上记录。 提高操作实验 向自设计的数据库应用系统的库中的各表，输入多条实际数据，并实现数据的增、删。改操 作。 选择操作实验 将用数据修改语句和数据删除语句通过 Query Analyzer。输入给 SQL Server 数据库，并查 看、分析执行的结果，实现正确操作。 UPDATE 读者 SET 借阅册数=（SELECT COUNT（*） FROM 读者，借阅 WHERE 读者.编号=借阅.读者编号 AND 借阅日期 BETWEEN 2001-01-01 AND 2001-12-310 DELETE 借阅 WHERE 读者编号 IN (SELECT 编号 FROM 读者 WHERE 单位=计算机) 实验 3：数据库的简单查询和连接查询实验 5 本实验的目的是使学生掌握 SQL Server Query Analyzer 的使用方法，加深对 SQL 和 T-SQL 语言的查询语句的理解。熟练掌握简单表的数据查询、数据排序和数据联结广询的操 作方法。 本实验的主要内容是： ）简单查询操作。该实验包括投影、选择条件表达，数据排序，使用临时表等。 ）连接查询操作。该实验包括等值连接、自然连接、求笛卡儿积、一般连接、外连接。 内连接、左连接、右连接和自连接等。 实验方法：将查询需求用 T-SQL 语言表示；在 SQL Server Query Analyzer 的输入区中输入 T-SQL 查询语句；设置 Query Analyzer 的结果区为 Standard Execute（标准执行）或 Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结 果不正确，要进行修改，直到正确为止。 基本操作实验 （）简单查询实验 ）在学生选课库中实现其数据查询操作。 求数学系学生的学号和姓名 select sno,sname from student where sdept=ma 求选修了课程的学生学号 select sno from sc where grade is not null; 求选修 C1 课程的学生学号和成绩,结果按成绩降序排列,如成绩同按学号升序排列 6 select sno,grade from sc where cno=1 order by grade desc,sno asc; 求选修课程 C1 成绩在 8090 之间的学生学号和成绩,并将成绩乘以 0.8 输出 select sno,grade*0.85 from sc where cno=1 and grade between 80 and 90; 求数学或计算机系姓张的学生的信息 select * from student where sdept in (ma,is) and sname like 张% 求缺少了成绩的学生的学号和课程号 select sno,cno from sc where grade is null; ）在图书借阅库中实现其查询操作。 将计算机类的书存入永久的计算机图书表 insert into yongjiu select * from tushu where leibie=小说; 7 将借阅日期在 99 年以前的借阅记录存入临时的超期借阅表 （）连接查询实验 ） 在学生选课库中实现其数据连接查询操作。 查询每个学生的情况以及他(她)所选修的课程 select student.*,o from student,sc where student.sno=sc.sno; 求学生的学号、姓名、选修的课程及成绩 select student.sno,sname,cno,grade from student,sc where student.sno=sc.sno; 求选修课程 C1 且成绩在 90 分以上的学生学号、姓名及成绩 select student.sno,sname,grade from student,sc where student.sno=sc.sno and grade90 and o=1; 查询每一门课的间接先行课（即先行课的先行课） select o,second.cpno from course first,course second where first.cpno=o; 2）在图书借阅库中实现其连接查询操作。 查询借书者的编号、姓名、单位、所借书号、书名和借阅日期 select duzhe.bianhao,xingming,danwei,shuhao,riqi 8 from duzhe,jieyue where duzhe.bianhao=jieyue.bianhao 提高操作实验 ）建立职工部门库和职工、部门表，并向表中输入数据 职工表 create table 职工表 ( 职工号 char(20) primary key, 姓名 char(20) unique, 性别 char(20), 年龄 smallint, 部门 char(20) ); 部门表 部门号 部门名称 电话 11 生产科 566 12 计划科 578 13 一车间 467 职工号 姓名 性别 年龄 所在部门 1010 李勇 男 20 11 1011 刘晨 女 19 14 1012 王敏 女 22 12 1014 张立 男 21 13 9 14 科研所 create table 部门表 ( 部门号 char(20) primary key, 部门名称 char(20) unique, 电话 char(20) ); 2 ）用 T-SQL 语句在职工部门库中实现其数据内连接和各种外查询操作。 内连接 Select 姓名，部门名称，电话 form 职工表，部门表 where 职工表.所在部门=部门表.部门号 select 姓名,部门名称,电话 from 职工表 ,部门表 where 职工表 .部门= 部门表. 部门号 改为左外连接和右外连接 select 姓名,部门名称,电话 from 职工表 left join 部门表on (职工表.部门=部门表.部门号); select 姓名,部门名称,电话 from 职工表 right join 部门表on (职工表.部门=部门表.部门号); 10 选择操作实验 (1) 设职工社团库有三个基本 ）建立职工社团数据库和基本表，向库中输入一定的记录。 职工（职工号，姓名，负责人，活动地点） create table 职工 ( 职工号 char(20) primary key, 姓名 char(20) unique, 负责人 char(20), 活动地点 char(20) ); 社会团体（编号，名称，负责人，活动地点） create table 职工社团 ( 编号 char(20) primary key, 名称 char(20) unique, 负责人 char(20), 活动地点 char(20) ); 参加（职工号，编号，参加日期） create table 参加 (职工号 char(20), 编号 char (20), 活动地点 char(20), 11 primary key (职工号,编号), foreign key (职工号) references 职工(职工号), foreign key (编号) references 社会团体(编号) ); ）按以下要求用 T-SQL 语句表示。 1 定义职工表、社会团体表和参加表，并说明其主码和参照关系。 2 参加唱歌队或篮球队的职工事情和姓名。 3 查找没有参加任何社会团的职工情况。 4 查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。 ）通过 SQL Server Query Analyzer 实现查询操作。 （）设工程零件库有四个表 ）建立工程零件数据库和基本表，向库中输入一定的记录。 供应商（供应商代码，姓名，所在城市，联系电话） ； 工程（工程代码，工程名，负责人，预算） ； 零件（零件代码，零件名，规格，产地，颜色） ； 供应零件（供应商代码，工程代码，零件代码，数量） 。 2）以下要求用-SQL 语句表示。 （1）找出天津市供应商的姓名和电话。 select sname,电话 from s where city=天津 （2）查找使用预算在 50000100000 元之间的工程的信息，并将结果按预算降序排列。 12 select * from J where 预算 between 50000 and 100000 （3）找出使用供应商 S1 所供零件名称及其数量。 select pname,qty from p,spj where p.pno=spj.pno and sno=s1 （4）找出工程项目 J2 使用的各种零件名称及其数量。 select pname,qty from p,spj where p.pno=spj.pno and jno=j2 ）通过 SQL Server Query Analyzer 实现查询操作。 实验 4：数据库的嵌套查询实验 本实验的目的是使学生进一步掌握 SQL Server Query Analyzer 的使用方法，加深 SQL 和 T-SQL 语言的嵌套查询语句的理解。 本实验的主要内容是：在 SQL Server Query Analyzer 中使用 IN、比较符、ANY 或 ALL 和 EXISTS 操作符进行嵌套查询操作。 实验方法：将查询需求用 T-SQL 语言表示；在 SQL Server Query Analyzer 的输入区中 输入 T-SQL 查询语句；设置 Query Analyzer 的结果区为 Standard Execute（标准 执行）或 Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果； 如果结果不正确，要进行修改，直到正确为止。 13 基本操作实验 求进修了高等数学的学生学号和姓名。 select student.sno,sname from student,sc where student.sno=sc.sno and cno in (select cno from course where cname=数学 ); 求 C1 课程的成绩高于张三的学生学号和成绩。 select student.sno,grade from student,sc where student.sno=sc.sno and cno=1 and grade (select grade from sc where sno in (select sno from student where sname=张立) ); 求其他系中比计算机某学生年龄小的学生（即求年龄小于计算机系年龄最大者的学生） select * from student where sdeptcs and sagecs and sage (select min(sage) from student where sdept=cs ); 求进修了 C2 课程的学生姓名。 select sname from student,sc where student.sno=sc.sno and cno=2; 求职没有选修 C2 课程的学生姓名。 select sname from student,sc where not exists (select * from sc where sno=student.sno and cno=2 15 ); 查询送修了全部课程的学生姓名。 select sname from student where not exists (select * from course where not exists( select * from sc where sno=student.sno and cno=o); 求职至少选修了学生为“S2”的学生所选修的全部课程的学生学号的姓名 select distinct sno from sc scx where not exists ( select * From sc scy Where scy.sno=200215122and not exists (select * From sc scz Where scz.sno=scx.sno and o=o) ); 用 T-SQL 语句表示，在学生选课库中实现其数据嵌套查询操作。 提高操作实验 16 按以下要求用 T-QL 语句表示并通过 SQL Server Query Analyzer 实现查询操作。 找出上海厂商供应的所有零件号码。 select pno,jno,qty from s,spj where s.sno=spj.sno and spj.sno in (select sno from s where city=上海 ) 找出使用上海产的零件的工程名称。 select jno,qty,pno from s,spj where s.sno=spj.sno and spj.sno in (select sno from s where city=上海 ) 找出没有使用天津产零件的工程号码。 select pno (没调试出来的) from spj where not exists (select * from s,spj where s.sno=spj.sno and s.city=天津 17 ) 选择操作实验 将自设计的数据库应用项目中的数据查询操作分类，用 T-SQL 语句表示其中的简单、连 接和嵌套查询，并通过 SQL Server Query Analyzer 实现其查询操作。 实验 5：数据库的组合查询和统计查询实验 本实验的目的是使学生熟练掌握 SQL Server Query Analyzer 的使用方法，加深对 SQL 和-SQL 语言查询语句的理解。熟练掌握数据查询中分组、统计、计算和组合的操作方法。 本实验的主要内容是： 1)分组查询实验。该实验包括分组条件表达、选择组条件表达的方法。 2)使用函数查询的实验。该实验包括统计函数和分组统计函数的使用方法。 3)组合查询实验。 4)计算和分组计算查询的实验。 实验方法：将查询需求用 T-SQL 语言表示；在 SQL Server Query Analyzer 的输入区中 输入 T-SQL 查询语句；设置 Query Analyzer 的结果区为 Standard Execute（标准 执行）或 Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果； 如果结果不正确，要进行修改，直到正确为止。 基本操作实验。 1) 查找这样的图书类别:要求类别最高的图书定价不低于全部按类别分组的图书平均定价 18 的确倍. 2) 求机械工业出版社出版的各类图书的平均定价,用 Group By 表示. 3) 列出计算机类图书的书号,名称及价格,最后求出册数和总价格. 4) 列出计算机类图书的书号,名称及价格,并求出各出版社这类书的总价格,最后求出全部 册数和总价格. 5) 查询计算机类和机械工业出版社出版的图书. 在图书借阅库中实现其查询操作。 提高操作实验 1) 求学生的总人数. 2) 求选修课程的学生人数. 3) 求课程和选修课程的人数. 4) 求选修课的学生学号 转换为 T-SQL 语句，在学生选课库中实现其数据查询操作。 选择操作实验。 ）按以下要求用-SQL 语句表示，并通过 SQL Server Query Analyzer 实现查询操作。 在工程零件库中 1. 把全部红色零件的颜色改为蓝色 2. 将由供应商 S5 供给工程代码为 J4 的零件 P6 改为由 S3 供应由 S3 供应,并作其他必 要修改. ）将自设计的数据库应用项目中的分组、统计、计算和组合查询，用 T-SQL 语句表示，并 通过 SQL Server Query Analyzer 实现其查询操作。 19 实验 6：数据库的视图和图表的定义及使用实验 本实验的目的是使学生掌握 SQLServer 中的视图创建向导和图表创建向导的使用方法， 加深对视图和 SQL Server 图表作用的理解。 本实验的主要内容是： ）创建、查看、修改和删除视图。 ）创建、编辑和删除数据库图表。 用视图创建向导创建视图，用工具和向导创建数据库图表 基本操作实验 ）在 SQL Server Enterpriser Manager 中调出 Create View Wizard 描述的视图定义，创 建借阅计算机图书视图。 Create View 借阅计算机图书 As select 图书.*，借阅.* From 图书，借阅 Where 图书编号=借阅.书号 AND 图书.类别=计算机 提高操作实验 在 SQL Server Enterpriser Manager 中，用视图创建向导创建这个视图。 建立计算机系学生的视图 由学生、课程和选修课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、 20 姓名、课程名和成绩 将学生的学号、总成绩、平均成绩定义成一个视图 选择操作实验 针对学生数据库中三个数据表：Student,course,sc 完成下面任务： 1、 创建视图 pride1,按照学号的升序，将前 30%的同学的学号、平均成绩显示出来。 2、 创建视图 pride2，将平均成绩最高的前 3 名同学的学号、平均成绩显示出来。 3、 创建视图 pride3，将同时选修了数据库和信息系统的学生姓名显示出来。 4、 创建视图 pride4，将课程名和课程平均成绩显示出来。 5、 创建视图 pride5，将课程成绩大于 80 分的学生的姓名、课程名以及成绩显示出来，要 求修改试图 pride5，必须满足限制条件：成绩大于 80 分。 6、删除视图 pride5 和 pride2。 7、从视图 pridel 中查询学号最小的学生的平均成绩。 8、从视图 pride4 中查询课程平均成绩在 7080 分之间的课程名。 9创建视图 pride6，将所有女生的记录显示出来。 10向视图 pride6，插入一条记录： (95005，乔海石，女，20，IS) 11、删除视图 pride6 中信息系的所有记录。 12、创建视图 pride7，将所有同时选修了信息系统、数学的女生的成绩列出来。要求列表 中的列为姓名、课程名称，成绩。 21 实验 7：数据完整性和数据安全性实验 本实验的目的是通过实验使学生加深对数据安全性和完整性的理解，并掌握 SQL Server 中有关用户、角色及操作权限的管理方法，学会创建和使用规则、缺省和触发器。 本实验的实验内容为： ）数据库的安全性实验。在 SQL Server Enterpriser Manager 中，设置宋庆龄 SQL Server 的安全认证模式，实现对 SQLerver 的用户和角色管理，设置和管理数据操作权限。 2)数据库的完整性实验。使用 T-SQL 设计规则、缺省、约束和触发器，通过 SQL Server Enterpriser Manager 定义它们。 基本操作实验 ）在 SQL Server Enterpriser Manager 中为所属的 SQL 服务器设置安 全认证模式。 2)在 SQL Server Enterpriser Manager 中为自己建立一个服务器用户、