1-8 关系数据库与SQL实训
- 完成建立数据库实训任务
- 完成建立，并修改基本表实训任务
- 完成综合数据库设计实训任务
- 完成索引实训任务
- 完成SQL综合查询任务实训
1-8-1 建立数据库实训
1. 实训一：建立数据库实训
在查询窗口中创建数据库test1，建立的数据文件及日志文件的基本信息要求如下表所示，请按照规定的参数编写代码，并可以成功运行。如表1-9所示。
表1-9 建立主外键之间的关系
|
参数
|
参数值
|
|
数据库名称
|
Test1
|
|
数据库逻辑文件名称
|
Test1_data
|
|
操作系统数据库文件
|
C:\mssql\data\test1_data.mdf
|
|
数据文件初始化大小
|
10M
|
|
数据文件最大值及文件增长
|
50M 20%
|
|
参数
|
参数值
|
|
日志逻辑文件名称
|
Test1_ log
|
|
操作系统日志文件
|
C:\mssql\data\test1_log.ldf
|
|
数据文件初始化大小
|
5M
|
|
数据文件最大值及文件增长
|
5M，25M
|
2. 实训二：修改数据库实训
请将刚刚建立的test1数据库进行修改，具体修改的基本参数见下表所示。如表1-10所示。
表1-10 修改test1数据库基本参数表
|
参数
|
参数值
|
|
数据库名称
|
Test1
|
|
增加的文件组名称
|
Test1_fg1
|
|
增加的文件1的逻辑名
|
Test1_dat3
|
|
增加的文件1在磁盘中的路径
|
C:\mssql\data\test1_dat3.ndf
|
|
增加的文件1初始化大小
|
5M
|
|
文件1最大值及文件增长
|
50M/5M
|
|
增加的文件2的逻辑名
|
Test1_dat4
|
|
增加的文件2在磁盘中的路径
|
C:\mssql\data\test1_dat4.ndf
|
|
增加的文件2初始化大小
|
5M
|
|
文件2最大值及文件增长
|
50M/5M
|
|
增加的日志文件名
|
Test1_log2
|
|
增加的日志文件在磁盘中路径
|
C:\mssql\data\test1_log2.ldf
|
|
增加的日志文件初始化大小
|
5M
|
|
增加的日志文件最大值及文件增长
|
100M/5M
|
1-8-2 建立/修改基本表实训
1. 建立基本表实训
请你建立school数据库，其逻辑关系如图1-47所示：
图1-47 school数据库逻辑关系图
2. 修改基本表实训
请重新创建数据库school1，按照下面的代码生成四张基本表student，sc，course，Teacher表（注意：无任何约束条件和限制）。请你建立如下代码并运行，其执行后将建立数据库school1，并且生成若干不符合数据库规范的基本表，请你最后按照要求进行修改基本表的练习。
use master
go
--开始建立school1数据库
create database school1
on
(name=school1_data,
filename='E:\Program Files\
Microsoft SQL Server\MSSQL\Data\school1_data.mdf',
size=5,
maxsize=15,
filegrowth=10%)
log on
(name=school1_log,
filename='E:\Program Files\Microsoft SQL Server\MSSQL\Data\school1_log.ldf',
size=5,
maxsize=15,
filegrowth=10%)
--调用school1数据库
use school1
--在school1数据库下面建立表student
create table student
(sno int primary key identity(1,1),
sname varchar(12),
sdept char(20),
sage int)
--建立基本表course
create table course
(cno int primary key identity(1,1),
cname varchar(16),cpno char(10),credit int)
--建立基本表sc
create table sc
(sno char(10),cno char(10),grade int)
--建立基本表teacher
create table teacher
(tno char(10),tname char(14),tage int,tdept varchar(12))
根据school1数据库不规范的实际情况，请你立即完成下面的实训修改练习：
(1) 将sdept由char改变成为varchar
(2) 将cpno变成为与cno相同的属性
(3) 指定cpno必须受到cno的参照完整性的约束
(4) 将SC表中的sno和cno同时指定成为主键
(5) 将grade变成为float类型
(6) 设定sc表的参照完整性约束
(7) 修改teacher表中的tno
(8) 在teacher表中增加一项内容 ,cno并且增加参照完整性
(9) 将tname的char变成为varchar
(10)分别给student和teachar表增加性别项，并且仅仅只能够是‘男’‘女’
(11)将学生的年龄限制在12——40之间
1-8-3 综合数据库设计实训
1．实训背景说明：
创建产品销售数据库（CPXS），初始化大小10，最大100，10%增长。其中的基本表情况如下：
（1）产品入库表（产品流水号，产品编号，入库时间，产品名称，单位价格，库存量，货物价值），（限制条件：库存量必须小于10000，单位价格*库存量=货物价值，产品流水号为自动增长，产品编号为字符类型和产品自然编号吻合；入库时间默认为计算机当前的时间）。
（2）销售商表（销售商编号，销售商名称，地区，负责人，性别，电话，登记时间）（限制条件：性别为男女，电话必须是14位数字以内，登记时间默认为计算机当前的时间，销售商编号为字符类型）。
（3）产品销售表（销售日期，产品编号，客户编号，数量，销售额，经手人）或者产品销售表（销售日期，产品流水号，客户编号，数量，销售额，经手人），请问哪个正确呢？（限制条件：销售的数量必须小于某种产品到目前为止的销售数量之总和，销售额=单位价格*数量，经手人必须出自员工信息表；销售日期默认为计算机当前的时间）。
（4）员工数据表（员工编号，姓名，性别，年龄，职位，部门编号，岗位）（限制条件：部门编号必须来自部门表，年龄低于60）。
（5）部门数据表（部门编号，名称，数据表，电话）（限制条件：经理编号必须来自员工数据表，电话必须是14位数字以内）。
2．实训要求1：
请按照图1-48创建的逻辑关系图建立关系数据库，如果不要将产品流水号作为主键，将产品号，入库时间联合作为主键行吗？
图1-48 建立主外键之间的关系
3．实训要求2：
请你完成下面的4项实训任务：
(1) 用T—SQL设计数据库和表，指定表的主键和外键，设定用户定义的完整性。
(2) 每张表输入10条以上的数据。
(3) 将产品入库表中的货物价值按照规定的数值批量填充进去。
(4) 将产品入库表中增加一项属性“增税”，增税=货物价值×2%；再增加一项税后价格，税后价格=增税+货物价值。
1-8-4 索引实训
1．实训要求：
本实训的目标是能够使用 SQL Server Management Studio 和 Transact-SQL 创建索引。
（1）建立课程表course，teacher的聚簇性索引和唯一性索引；建立对课程名cname以及教师名tname的非聚簇性索引。要求首先用命令行方式完成，而后再用管理平台完成。
（2）建立成绩表score的聚簇性索引和唯一性索引。注意：score表的键属于宽键，由sno,cno共同构成。
1-8-5 SQL设计编码实训
1．实训背景说明：
请你首先建立下面的四张表（表1-11至表1-14），并录入表中的具体数据内容。而后，完成下面的39道SQL查询的基本任务，由于每个人在进行阅读题目语义时候可能会产生歧义，加之完成同样的SQL查询任务时候代码书写可能会完全不同，因此建议实训后由教师进行集体点评，并总结可能会遇到的典型错误，归纳出较好的SQL设计思路，以提高学生SQL查询技能。
表1-11 Student表 表1-12 Score表
|
NO
|
NAME
|
SEX
|
BIRTHDAY
|
CLASS
|
|
108
|
曾华
|
男
|
09/01/77
|
95033
|
|
105
|
匡明
|
男
|
10/02/75
|
95031
|
|
107
|
王丽
|
女
|
01/23/76
|
95033
|
|
101
|
李军
|
男
|
02/20/76
|
95033
|
|
109
|
王芳
|
女
|
02/10/75
|
95031
|
|
103
|
陆军
|
男
|
06/03/74
|
95031
|
|
110
|
张明
|
男
|
07/05/80
|
95033
|
|
NO
|
CNO
|
DEGREE
|
|
103
|
3-245
|
84
|
|
105
|
3-245
|
67
|
|
109
|
3-245
|
98
|
|
103
|
3-105
|
78
|
|
105
|
3-105
|
91
|
|
109
|
3-105
|
68
|
|
101
|
3-105
|
93
|
表1-13 Course表 表1-14 Teacher表
|
CNO
|
CNAME
|
TNO
|
|
3-105
|
计算机导论
|
825
|
|
3-245
|
操作系统
|
804
|
|
6-166
|
数字电路
|
856
|
|
9-888
|
高等数学
|
100
|
|
NO
|
NAME
|
SEX
|
BIRTHDAY
|
PROF
|
DEPART
|
|
804
|
李城
|
男
|
12/02/58
|
副教授
|
计算机系
|
|
856
|
张旭
|
男
|
03/12/69
|
讲师
|
电子工程系
|
|
825
|
王萍
|
女
|
05/05/72
|
助教
|
计算机系
|
|
831
|
刘冰
|
女
|
08/14/77
|
助教
|
电子工程系
|
2．实训要求：
(1) 查询student表所记录的name,sex,class列；
(2) 显示教师工作的单位（不许重复）；
(3) 以汉字属性显示student表所有记录；
(4) 查询成绩在60到80之间的同学姓名；
(5) 查询成绩为80，85，或88的记录；
(6) 查询‘95031’班或者性别为‘女’的同学记录；
(7) 以“班级”降序显示student中的记录；
(8) 以cno升序、degree降序显示score中的记录；
(9) 显示‘95031’班中的学生人数；
(10)显示score表中分数最高的学生的学号和课程号；
(11)显示‘3-105’号课程的平均分数；
(12)显示至少有5名学生选修的并以‘3’开头的课程号的平均分数；
(13)显示最低分数大于70，最高分数低于90的no列；
(14)查询学生的姓名、所学的课程号以及该课程的成绩；
(15)查询95033班所选课程的平均分数；
(16)显示选修3-105课程的成绩高于109号同学成绩的所有同学的记录；
(17)查询选修多门课程的同学中分数为非最高成绩的记录；
(18)查询成绩高于学号为‘109’号、课程号为‘3-105’的成绩的所有记；
(19)查询和学号为108的同学同年出生的所有学生的no,name,birthday列；
(20)显示“张旭”老师任课的学生姓名和成绩；
(21)显示选修某课程的同学人数多余5人的教师姓名；
(22)显示95033班和95031班全体学生的记录；
(23)查询存在有85分以上的成绩的课程号；
(24)查询“计算机系”教师所教课程的成绩表；
(25)查询“计算机系”与“电子工程系”不同职称的教师的name和prof；
(26)查询选修课编号是“3-105”课程且成绩至少高于选修课程编号为“3-245”的同学的cno,no和degree，并且degree从高到低次序排列；
(27)在屏幕上列出选修编号为“3-105”并且成绩高于选修课程编号为“3-245”课程的同学的cno,no,degree；
(28)查询成绩比该课程平均成绩低的同学的成绩表；
(29)查询所有未讲课的教师的name和depart；
(30)查询至少有2名男生的班级号码；
(31)查询不姓“王”的同学记录；
(32)查询每个学生的姓名和年龄；
(33)查询最大和最小生日值及其姓名；
(34)以班号和年龄从大到小的顺序查询student中的所有信息；
(35)查询男老师及其所上的课程名称；
(36)查询平均成绩最低的学生姓名及其平均成绩；
(37)查询和“李军”同性别的所有学生的姓名；
(38)查询和“李军”同性别但是并不同班的所有学生的姓名；
(39)查询所有选修“计算机导论”的男学生成绩表。
l 掌握数据库文件的种类及数据库文件的命名方式，掌握数据文件页和区的概念；
l 掌握数据库文件的类型，可以较熟练的在管理平台下建立数据库文件和文件组，通过SQL语句建立和修改数据库文件及日志文件，通过SQL语句对数据库的文件进行收缩；
l 掌握通过SQL语句创建基本表，特别是完成关系逻辑模式的设计，对主键和外键关联性的定义；熟悉SQLServer的主要数据类型；
l 掌握通过SQL语句修改基本表的模式结构以及通过SQL删除基本表；
l 学习索引的基本概念和特性；
l 掌握堆、聚簇索引、非聚簇索引、唯一性索引的内涵，可以建立与删除索引的SQL语句，可以获取及优化索引信息的方法；
l 熟练掌握插入操作（insert），删除操作（delete），更新操作（update）的基本SQL语句；
l 熟练掌握SQL查询的更名，取值重复行，条件查询，集函数与分组查询；熟练设计多表连接查询，单表的自身连接查询以及多表嵌套查询；掌握SQL86与SQL92语法的异同点，掌握SQL86与SQL92实现内连接查询、左外连接和右外连接查询；了解SQL交叉与无限制连接查询，SQL集合并与交的查询，关系整除的SQL查询方法，近似除与关系整除的查询以及全称谓词查询；
l 掌握如何建立和删除视图的SQL语句，特别是参数WITH CHECK OPTION的使用特点；
一 . 填空题
1、________是由一个或多个数据表（基本表）或视图导出的虚拟表。
2、索引的类型有__________和非聚集索引。
3、SQL Server聚合函数有最大、最小、求和、平均和计数等，它们分别是_______、_______、_______、avg和count。
4、在 SQL Server 中，页的大小为______KB，每MB有_____页，一个区是______个物理上连续的页，每MB有______个区
二 . 选择题
1、在SQL中，建立表用的命令是 ( )。
A.CREATE SCHEMA B.CREATE TABLE C. CREATE VIEW D.CREATE INDEX
2、 SQL语言中，条件年龄 BETWEEN 15 AND 35表示年龄在15至35之间，且( )。
A.包括15岁和35岁 B.不包括15岁和35岁
C.包括15岁但不包括35岁 D.包括35岁但不包括15岁
3、下列四项中，不正确的提法是( )。
A.SQL语言是关系数据库的国际标准语言
B.SQL语言具有数据定义、查询、操纵和控制功能
C.SQL语言可以自动实现关系数据库的规范化
D.SQL语言称为结构查询语言
4、SQL语言中，删除表中数据的命令是( )。
A. DELETE B. DROP C. CLEAR D. REMOVE
5、SQL的视图是从（ ）中导出的。
A. 基本表 B. 视图 C. 基本表或视图 D. 数据库
6、下列哪个不是sql 数据库文件的后缀。
A．.mdf B．.ldf C．.tif D．.ndf
7、建立索引的目的是（ ）。
A．降低SQL Server数据检索的速度 B．与SQL Server数据检索的速度无关
C．加快数据库的打开速度 D．提高SQL Server数据检索的速度
8、以下关于外键和相应的主键之间的关系，正确的是( )。
A.外键并不一定要与相应的主键同名 B.外键一定要与相应的主键同名
C.外键一定要与相应的主键同名而且唯一
D.外键一定要与相应的主键同名，但并不一定唯一
9、SQL中，下列涉及空值的操作，不正确的是( )
A、age IS NULL B、age IS NOT NULL C、age = NULL D、NOT (age IS NULL)
10、每个数据库有且只有一个( )
A、主要数据文件 B、次要数据文件 C、日志文件 D、索引文件
11、当关系R和S自然联接时，能够把R和S原该舍弃的元组放到结果关系中的操作是( )
A、左外联接 B、右外联接 C、内联接 D、外联接
三 . 设计题
1. 已知有如下4个表：
供应商表S(SNO,SNAME,CITY)，零件表J(JNO,JNAME,COLOR,WEIGHT)，工程表P(PNO,PNAME,CITY)，供应情况表SPJ(SNO,PNO,JNO,QTY)
其中，SNO、SNAME、CITY分别表示供应商代码、供应商姓名、供应商所在城市，JNO、JNAME、COLOR、WEIGHT分别表示零件代码、零件名、颜色和重量，PNO、PNAME、CITY分别表示工程代码、工程名、工程所在城市，QTY表示某供应商供应某工程某种零件的数量。
分别写出SQL语句，完成如下功能：
(1)查询出重量大于30或颜色为"red"的零件名；
(2)查询出每个供应商为每个工程供应零件的数量
(3)查询出给"北京"的工程供应"齿轮"零件的供应商名；
(4)建一个视图，定义为所有"green"颜色的零件。
2. 图书出版管理数据库中有两个基本表：
图书 (书号，书名，作者编号，出版社，出版日期)
作者 (作者编号，作者名，年龄，地址)
试用SQL语句写出下列查询：检索年龄低于作者平均年龄的所有作者的作者名、书名和出版社。
3. 现有关系数据库如下：
学生(学号，姓名，性别，专业、奖学金) 课程(课程号，名称，学分) 学习(学号，课程号，分数) 用SQL实现：
(1)查询没有获得奖学金、同时至少有一门课程成绩在95分以上的学生信息，包括学号、姓名和专业；
(2)查询没有任何一门课程成绩在80分以下的所有学生的信息，包括学号、姓名和专业；
(3)对成绩得过满分(100分)的学生，如果没有获得奖学金的，将其奖学金设为 1000元；
(4)定义学生成绩得过满分(100分)的课程视图AAA，包括课程号、名称和学分；
