sql server习题 实验教学文案.docx
《sql server习题 实验教学文案.docx》由会员分享，可在线阅读，更多相关《sql server习题 实验教学文案.docx（25页珍藏版）》请在冰点文库上搜索。
sqlserver习题实验教学文案
sqlserver2008习题实验
第九周【20110420/21，3节*2】复习及作业实验
一、填空或改错或判断
1、P12
（1）也是一种模型，它是对现实世界
（2）的抽象。
也就是说数据模型是用来（3）、（4）和（5）的。
（答案：
1、数据模型；2、数据特征；3、描述数据；4、组织数据；5）对数据进行操作）
2、P12通俗地讲，
（1）就是现实世界的模拟。
（数据模型）
3、P12在开发实施数据库应用系统时需要使用不同的数据模型：
第一类为
（1），第二类为
（2）。
（1、概念模型；2、逻辑模型、物理模型）
4、P12概念模型（ConceptualModel）也称信息模型，它是按用户观点来对数据和信息建模，主要用于（数据库设计）。
（问题：
E-R图是概念数据模型？
）
5、P12
（1）是按计算机系统的观点对数据建模，主要用于数据库管理系统（DBMS）的实现。
主要包括：
层次模型、网状模型、
（2）（RelationalModel）、面向对象模型和对象关系模型。
（答：
1、逻辑模型；2、关系模型）
6、P12
（1）是对数据最低层的抽象，它描述数据在系统内部的
（2），在磁盘或磁带上的存储方式和存取方法，是面向计算机系统的。
物理模型的具体实现是DBMS的任务，数据库设计人员需要了解和选择（3），一般用户不必考虑物理级的细节。
（1、物理模型；2、表示方式和存取方法；3、物理模型）
7、P13
（1）数据模型是数据库系统的核心和基础。
各种机器上实现的DBMS软件都是基于某种数据模型或者说是支持某种数据模型的。
（问题：
SQLServer2008是基于关系数据模型，或者说是支持关系数据模型的？
）
8、P13为了将现实世界中的具体事物抽象、组织为某一DBMS支持的数据模型，人们常常首先将现实世界抽象为
（1）信息世界，然后将
（1）信息世界转换为
（2）机器世界。
也就是说将，首先把现实世界的客观对象抽象为某一种信息结构，这种信息结构并不依赖于具体的计算机系统，不是某一个DBMS支持的数据模型，而是概念级的模型（如E-R图）；然后再把概念模型转换为计算机上某一DBMS支持的数据模型。
9、P13从现实世界到
（1）概念模型的转换是由数据库设计人员完成的，从
（1）概念模型到
（2）逻辑模型的转换可以由数据库设计人员完成，也可以用数据库设计工具协助设计人员完成，从逻辑模型到物理模型的转换一般由（3）DBMS完成的。
10、P13一般来讲，
（1）数据模型是严格定义的一组概念的集合。
这些概念精确描述了系统的静态特性、动态特性和完整性约束条件（integrityconstraints）。
因此数据模型通常由
（2）数据结构、数据操作和完整性约束条件三部分组成。
11、P13数据结构描述数据库的组成对象以及对象之间的联系
12、P13
（1）数据结构是刻画一个数据模型性质最重要的方面。
因此在数据库系统中，人们通常按照
（1）数据结构的类型来命名数据模型。
13、P14数据操作是指对数据库中各种
（1）对象（型）的
（2）实例（值）允许执行的操作的集合，包括（3）操作及有关（4）操作规则。
14、P14数据库主要有
（1）查询和
（2）更新（包括插入、删除、修改）两大类操作。
数据模型必须定义这些操作的确切定义、操作符号、操作规则（优先级以及实现操作的语言）
15、P15概念模型用于信息世界建模，是现实世界到信息世界的第一层抽象，是数据库设计人员进行数据库设计的有力工具，也是数据库设计人员与用户之间进行交流的语言，因此概念模型一方面应该具有较强的语义表达能力，能够方便直观地表达应用中的各种语义知识，另一方面它还应该简单、清晰、易于用户理解。
16、*P14-15信息世界涉及的概念有
**P14
（1）实体是客观存在并可相互区别的事物
**P14
（2）属性是实体所具有的某一特性
**P15（3）码是唯一标识实体的属性集
**P15（4）域是一组具有相同类型值的集合
**P15（5）实体型是具有相同属性的实体必须具有共同的特征和性质。
用实体名及其属性名集合来抽象和刻画同类实体称为（5）实体型。
**P15（6）实体集是同一类实体的集合称为实体集
**P15在现实世界中，事物内部以及事物之间是有联系的，这些联系在信息世界中反映为（7）实体（型）内部的联系和（8）实体（型）之间的联系。
实体内部的联系通常是指组成实体的各属性之间的联系；实体之间的联系通常是指实体集之间的联系。
**P15两个实体型之间的联系（1:
1，1:
n，m:
n）
**P16两个以上实体型之间的联系
**P16单个实体型内的联系（同一个实体集内部实体之间的1:
1,1:
n,m:
n联系）
17、P17概念模型的一种最著名最常用表示方法是实体-联系方法
关系术语
一般表格术语
1
关系名
表名
2
关系模式
表头（表格的描述）
3
关系
（一张）二维表
4
元组
记录或行
5
属性
列
6
属性名
列名
7
属性值
列值
8
分量（元组中的一个属性值）
一条记录中的列值
9
非规范关系（关系模型要求关系必须是规范化的P29）
表中有表（大表中套小表）
18、P30把关系和现实生活中的表格所使用的术语做一个初略的对比
19、P21工资表关系是否是一个规范化关系？
工资（职工号，姓名，职称，工资包括基本工资津贴职务工资，扣除房租与水电，实发）
学生表中的列“入校毕业年份”？
20、P30关系数据模型的操作（查询、插入、删除、修改）（对照P14）。
这些操作必须满足关系的完整性约束条件。
21、P30在关系数据模型中实体与实体间的联系都用表（即关系）来表示
22、P30在关系数据库的物理组织中，有的DBMS一个表对应一个操作系统文件，有的DBMS从操作系统获得若干个大的文件，自己设计表索引等存储结构。
（对于SQLServer2008来说是否是一个表对应一个操作系统文件？
）
23、P31在数据模型中有型和值的概念。
型是指对某一类数据的结构和属性的说明，值是型的一个具体赋值。
24、P31模式是数据库中全体数据的逻辑结构和特征的描述，它仅仅涉及到型的描述，不涉及到具体的值。
模式的一个具体值称为模式的一个实例。
同一个模式可以有很多实例。
25、P47在一个给定的应用领域中，所有实体以及实体之间联系的关系的集合构成一个关系数据库。
26、P47关系数据库也有型和值之分。
关系数据库的型也称为关系数据库模式，是对关系数据库的描述。
关系数据库模式包括：
若干域的定义；在这些域上定义的若干关系模式。
27、P48关系数据库的值是这些关系模式在某一时刻对应的关系的集合，通常称为关系数据库。
28、P44关系可以有三种类型：
基本关系（又称为基本表或基表）、查询表和是图标
*基本表是实际存在的表，它是实际存储数据的逻辑表示
*查询表是查询结果对应的表
*视图表是由基本表或其他视图表导出的表，是虚表，不对应实际存储的数据。
29、P45关系的分量必须取原子值，即每个分量都必须是不可分割的数据项。
30、P45任意两个元组的候选码不能相同（有些关系数据库产品中允许关系表中存在两个完全相同的元组，除非用户特别定义了相应的约束条件）
31、P51任何关系数据库系统都应该支持实体完整性和参照完整性。
这是关系模型所要求的。
用户自定义的完整性就是针对某一具体关系数据库的约束条件。
它反映某一具体应用所涉及的数据必须满足的语义要求。
32、P85建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存放入系统的数据字典中。
当用户操作表中的数据时，由DBMS自动检查该操作是否违背这些完整性约束条件
33、P85系统执行建表语句后，就在数据库中建立一个新的空的学生表，并将有关表的定义及有关约束条件存放在数据字典中。
34、P86在SQL中域的概念用数据类型来实现
35、P224）E-R图向关系模型的转换要解决的问题是如何将实体型和实体间的联系转换为关系模式，如何确定这些关系模式的属性和码。
36、将实体型、实体的属性和实体间的联系转换为关系模式的主转换原则（P225）：
一个实体型转换为一个关系模式；联系转换（1:
1,1:
n,m:
n）?
37、
38、
二、数据库设计（E-R图设计，将E-R图转换为关系模式）
1、P17概念模型一个实例：
工厂物资管理概念模型，P19两种ER图的表示法。
将P19图1.14（c）转换为关系模式。
2、实验三（E-R图设计）：
P38第12、13，P74第5
3、P195第2题E-R图
4、补充
根据以下需求，回答后述问题。
（1）医院里只有医生可以开处方单，每个医生可以开多张处方，每张处方只能由一个医生开。
处方单的属性有：
处方单号（为主码）、开单日期。
医生的属性有：
职工号（为主码）、姓名、性别、年龄。
（2）一张处方单对应一个病人，但每个病人可能有多张处方单。
病人的属性有：
病历号、姓名、性别、年龄、主要疾病（一种）。
（3）一张处方单包含很多明细行，每行描述了客户使用的一种药品，一种药品可以出现在一张处方的多个明细行中。
明细行的属性有：
行号、药品代码、药品名称、摘要、数量、剂量。
（4）药品的属性有：
药品代码、药品名称、用途。
（5）每种药品有多个供应商，每个供应商可能供应多种药品。
供应商的属性有：
供应商编号、供应商名称、地址。
问题：
1）创建该系统的E－R图；（8分）
答：
1
n
1n
1m
nn
n1
2）给出相应的关系模式；（8分）
答：
医生（职工号、姓名、性别、年龄）
处方单（处方单号、开单日期、病历号、职工号）（1、2）
病人（病历号、姓名、性别、年龄、主要疾病）
明细行（行号、摘要、数量、剂量、药品代码、处方单号）（3、4）
药品（药品代码、药品名称、用途）
供应商（供应商编号、供应商名称、地址）
供应（药品代码、供应商编号）（5）
（少一联系描述扣一分，少一实体描述扣一分）
5、补充根据以下需求，回答后述问题。
（1）发货单由销售代表填写，每名销售代表都可以填写多个发货单，但每个发货单只能由单独的一名销售代表来填写。
发货单中的属性有：
发货单号（为主码）、发货日期、发货方式。
销售代表的属性有：
代表编号（为主码）、姓名、性别、年龄、分管区。
（2）一个发货单对应一名客户，但是，每名客户可能有多个发货单。
客户的属性有：
客户编号、姓名、性别、年龄、爱好、地区。
（3）一个发货单包含很多明细行，每行描述了客户所购买的一种产品，一种产品可以出现在一个发货单的多个明细行中。
明细行的属性有：
行号、产品代码、产品名称、摘要、数量、销售单价、金额。
（4）产品信息保存在一个称为产品的实体中。
产品的属性有：
产品代码、产品名称、颜色（每种产品的颜色只有一种）。
（5）每个产品只有一个供应商，每个供应商可能供应多种产品。
供应商的属性有：
供应商编号、供应商名称、地址。
问题：
1）创建该系统的E－R图；（8分）
代表编号
姓名
年龄
分管区
答：
供应商
销售代表
填写1
发货单
包含5
对应2
客户
明细行
包含3
产品
包含4
性别
发货单号
发货日期
发货方式
客户编号
姓名
性别
年龄
爱好
地区
摘要
数量
销售单价
金额
行号
产品代码
产品名称
颜色
供应商编号
供应商名称
地址
1
n
1n
11
nn
n1
评分标准：
每一个实体或联系错误扣0.5分；
错误比较多的：
明细行中包括产品代码、产品名称
2）给出相应的关系模式；（8分）
答：
发货单（发货单号、发货日期、发货方式、代表编号、客户编号）（1、2）
销售代表（代表编号、姓名、性别、年龄、分管区）
客户（客户编号、姓名、性别、年龄、爱好、地区）
明细行（行号、摘要、数量、销售单价、金额、发货单号、产品代码）（3、4）
产品（产品代码、产品名称、颜色、供应商编号）（5）
供应商（供应商编号、供应商名称、地址）
6、
三、基于3-7周
四、基于第8周
1、在E盘的db目录下建立名为test数据库，要求文件的大小为5M，当文件被写满时能自动增长20%，请完善下面的语句以便在SQLServer2008中实现建表test数据库功能
CREATEDATABASEtest
ON（NAME=
FILENAME=
SIZE=
FILEGROWTH=）
LOGON（NAME=
FILENAME=,
SIZE=
FILEGROWTH=）
COLLATEChinese_PRC_CI_AS--指定数据库的默认排序规则
2、在test数据库中创建表s【加完整性】
createtables
（
snochar（8）,--primarykey,
snamechar（20）unique,
ssexchar
（2）,
sageintcheck（sage>=15andsage<=36）,
sdeptchar（20）notnull,
constraintPK_Sprimarykey（sno）–显式定义约束名
1）constraintPK_S的作用是什么
2）对主键约束来说，如果不显式定义约束名，是否意味系统没有约束名？
3）指出此建表语句有哪些完整性约束条件定义，并说明哪些是表级约束哪些是列级约束
4）如果向空的s表中插入两条记录，其SQL命令为：
insertintosvalues（'1002','n1','f',20,'信电系'）
insertintosvalues（'1002','n2','f',40,'信电系'）
此两语句是否能执行成功？
如不成功说明违背了什么完整性约束条件
3、学生选课信息管理系统的数据库设计结果有以下两个方案，请比较两个方案的优劣
方案一：
三个关系模式
学生（学号，姓名，性别，年龄，系，）
课程表（课程号，课程名，先修课，学分）
选课（学号，课程号，成绩）
方案二：
一个关系模式
学生选课（学号，姓名，性别，年龄，系，课程号，课程名，先修课，学分，成绩）
4、如果学生选课管理系统中有如下一组关系模式
学生（学号，姓名，性别，年龄，系，班号）
课程表（课程号，课程名，先修课，学分）
选课（学号，课程号，成绩）
班级（班号，班名，人数）
请问哪些表只描述了实体，哪些表只描述了联系，哪些表既描述了实体也描述了实体之间的联系？
指出这些表的外码并说明其语义以及是否能取空值？
班级表中的人数是否一定需要描述？
为什么？
5、课程表C学生表S与学生选课表SC的内容为及建表语句为：
S表：
SC表：
usejsj
/*Table:
course*/
createtablec（
cnochar（8）constraintPK_Cprimarykey,--列级显式定义约束名
cnamechar（20）,
cpnochar（8）constraintfk_c_cpnoreferencesc（cno）,--列级显式定义约束名
ccreditint
）
/*Table:
student*/
createtables（
snochar（8）primarykey,--未给出约束名，DBMS自动命名
snamechar（10）null,
ssexchar
（2）,
sageint,
sdeptchar（20）
）
/*Table:
sc*/
createtablesc（
snochar（8）,
cnochar（8）,
gradeintegernull,
primarykey（sno,cno）,--问题：
是否可以定义在列级？
（不可以）
foreignkey（sno）referencess（sno）,
foreignkey（cno）referencesc（cno）--问题：
是否可以定义在列级？
）
请问以下语句是否能执行成功？
为什么？
--增加SC表记录（外码取值约束）
usejsj
insertintoscvalues（98001,1,null）;--出错原因？
sno未取S表中元组的主码分量值？
--修改SC表记录（外码取值约束）
usejsj
updatescsetsno='98001'wheresno='97001'
问题:
sno改为'97101'是否可以?
6、请距离说明SC表定义中的ondeletecascadeonupdatecascade含义
usejsj
--droptablec;
createtablec
（
cnochar（8）constraintPK_Cprimarykey,--列级约束，显式定义约束名
cnamechar（20）,
cpnochar（8）constraintfk_c_cpnoreferencesc（cno）ondeleteNOACTION,
ccreditint
）;
--droptables;
createtables
（
snochar（8）,--primarykey,
snamechar（20）unique,
ssexchar
（2）,
sageint,
sdeptchar（20）,
constraintPK_Sprimarykey（sno）,--表级约束显式定义约束名
constraintcheck_scheck（ssexin（'f','m'）andsage<36）--元组级命名check约束
--或者
--check（ssexin（'f','m'）andsage<36）--元组级非显式命名check约束【自动命名】
）;
--注：
主键与唯一约束自动建立索引
--droptablesc;
createtablesc
（
snochar（8）referencess（sno）ondeletecascadeonupdatecascade,
--cascade表示当删除或修改主表中被引用列的数据时，级联删或修改子表中相应的数据行
--或者setnull表示当删除主表中被引用列的数据时，将子表中相应引用列的值设置为null
--或者noaction表示当删除主表中被引用列的数据时，如果子表中引用的列包含该值，则禁止操作执行【默认】
cnochar（8）,--referencesc（cno）ondeletecascade,
gradeintegernull,
primarykey（sno,cno）,
constraintfk_sc_cnoforeignkey（cno）referencesc（cno）--列级显示命名约束定义
7、用关系代数以及SQL语言表达查询
1）查询年龄小于20学生的姓名的学号、姓名、年龄
2）学生表与学生选课表的（广义）笛卡尔积
3）学生表与学生选课表的等值连接与自然连接
4）查询选修了2号课程学生的学号及姓名，如何表达查询？
（连接查询及嵌套查询）
5）教材P105【例40】查询选修了课程名为“数据库原理”的学生学号和姓名（使用关系代数及SQL表达嵌套即连接查询）
6）P112【例51】查询既选修了课程1又选修了课程3的学生（用where表达交集）
7）查询选修了全部课程的学生姓名：
查询这样的学生，没有一门课是他不选的
8）查询至少选修了学生1选修的全部课程的学生号码：
查询这样的学生x，不存在这样的课程y，学生1选修了y，而学生x没有选。
8、
五、基于第8周
1、在SQLserver2008中下列语句的查询结果表的列名是什么？
如何给2011-sage计算列命名？
selectupper（sname）,'生日',2011-sage,lower（sdept）froms
2、在SQLServer2008中，查询各课程的平均成绩（思路：
依课程分组，求每组的平均成绩）的如下SQL语句中，如果将'avg（grade）'的引号去掉会出现什么问题？
usejsj
select*fromsc;
selectcno,avg（grade）'avg（grade）'-----问题：
去掉‘’情况？
fromscgroupbycno;--集函数作用于组（先分组）
3、请问selectsname,sage,sdeptfromswheresdeptin（'信电系'）中的in是否可以改为等号？
4、使用like在课程表C中查找课程名带下划线的课程记录
如何查找课程名带下划线的两门课
selectcno,cnamefromcwherecnamelike'%\_%'escape'\';
即让通配符_转变为自己（失去通配功能）
同样可以让%失去通配功能，变为字符本身
5、在课程表中查找学分为空的记录以下查询语句是否正确？
select*fromcwhereccredit=null;
6、在SQLServer2008中，下面查询语句的查询结果表中姓名为空值的记录排在最前面
selectcno,cnamefromcorderbycnamedesc;
字符串空值最小
7、在SQLServer2008中，下面查询语句的查询结果表中成绩空值排在最后面
selectcno,sno,gradefromsc
wherecnoin（1,2,3）orderbygradedesc;--空值最小
8、求每门课程的平均成绩，求每个同学的平均成绩？
9、求一门课的最高、最低、平均、总分？
10、查询各课程的课程号与相应的选修人数
11、【例】查询课程的平均成绩大于90分的cno与平均成绩等
思路：
*依课程分组，求每组的平均成绩（图2）
*从每组的平均成绩查询表中查找平均成绩大于90元组（图3）
*作用在分组上的条件可以是复杂条件：
havingavg（grade）>=90andcno<>1（图4）
usejsj
select*fromsc;----图1
selectcno,avg（grade）'avg（grade）'
fromscgroupbycno;--集函数作用于组（先分组）图2
---在前面的查询中加作用在分组上的条件：
havingavg（grade）>=90
selectcno,avg（grade）'avg（grade）>90'
fromscgroupbycno--集函数作用于组（先分组）
havingavg（grade）>=90;---图3分组后只输出满足条件的组
图1
图2
图3
selectcno,avg（
升级会员 