SQL数据库原理实验指导书及答案.docx
- 文档编号:30468950
- 上传时间:2023-08-15
- 格式:DOCX
- 页数:21
- 大小:23.01KB
SQL数据库原理实验指导书及答案.docx
《SQL数据库原理实验指导书及答案.docx》由会员分享,可在线阅读,更多相关《SQL数据库原理实验指导书及答案.docx(21页珍藏版)》请在冰豆网上搜索。
SQL数据库原理实验指导书及答案
数据库原理
SQLServer
实
验
指
导
书
数据库系统原理实验
一、基本操作实验
实验1:
数据库的定义实验
本实验的实验目的是要求学生熟练掌握和使用SQL、T-SQL、SQLServerEnterpriserManagerServer创建数据库、表、索引和修改表结构,并学会使用SQLServerQueryAnalyzer,接收T-SQL语句和进行结果分析。
本实验的内容包括:
l)创建数据库和查看数据库属性。
2)创建表、确定表的主码和约束条件,为主码建索引。
3)查看和修改表结构。
4)熟悉SQLServerEnterpriserManager和QueryAnalyzer工具的使用方法
具体实验任务如下。
1.基本提作实验
1)使用EnterpriserManager建立图书读者数据库
2)在EnterpriserManager中查看图书读者数据库的属性,并进行修改,使之符合要求。
3)通过EnterpriserManager,在建好了图书借阅数据库中建立图书、读者和借阅3个表,
其结构为:
图书(书号,类别,出版社,作者,书名,定价,版次);
读者(编号,姓名,单位,性别,电话):
借阅(书号,读者编号,借阅日期人)
要求为属性选择合适的数据类型,定义每个表的主键,AllowNull(是否允许空值)和
DefaultValue(缺省值)等列级数据约柬。
4)在EnterpriserManager建立图书、读者和借阅3个表的表级约束:
每个表的PrimaryKey(主键)约束;借阅表与图书表间、借阅表与读者表之间的FOREIGNKEY(外码)约柬,要求按语义先确定外码约束表达式,再通过操作予以实现;实现借阅表的书号和读者编号的UNIQUE(惟一性)约束;实现读者性别只能是“男”或“女”的check(检查)约束。
2.提高操作实验
建立学生_课程库操作,在查询分析器中用SQL命令实现。
库中表结构为:
Student(sno,sname,sage,ssex,sdept):
course(cno,cname,cpno,ccredit);
sc(sno,cno,grade)。
要求:
l)建库、建表、建立表间联系。
2)选择合适的数据类型。
3)定义必要的索引、列纽约束和表级约束。
3选择操作实验
将自设计的数据库应用系统中的数据库、库中的表、索引和约束用T-SQL表达,并通过EnterpriserManager或QueryAnalyzer实现建库、建表、建立表间联系和建立必要的索引、列级约束和表级约束的操作。
实验2:
数据库的建立和维护实验
本实验的目的是要求学生熟练掌握使用SQL、T-SQL和SQLServerEnterpriserManager向数据库输入数据、修改数据和删除数据的操作。
1.基本操作实验
1)通过EnterpriserManager,在图书借阅数据库的图书、读者和借阅3个表中各输入10条记录。
要求记录不仅满足数据约束要求,还要有表间关联的记录。
2)图书借阅数据库的三个基本表为图书(书号,类别,出版社,作者,书名,定价,版次);读者(编号,姓名,单位,性别,电话);借阅(书号,读者编号,借阅日期)。
对通过EnterpriserManager实现对图书借阅数据库的图书、读者和借阅3个表中数据的插入、删除和修改操作。
3)通过EnterpriserManager实现对学生选课库的数据增加、数据删除和数据修改操作。
要求学生、课程和选课表中各有10条以上记录。
2.提高操作实验
向自设计的数据库应用系统的库中的各表,输入多条实际数据,并实现数据的增、删。
改操作。
3选择操作实验
将用数据修改语句和数据删除语句通过QueryAnalyzer。
输入给SQLServer数据库,并查看、分析执行的结果,实现正确操作。
①UPDATE读者SET借阅册数=(SELECTCOUNT(*)FROM读者,借阅
WHERE读者.编号=借阅.读者编号AND借阅日期
BETWEEN‘2001-01-01’AND‘2001-12-31’0
②DELETE借阅WHERE读者编号IN(SELECT编号FROM读者WHERE单位=’计算机’)
实验3:
数据库的简单查询和连接查询实验
本实验的目的是使学生掌握SQLServerQueryAnalyzer的使用方法,加深对SQL和T-SQL语言的查询语句的理解。
熟练掌握简单表的数据查询、数据排序和数据联结广询的操作方法。
本实验的主要内容是:
1)简单查询操作。
该实验包括投影、选择条件表达,数据排序,使用临时表等。
2)连接查询操作。
该实验包括等值连接、自然连接、求笛卡儿积、一般连接、外连接。
内连接、左连接、右连接和自连接等。
实验方法:
将查询需求用T-SQL语言表示;在SQLServerQueryAnalyzer的输入区中输入T-SQL查询语句;设置QueryAnalyzer的结果区为StandardExecute(标准执行)或ExecutetoGrid(网格执行)方式;发布执行命令,并在结果区中查看查询结果;如果结果不正确,要进行修改,直到正确为止。
1.基本操作实验
(1)简单查询实验
1)在学生选课库中实现其数据查询操作。
1求数学系学生的学号和姓名
selectsno,sname
fromstudent
wheresdept='ma'
2求选修了课程的学生学号
selectsno
fromsc
wheregradeisnotnull;
3求选修C1课程的学生学号和成绩,结果按成绩降序排列,如成绩同按学号升序排列
selectsno,grade
fromsc
wherecno='1'
orderbygradedesc,snoasc;
4求选修课程C1成绩在80~90之间的学生学号和成绩,并将成绩乘以0.8输出
selectsno,grade*0.85
fromsc
wherecno='1'andgradebetween80and90;
5求数学或计算机系姓张的学生的信息
select*
fromstudent
wheresdeptin('ma','is')andsnamelike'张%'
6求缺少了成绩的学生的学号和课程号
selectsno,cno
fromsc
wheregradeisnull;
2)在图书借阅库中实现其查询操作。
1将计算机类的书存入永久的计算机图书表
insert
intoyongjiu
select*
fromtushu
whereleibie='小说';
②将借阅日期在99年以前的借阅记录存入\临时的超期借阅表
(2)连接查询实验
1)在学生选课库中实现其数据连接查询操作。
1查询每个学生的情况以及他(她)所选修的课程
selectstudent.*,o
fromstudent,sc
wherestudent.sno=sc.sno;
2求学生的学号、姓名、选修的课程及成绩
selectstudent.sno,sname,cno,grade
fromstudent,sc
wherestudent.sno=sc.sno;
3求选修课程C1且成绩在90分以上的学生学号、姓名及成绩
selectstudent.sno,sname,grade
fromstudent,sc
wherestudent.sno=sc.snoandgrade>90ando='1';
4查询每一门课的间接先行课(即先行课的先行课)
selecto,second.cpno
fromcoursefirst,coursesecond
wherefirst.cpno=o;
2)在图书借阅库中实现其连接查询操作。
①查询借书者的编号、姓名、单位、所借书号、书名和借阅日期
selectduzhe.bianhao,xingming,danwei,shuhao,riqi
fromduzhe,jieyue
whereduzhe.bianhao=jieyue.bianhao
2.提高操作实验
l)建立职工部门库和职工、部门表,并向表中输入数据
职工表
职工号
姓名
性别
年龄
所在部门
1010
李勇
男
20
11
1011
刘晨
女
19
14
1012
王敏
女
22
12
1014
张立
男
21
13
createtable职工表
(
职工号char(20)primarykey,
姓名char(20)unique,
性别char(20),
年龄smallint,
部门char(20)
);
部门表
部门号
部门名称
电话
11
生产科
566
12
计划科
578
13
一车间
467
14
科研所
createtable部门表
(
部门号char(20)primarykey,
部门名称char(20)unique,
电话char(20)
);
2)用T-SQL语句在职工部门库中实现其数据内连接和各种外查询操作。
2内连接Select姓名,部门名称,电话form职工表,部门表
where职工表.所在部门=部门表.部门号
select姓名,部门名称,电话
from职工表,部门表
where职工表.部门=部门表.部门号
3改为左外连接和右外连接
select姓名,部门名称,电话
from职工表leftjoin部门表on
(职工表.部门=部门表.部门号);
select姓名,部门名称,电话
from职工表rightjoin部门表on
(职工表.部门=部门表.部门号);
3.选择操作实验
(1)设职工—社团库有三个基本
1)建立职工.社团数据库和基本表,向库中输入一定的记录。
职工(职工号,姓名,负责人,活动地点)
createtable职工
(
职工号char(20)primarykey,
姓名char(20)unique,
负责人char(20),
活动地点char(20)
);
社会团体(编号,名称,负责人,活动地点)
createtable职工社团
(
编号char(20)primarykey,
名称char(20)unique,
负责人char(20),
活动地点char(20)
);
参加(职工号,编号,参加日期)
createtable参加
(职工号char(20),
编号char(20),
活动地点char(20),
primarykey(职工号,编号),
foreignkey(职工号)references职工(职工号),
foreignkey(编号)references社会团体(编号)
);
2)按以下要求用T-SQL语句表示。
1定义职工表、社会团体表和参加表,并说明其主码和参照关系。
2参加唱歌队或篮球队的职工事情和姓名。
3查找没有参加任何社会团的职工情况。
4查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。
3)通过SQLServerQueryAnalyzer实现查询操作。
(2)设工程—零件库有四个表
l)建立工程_零件数据库和基本表,向库中输入一定的记录。
供应商(供应商代码,姓名,所在城市,联系电话);
工程(工程代码,工程名,负责人,预算);
零件(零件代码,零件名,规格,产地,颜色);
供应零件(供应商代码,工程代码,零件代码,数量)。
2)以下要求用T-SQL语句表示。
(1)找出天津市供应商的姓名和电话。
selectsname,电话
froms
wherecity='天津'
(2)查找使用预算在50000—100000元之间的工程的信息,并将结果按预算降序排列。
select*
fromJ
where预算between50000and100000
(3)找出使用供应商S1所供零件名称及其数量。
selectpname,qty
fromp,spj
wherep.pno=spj.pnoandsno='s1'
(4)找出工程项目J2使用的各种零件名称及其数量。
selectpname,qty
fromp,spj
wherep.pno=spj.pnoandjno='j2'
3)通过SQLServerQueryAnalyzer实现查询操作。
实验4:
数据库的嵌套查询实验
本实验的目的是使学生进一步掌握SQLServerQueryAnalyzer的使用方法,加深SQL和T-SQL语言的嵌套查询语句的理解。
本实验的主要内容是:
在SQLServerQueryAnalyzer中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。
实验方法:
将查询需求用T-SQL语言表示;在SQLServerQueryAnalyzer的输入区中输入T-SQL查询语句;设置QueryAnalyzer的结果区为StandardExecute(标准
执行)或ExecutetoGrid(网格执行)方式;发布执行命令,并在结果区中查看查询结果;
如果结果不正确,要进行修改,直到正确为止。
1.基本操作实验
①求进修了高等数学的学生学号和姓名。
selectstudent.sno,sname
fromstudent,sc
wherestudent.sno=sc.snoandcnoin
(selectcno
fromcourse
wherecname='数学'
);
2求C1课程的成绩高于张三的学生学号和成绩。
selectstudent.sno,grade
fromstudent,sc
wherestudent.sno=sc.snoandcno='1'andgrade>
(selectgrade
fromsc
wheresnoin
(selectsno
fromstudent
wheresname='张立')
);
3求其他系中比计算机某学生年龄小的学生(即求年龄小于计算机系年龄最大者的学生)
select*
fromstudent
wheresdept<>'cs'andsage<
(selectmax(sage)
fromstudent
wheresdept='cs'
);
4求其他系中比计算机系学生年龄都小的学生。
select*
fromstudent
wheresdept<>'cs'andsage<
(selectmin(sage)
fromstudent
wheresdept='cs'
);
5求进修了C2课程的学生姓名。
selectsname
fromstudent,sc
wherestudent.sno=sc.snoandcno='2';
6求职没有选修C2课程的学生姓名。
selectsname
fromstudent,sc
wherenotexists
(select*
fromsc
wheresno=student.snoandcno='2'
);
7查询送修了全部课程的学生姓名。
selectsname
fromstudent
wherenotexists
(select*
fromcourse
wherenotexists(select*
fromsc
wheresno=student.snoandcno=o));
8求职至少选修了学生为“S2”的学生所选修的全部课程的学生学号的姓名
selectdistinctsno
fromscscx
wherenotexists
(select*
Fromscscy
Wherescy.sno=’200215122’andnotexists
(select*
Fromscscz
Wherescz.sno=scx.snoando=o)
);
用T-SQL语句表示,在学生选课库中实现其数据嵌套查询操作。
2提高操作实验
按以下要求用T-SQL语句表示并通过SQLServerQueryAnalyzer实现查询操作。
1找出上海厂商供应的所有零件号码。
selectpno,jno,qty
froms,spj
wheres.sno=spj.snoandspj.snoin
(selectsno
froms
wherecity='上海'
)
2找出使用上海产的零件的工程名称。
selectjno,qty,pno
froms,spj
wheres.sno=spj.snoandspj.snoin
(selectsno
froms
wherecity='上海'
)
3找出没有使用天津产零件的工程号码。
selectpno(没调试出来的)
fromspj
wherenotexists
(select*
froms,spj
wheres.sno=spj.snoands.city='天津'
)
3选择操作实验
将自设计的数据库应用项目中的数据查询操作分类,用T-SQL语句表示其中的简单、连接和嵌套查询,并通过SQLServerQueryAnalyzer实现其查询操作。
实验5:
数据库的组合查询和统计查询实验
本实验的目的是使学生熟练掌握SQLServerQueryAnalyzer的使用方法,加深对SQL和T-SQL语言查询语句的理解。
熟练掌握数据查询中分组、统计、计算和组合的操作方法。
本实验的主要内容是:
1)分组查询实验。
该实验包括分组条件表达、选择组条件表达的方法。
2)使用函数查询的实验。
该实验包括统计函数和分组统计函数的使用方法。
3)组合查询实验。
4)计算和分组计算查询的实验。
实验方法:
将查询需求用T-SQL语言表示;在SQLServerQueryAnalyzer的输入区中输入T-SQL查询语句;设置QueryAnalyzer的结果区为StandardExecute(标准
执行)或ExecutetoGrid(网格执行)方式;发布执行命令,并在结果区中查看查询结果;
如果结果不正确,要进行修改,直到正确为止。
1.基本操作实验。
1)查找这样的图书类别:
要求类别最高的图书定价不低于全部按类别分组的图书平均定价的确倍.
2)求机械工业出版社出版的各类图书的平均定价,用GroupBy表示.
3)列出计算机类图书的书号,名称及价格,最后求出册数和总价格.
4)列出计算机类图书的书号,名称及价格,并求出各出版社这类书的总价格,最后求出全部册数和总价格.
5)查询计算机类和机械工业出版社出版的图书.
在图书借阅库中实现其查询操作。
2.提高操作实验
1)求学生的总人数.
2)求选修课程的学生人数.
3)求课程和选修课程的人数.
4)求选修课的学生学号
转换为T-SQL语句,在学生选课库中实现其数据查询操作。
3选择操作实验。
l)按以下要求用T-SQL语句表示,并通过SQLServerQueryAnalyzer实现查询操作。
在工程—零件库中
1.把全部红色零件的颜色改为蓝色
2.将由供应商S5供给工程代码为J4的零件P6改为由S3供应由S3供应,并作其他必要修改.
2)将自设计的数据库应用项目中的分组、统计、计算和组合查询,用T-SQL语句表示,并通过SQLServerQueryAnalyzer实现其查询操作。
实验6:
数据库的视图和图表的定义及使用实验
本实验的目的是使学生掌握SQLServer中的视图创建向导和图表创建向导的使用方法,加深对视图和SQLServer图表作用的理解。
本实验的主要内容是:
l)创建、查看、修改和删除视图。
2)创建、编辑和删除数据库图表。
用视图创建向导创建视图,用工具和向导创建数据库图表
1.基本操作实验
l)在SQLServerEnterpriserManager中调出CreateViewWizard描述的视图定义,创建借阅.计算机图书视图。
CreateView借阅.计算机图书
Asselect图书.*,借阅.*
From图书,借阅
Where图书编号=借阅.书号AND图书.类别=’计算机’
2.提高操作实验
在SQLServerEnterpriserManager中,用视图创建向导创建这3个视图。
1建立计算机系学生的视图
2由学生、课程和选修课三个表,定义一个计算机系的学生成绩视图,其属性包括学号、姓名、课程名和成绩
③将学生的学号、总成绩、平均成绩定义成一个视图
3.选择操作实验
针对学生数据库中三个数据表:
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中查询课程平均成绩在70—80分之间的课程名。
9.创建视图pride6,将所有女生的记录显示出来。
10.向视图pride6,插入一条记录:
(95005,乔海石,女,20,IS)
11、删除视图pride6中信息系的所有记录。
12、创建视图pride7,将所有同时选修了信息系统、数学的女生的成绩列出来。
要求列表中的列为姓名、课程名称,成绩。
实验7:
数据完整性和数据安全性实验
本实验的目的是通过实验使学生加深对数据安全性和完整性的理解,并掌握SQLServer中有关用户、角色及操作权限的管理方法,学会创建和使用规则、缺省和触发器。
本实验的实验内容为:
1)数据库的安全性实验。
在SQLServerEnterpriserManager中,设置宋庆龄SQLServer的安全认证模式,实现对SQLServer的用户和角色管理,设置和管理数据操作权限。
2)数据库的完整性实验。
使用T-
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 数据库 原理 实验 指导书 答案
![提示](https://static.bdocx.com/images/bang_tan.gif)