SQL Server 2005复习指南 上机内容

SQL server 2005 数据库及应用上机实验内容

实验一：SQL Server 应用基础（第一章）

实验内容：使用SQL进行简单查询 实验目的：熟悉环境 实验要求：

（一）打开SQL Server Management Studio，熟悉界面； （二）附加XK数据库；

（三）查看XK数据库中表的内容； （四）使用查询编辑器进行简单查询：

1.查询学生的所有信息： USE Xk GO SELECT * FROM Student GO

2.从学生表中查询所有学生的姓名： USE Xk GO Select stuname From student go

08计应3班 时间：2010年8月30日星期一 14:30-16:10 08计应2班 时间：2010年9月1日星期三 8:00-9：40 08计应1班 时间：2010年9月3日星期五 8:00-9：40

实验二 SQL Server应用基础（第一章）

实验内容：概念模型的创建

实验目的：掌握数据库的概念设计方法 实验要求：

图书馆借阅系统的设计需求分析：

1

图书馆的图书每种不止一本，每个读者一次可以借多本书，一本书可以由多个读者借。 按照下面的步骤进行E-R图的设计：

需求分析→定义实体→定义联系→定义主码→定义属性→绘制E-R图 1.按照：实体（属性1，属性2，??）的格式分别写出各实体和联系的属性 2.标出各实体和联系的主码和外码 3.在Word中绘制出E-R图 作业上交方式：

打开ftp://222.21.113.155 用户名：3，密码为空

参考答案：

读者（读者编号，姓名，类型编号，已借数量）

图书（图书编号，书名，ISBN，类别编号，作者，出版社，出版日期，定价，是否借出） 读者借书表（读者编号，图书编号，借出日期，还书日期，是否超期） 读者类型（类型编号，类型，借书期限） 图书类别（类别编号，图书分类）

读者编号

姓名

类型编号

已借数量

读者

读者编号

n 借阅

图书编号

m 图书

还期 借期

图书编号

书名 作者 出版社 出版日期 定价

08计应3班 时间：2010年9月6日星期一 14:30-16:10

2

实验二 SQL Server应用基础（第二章）

实验内容：1.概念模型的创建 2.数据库的日常维护 实验目的：1.掌握数据库的概念设计方法 2.掌握数据库的日常维护方法 实验要求： （一）E-R图的绘制

图书馆借阅系统的设计需求分析：

图书馆的图书每种不止一本，每个读者一次可以借多本书，一本书可以由多个读者借。 按照下面的步骤进行E-R图的设计：

需求分析→定义实体→定义联系→定义主码→定义属性→绘制E-R图 1.按照：实体（属性1，属性2，??）的格式分别写出各实体和联系的属性 2.标出各实体和联系的主码和外码 3.在Word中绘制出E-R图 （二）身份验证方式及登录名

1.把Windows身份验证模式改为混合身份验证模式 2.修改登录名sa的密码

3.使用SQL Server身份验证，使用sa用户连接 4.新建登录名，并使用新建的登录名连接数据库 （三）附加、分离、备份、还原数据库

1.附加XK数据库 2.备份XK数据库

选择“XK数据库”，右键-任务-备份

此时弹出备份窗口，选择“备份类型”为“完整”；在“目标”选项区域中，指定备份文件的磁盘位置（默认）。确定。 3.分离XK数据库 4.还原数据库

在对象资源管理器中，选中“数据库”，右键，“还原数据库”，在“目标数据库”中，键入新数据库的名称（自己的姓名）。

3

在“源数据库”中选择要还原的数据库，确定。

参考答案：

读者（读者编号，姓名，类型编号，已借数量）

图书（图书编号，书名，ISBN，类别编号，作者，出版社，出版日期，定价，是否借出） 学生借书表（读者编号，图书编号，借出日期，还书日期，应还日期） 读者类型（类型编号，类型，借书期限，限制数量） 图书类别（类别编号，图书分类）

读者编号

姓名

类型编号

已借数量

读者

读者编号

n 借阅

图书编号

m 图书

还期 借期

图书编号

书名 作者 出版社 出版日期 定价

08计应2班 时间：2010年9月8日星期三 8:00-9：40 08计应1班 时间：2010年9月10日星期五 8:00-9：40

实验三 SQL Server数据库基础（第二章）

实验内容：数据库的日常维护 实验目的：掌握数据库的日常维护方法 实验要求：

（一）配置SQL Server 2005的连接 打开SQL Server Configuration Manager

1.对SQL Server（MSSQLSERVER）服务进行停止和启动操作

4

2.对服务器协议和客户端协议进行启动和禁用的操作

3.打开控制面板—管理工具—服务，对SQL Server（MSSQLSERVER）服务进行停止和启动操作 （二）使用【已注册的服务器】

从【视图】菜单中找到【已注册的服务器】 删除数据库引擎中已有的服务器

右击数据库引擎，新建，数据库引擎，选择新的服务器进行注册 （三）身份验证方式及登录名

1.把Windows身份验证模式改为混合身份验证模式 2.修改登录名sa的密码

3.使用SQL Server身份验证，使用sa用户连接 4.新建登录名，并使用新建的登录名连接数据库 （四）附加、分离、备份、还原数据库

1.附加XK数据库 2.备份XK数据库

选择“XK数据库”，右键-任务-备份

此时弹出备份窗口，选择“备份类型”为“完整”；在“目标”选项区域中，指定备份文件的磁盘位置（默认）。确定。 3.还原数据库

在对象资源管理器中，选中“数据库”，右键，“还原数据库”，在“目标数据库”中，键入新数据库的名称（自己的姓名）。

在“源数据库”中选择要还原的数据库，确定。 4.分离XK数据库

08计应3班 时间：2010年9月13日星期一 14:30-16:10

实验三 数据库的创建及管理（第三章）

实验内容：数据库的创建及管理 实验目的：掌握数据库的创建及日常管理 实验要求：

5

一、创建数据库

1．使用SSMS创建名字为自己汉语名字的数据库，数据库主数据文件名为姓名_data，大小为3MB-5MB，增量为10%，事务日志文件为姓名_log，大小为2MB-10MB，增量为1MB；

2．使用T-SQL语句创建名字为Sale的数据库，数据库主数据逻辑文件名为Sale_Data，物理文件名为Sale_Data．mdf，大小为5MB-10MB，增量为1MB；事务日志逻辑文件名为Sale_log，物理文件为Sale_log．ldf，大小为8-20MB，增量是10%。（保存到E盘） 二、删除数据库

1．使用SSMS创建名字为Hello的数据库，并使用SSMS删除该数据库； 2．使用SSMS创建名字为“你好”的数据库，并使用T-SQL语句删除该数据库。 三、分离附加数据库

1．分离Sale数据库，并把E盘的Sale_Data．mdf文件和Sale_log．ldf文件复制到D盘； 2．把D盘的Sale数据库附加到SSMS中。 四、导入导出数据表

1．把XK数据库附加到SSMS中；

2．把XK数据库中的Class表导出到Excel中； 3．把刚才导出的Class表导入到Sale数据库中。 五、数据库的备份还原

1．把Sale数据库备份到E盘； 2．还原Sale数据库。

08计应2班 时间：2010年9月15日星期三 8:00-9：40 08计应1班 时间：2010年9月17日星期五 8:00-9：40 08计应3班 时间：2010年9月20日星期一 14:30-16:10

实验四 数据库表的创建及管理（第四章）

实验内容：数据表的创建及管理

实验目的：掌握概念模型转换为数据模型的方法和表的创建 实验要求：

一、把图书借阅系统的E-R图转换为二维关系表，并进行规范化处理

6

二、把上述二维关系表在图书借阅数据库中实现 1.使用SSMS和T-SQL语句创建图书借阅系统数据库 2.分别使用SSMS和T-SQL语句创建如上二维关系表 要求自己分析数据类型，长度，是否为空 三、对每张表设置主键

四、分离图书借阅系统，上传至ftp：//222.21.113.155 图书借阅系统参考表名和列名：

读者表（读者编号，读者姓名，类型编号，已借数量）

图书表（图书编号，图书名称，作者，出版社，出版日期，定价，ISBN） 读者类型（类型编号，类型名，限制数量，限制时间）

借书表（读者编号，图书编号，借书日期，还书日期，应还日期）

参考：Library

读者表（读者编号 char（10） not null，

读者姓名 char（8） not null， 类型编号 char（2） not null， 已借数量 int not null）

图书表（图书编号 char（9） not null，

图书名称 nvarchar（42） not null， 作者 nvarchar（20） not null， 出版社 nvarchar（28） not null， 出版日期 datetime not null， 定价 decimal（7,2） not null ISBN char（13） not null）

读者类型（类型编号 char(2) not null， 类型名 char（8） null， 限制数量 int null，

限制时间 int null）

7

借书表（读者编号 char（10） not null，

图书编号 char（9） not null， 借书日期 datetime not null， 还书日期 datetime null， 应还日期 datetime null）

08计应1班 时间：2010年9月26日星期日 8:00-9：40 08计应3班 时间：2010年9月27日星期一 14:30-16:10

实验四 数据库表的创建及管理（第四章）

实验内容：数据表的创建及管理

实验目的：掌握概念模型转换为数据模型的方法和表的创建 实验要求：

一、把图书借阅系统的E-R图转换为二维关系表，并进行规范化处理 二、把上述二维关系表在图书借阅数据库中实现

1.使用SSMS和T-SQL语句创建图书借阅系统数据库 2.分别使用SSMS和T-SQL语句创建如上二维关系表 要求自己分析数据类型，长度，是否为空 三、对每张表设置主键

四、对图书借阅系统中的表进行一些适当的修改（分别使用SSMS和T-SQL语句）

1.把读者表的表名修改为“Reader” 2.把读者表中的“读者姓名”改为“姓名”

3.把图书表中图书名称的数据类型改为“nchar（50）” 4.在借书表中增加一个新列“姓名 char（8） not null”并删除 五、分离图书借阅系统，上传至ftp：//222.21.113.155 图书借阅系统参考表名和列名：

读者表（读者编号，读者姓名，类型编号，已借数量）

图书表（图书编号，图书名称，作者，出版社，出版日期，定价，ISBN） 读者类型（类型编号，类型名，限制数量，限制时间）

借书表（读者编号，图书编号，借书日期，还书日期，应还日期）

8

08计应2班 时间：2010年9月29日星期三 8:00-9：40

实验五数据库表的管理及简单查询（第九章）

实验内容：数据表管理和简单查询

实验目的：掌握修改表的方法和select查询语句的简单使用 实验要求：

一、对图书借阅系统中的表进行一些适当的修改（分别使用SSMS和T-SQL语句） （一）到ftp://222.21.113.155（用户名为3）上下载自己做的数据库。 （二）对自己的数据库表进行以下修改：

1.把读者表的表名修改为“Reader” 2.把读者表中的“读者姓名”改为“姓名”

3.把图书表中图书名称的数据类型改为“nchar（50）” 4.在借书表中增加一个新列“姓名 char（8） not null”并删除 二、使用Select语句进行简单查询（查询语句保留好） （1）附加XK数据库；

（2）对XK数据库表中的数据进行查询：

1. 从课程表中检索所有的信息；

2. 从学生表中检索所有学生的姓名和学号；

3. 检索课程表（Course）中的课程名称，教师，上课时间，要求用三种方式为列起别名，改成中文名称。 4. 从学生表中检索学生所在班的班级编码；

5. 从学生表中检索学生所在班的班级编码；并消除重复记录。

6. 从课程表汇总检索老师所在系的系部编码，要求消除值相同的那些行； 7. 从课程表（Course）中检索上课时间和课程分类，要求消除相同的那些行； 8. 从课程表中查找前10行课程的课程名和任课老师； 9. 从课程表中查找前10%的课程的课程名和任课老师；

10. 检索XK数据库的Course表的课程信息，要求显示报名人数与限选人数之比； 11.检索课程表中最小的报名人数、最大的报名人数以及平均报名人数，并修改列标题。

9

08计应3班 时间：2010年10月11日星期一 14:30-16:10

实验五 数据库表的管理及简单查询（第九章）

实验内容：使用Select语句进行简单查询 实验目的：Select查询语句的简单使用 实验要求：

一、使用Select语句进行简单查询（查询语句保留好） （1）附加XK数据库；

（2）对XK数据库表中的数据进行以下查询： 1. 从课程表中检索所有的信息；

2. 从学生表中检索所有学生的姓名和学号；

3. 检索课程表（Course）中的课程名称，教师，上课时间，要求用三种方式为列起别名，改成中文名称。 4. 从学生表中检索学生所在班的班级编码；

5. 从学生表中检索学生所在班的班级编码；并消除重复记录。

6. 从课程表汇总检索老师所在系的系部编码，要求消除值相同的那些行； 7. 从课程表（Course）中检索上课时间和课程分类，要求消除相同的那些行； 8. 从课程表中查找前10行课程的课程名和任课老师； 9. 从课程表中查找前10%的课程的课程名和任课老师；

10. 检索XK数据库的Course表的课程信息，要求显示报名人数与限选人数之比； 11.检索课程表中最小的报名人数、最大的报名人数以及平均报名人数，并修改列标题。 12.检索课程类别为‘信息技术’的课程信息。 13.检索报名人数不少于15人的课程信息。

14.检索报名人数少于25并且多于15人的课程信息。 15.检索课程编码为‘001’‘002’‘010’的课程信息。 16. 检索姓为林、李、王的教师所带课程的信息。

17.从student表中检索陈姓学生的信息。

18.从student表中检索名字中含有“丽”字的学生的信息。 19.从student表中检索不姓“李”学生的信息。

10

20.从student表中检索第2个字为“丽”的学生的信息。

21.检索课程号不为‘004’,‘007’,‘013’的课程号和课程名称。

08计应2班 时间：2010年10月13日星期三 8:00-9：40 08计应1班 时间：2010年10月15日星期五 8:00-9：40

参考答案：

1. 从学生表中检索所有的信息；（分别使用*和列名） Select * from student go

Select stuno,stuname,classno,pwd from student go 2. 从学生表中检索所有学生的姓名和学号； Select stuno,stuname from student go

3. 检索课程表（Course）中的课程名称，教师，上课时间，要求用三种方式为列起别名，改成中文名称。 Select ‘课程名称’=couname,’ 教师’=teacher,’上课时间’=schooltime From course Go

Select couname ‘课程名称’, teacher ’ 教师’ , schooltime ’上课时间’ From course go

Select couname AS‘课程名称’, teacher AS ‘教师’ , schooltime AS’上课时间’ From course go

4. 从学生表中检索学生所在班的班级编码； Select classno from student go

5. 从学生表中检索学生所在班的班级编码；并消除重复记录。 Select distinct classno from student go

11

6. 从课程表汇总检索老师所在系的系部编码，要求消除值相同的那些行； Select distinct departno from course go

7. 从课程表（Course）中检索上课时间和课程分类，要求消除相同的那些行； Select distinct kind,schooltime from course go 8. 从课程表中查找前10行课程的课程名和任课老师； Select top 10 couname,teacher from course go 9. 从课程表中查找前10%的课程的课程名和任课老师； Select top 10 percent couname,teacher from course go

10. 检索XK数据库的Course表的课程信息，要求显示报名人数与限选人数之比； SELECT *,WillNum/LimitNum FROM Course Go

11.检索课程表中最小的报名人数、最大的报名人数以及平均报名人数，并修改列标题。

SELECT '最小的报名人数'=MIN(WillNum),'最大的报名人数'=MAX(WillNum),'平均报名人数'=AVG(WillNum) FROM Course

12.检索课程类别为‘信息技术’的课程信息。 Select * from course where kind=‘信息技术’ 13.检索报名人数不少于15人的课程信息。 Select * from course where willnum>=15 14.检索报名人数少于25并且多于15人的课程信息。

SELECT * FROM Course WHERE WillNum>15 AND WillNum<25 15.检索课程编码为‘001’‘002’‘010’的课程信息。

SELECT * FROM Course

WHERE CouNo='001' OR CouNo='002' OR CouNo='013' 或者SELECT * FROM Course

WHERE CouNo IN('001','002','013') 16. 检索姓为林、李、王的教师所带课程的信息。

SELECT * from course where teacher like'[林李王]%' 17.从student表中检索陈姓学生的信息。

Select * from student where stuname like ?陈%?

12

18.从student表中检索名字中含有“丽”字的学生的信息。

Select * from student where stuname like ?%丽%? 19.从student表中检索不姓“李”学生的信息。

Select * from student where stuname like ?[^李]%? 或 Select * from student where stuname not like ?[李]%? 20.从student表中检索第2个字为“丽”的学生的信息。

Select * from student where stuname like ?_[丽]%?

21.检索课程号不为‘004’,‘007’,‘013’的课程号和课程名称。

SELECT * FROM Course

WHERE CouNo not IN('004','007','013')

实验六 数据库表的管理及简单查询（第九章）

实验内容：使用Select语句进行简单查询 实验目的：Select查询语句的简单使用 实验要求：

一、使用Select语句进行简单查询（查询语句保留好） （1）附加XK数据库；

（2）对XK数据库表中的数据进行查询：

1.从course表中检索报名人数介于21与27之间（包括21和27）的课程的信息。

2.检索报名人数与限选人数的差额（报名人数—限选人数）大于20的课程名、教师、报名人数与限选人数的差额。

3.检索姓刘，并且第二个字不是丽、志的学生姓名。

4.检索班级号为20000002的姓李学生的学号、姓名，并修改列标题。

5.从课程表中检索前5行的所有课程信息，要求课程分类名称不包含技术两个字。

13

6.检索限选人数多于20人或者小于15人的课程名称和教师，并修改列标题。 7.检索课程类别为‘信息技术’的课程信息。 8.检索报名人数不少于15人的课程信息。

9.检索报名人数少于25并且多于15人的课程信息。 10.检索课程编码为‘001’‘002’‘010’的课程信息. 11.检索课程号不是008，010，012的课程的信息。 12. 检索姓为林、李、王的教师所带课程的信息。

13.从student表中检索陈姓学生的信息。

14.从student表中检索名字中含有“丽”字的学生的信息。 15.从student表中检索不姓“李”学生的信息。 16.从student表中检索第2个字为“丽”的学生的信息。

08计应3班 时间：2010年10月18日星期一 14:30-16:10

参考答案：

1.从course表中检索报名人数介于21与27之间（包括21和27）的课程的信息。 Select * from course where willnum between 21and 27

2.检索报名人数与限选人数的差额（报名人数—限选人数）大于20的课程名、教师、报名人数与限选人数的差额。

Select couname,teacher, 报名人数与限选人数的差额=willnum-limitnum from course wherer willnum-limitnum>20

3.检索姓刘，并且第二个字不是丽、志的学生姓名。 Select stuname from student where stuname like ?_[^丽志]%?

4.检索班级号为20000002的姓李学生的学号、姓名，并修改列标题。

14

Select stuno as 学号，stuname as 姓名 from student where classno=20000002 and stuname like ?李%? 5.从课程表中检索前5行的所有课程信息，要求课程分类名称不包含技术两个字。 Select top 5 * from course where kind like ?%[^技术]%?

6.检索限选人数多于20人或者小于15人的课程名称和教师，并修改列标题。

Select 课程名称=couname, 教师=teacher from course where limitnum not between 15 and 20 7.检索课程类别为‘信息技术’的课程信息。 Select * from course where kind=‘信息技术’ 8.检索报名人数不少于15人的课程信息。 Select * from course where willnum>=15 9.检索报名人数少于25并且多于15人的课程信息。 Select * from course where willnum>15 and willnum<25 10.检索课程编码为‘001’‘002’‘010’的课程信息.

SELECT * FROM Course

WHERE CouNo='001' OR CouNo='002' OR CouNo='013' 或者SELECT * FROM Course

WHERE CouNo IN('001','002','013') 11.检索课程号不是008，010，012的课程的信息。

SELECT * FROM Course

WHERE CouNo not IN('008','010','012') 12. 检索姓为林、李、王的教师所带课程的信息。

SELECT * from course where teacher like'[林李王]%' 13.从student表中检索陈姓学生的信息。

Select * from student where stuname like ?陈%?

14.从student表中检索名字中含有“丽”字的学生的信息。

Select * from student where stuname like ?%丽%? 15.从student表中检索不姓“李”学生的信息。

Select * from student where stuname like ?[^李]%?

15

或 Select * from student where stuname not like ?[李]%? 16.从student表中检索第2个字为“丽”的学生的信息。

Select * from student where stuname like ?_[丽]%?

实验六 数据库表的管理及简单查询（第九章）

实验内容：使用Select语句进行简单查询 实验目的：Select查询语句的简单使用 实验要求：

一、使用Select语句进行简单查询（查询语句保留好） （1）附加XK数据库；

（2）对XK数据库表中的数据进行如下查询：

1. 检索课程号不是008，010，012的课程的信息，并按报名人数降序排列，当报名人数相同时，按照课程名

升序排列。

2. 检索报名人数比限制选课人数多一倍以及一倍以上的课程信息，要求显示课程名称、报名人数和限制选课

人数之比，并按该比例的降序排列。

3. 从课程表中检索前5行的所有课程信息，要求课程分类名称不包含技术两个字，并按照课程名称降序排列。 4. 从课程表中查询报名人数最多的5门课的信息。

5. 检索限选人数多于20人或者小于15人的课程名称和教师，并修改列标题，检索结果按课程名称升序排列。 6. 从course表中查询每类课程的平均报名人数。 7. 按课程分类统计每类课程的最少报名人数

8. 按教师的系部编码统计每个系部所开课程的平均报名人数。 9. 从学生选课表中查询各个课程号及相应的选课人数。 10. 检索“工程技术”类的课程的最大报名人数和最小报名人数。 11. 检索“信息技术”类课程的平均报名人数。

12. 检索平均报名人数大于30人的课程分类和每类平均报名人数。

08计应2班 时间：2010年10月20日星期三 8:00-9：40 08计应1班 时间：2010年10月22日星期五 8:00-9：40

16

参考答案：

1. 检索课程号不是008，010，012的课程的信息，并按报名人数降序排列，当报名人数相同时，按照课程名

升序排列。 Select * From course

Where couno not in(008,010,012) Order by willnum desc,couname

2. 检索报名人数比限制选课人数多一倍以及一倍以上的课程信息，要求显示课程名称、报名人数和限制选课

人数之比，并按该比例的降序排列。 Select couname,willnum/limitnum From course

Where willnum/limitnum>=2 Order by willnum/limitnum desc

3. 从课程表中检索前5行的所有课程信息，要求课程分类名称不包含技术两个字，并按照课程名称降序排列。

Select top 5 * From course

Where couname not like ?%技术%? Order by couname desc

4. 从课程表中查询报名人数最多的5门课的信息。

SELECT TOP 5 * FROM Course

ORDER BY WillNum DESC

5. 检索限选人数多于20人或者小于15人的课程名称和教师，并修改列标题，检索结果按课程名称升序排列。

Select 课程名称=couname, 教师=teacher From course

Where limitnum>20 or willnum<15 Order by couname

6. 从course表中查询每类课程的平均报名人数。

Select kind,avg(willnum) From course

17

Group by kind

7. 按课程分类统计每类课程的最少报名人数

Select Kind, MIN(willnum) From Course Group By Kind

8. 按教师的系部编码统计每个系部所开课程的平均报名人数。

Select departno,avg(willnum) From course Group by departno

9. 从学生选课表中查询各个课程号及相应的选课人数。

SELECT 课程号=CouNo,选课人数=COUNT(CouNo) FROM StuCou Group By CouNo

10. 检索“工程技术”类的课程的最大报名人数和最小报名人数。

Select kind，max(willnum),min(willnum) From course

Where kind=’工程技术’ Group by kind 或

Select kind，max(willnum),min(willnum) From course Group by kind

Having kind=’工程技术’

11. 检索“信息技术”类课程的平均报名人数。

SELECT Kind AS’课程分类’,AVG(WillNum) AS ‘平均报名人数’ FROM Course

WHERE Kind='信息技术' GROUP BY Kind

12. 检索平均报名人数大于30人的课程分类和每类平均报名人数。

Select kind,avg(willnum)

18

From course Group by kind

Having avg(willnum)>30

实验七 数据库表的管理及简单查询（第九章）

实验内容：使用Select语句进行简单查询 实验目的：Select查询语句的简单使用 实验要求：

一、使用Select语句进行简单查询（查询语句保留好） （1）附加XK数据库；

（2）对XK数据库表中的数据进行如下查询：

1. 检索课程号不是008，010，012的课程的信息，并按报名人数降序排列，当报名人数相同时，按照课程名

升序排列。

2. 检索报名人数比限制选课人数多一倍以及一倍以上的课程信息，要求显示课程名称、报名人数和限制选课

人数之比，并按该比例的降序排列。

3. 从课程表中检索前5行的所有课程信息，要求课程分类名称不包含技术两个字，并按照课程名称降序排列。 4. 从课程表中查询报名人数最多的5门课的信息。

5. 检索限选人数多于20人或者小于15人的课程名称和教师，并修改列标题，检索结果按课程名称升序排列。 6. 从course表中查询每类课程的平均报名人数。 7. 按课程分类统计每类课程的最少报名人数

8. 按教师的系部编码统计每个系部所开课程的平均报名人数。 9. 从学生选课表中查询各个课程号及相应的选课人数。 10. 检索“工程技术”类的课程的最大报名人数和最小报名人数。 11. 检索“信息技术”类课程的平均报名人数。

12. 检索平均报名人数大于30人的课程分类和每类平均报名人数。

08计应3班 时间：2010年10月25日星期一 14:30-16:10

19

实验七 数据库表的管理及简单查询（第九章）

实验内容：使用Select语句进行高级查询 实验目的：Select查询语句的高级使用 实验要求：

一、使用Select语句进行高级查询（查询语句保留好） （1）附加XK数据库；

（2）对XK数据库表中的数据进行如下查询：

1. 从course表中检索分类为“信息技术”的课程的名称，课程分类和报名人数，并计算其平均报名人数，要求平均报名人数作为附加新行放在检索结果中。 2. 检索课程表的所有信息，并计算最大报名人数。

3. 从course表中检索所有课程的名称，报名人数及平均报名人数。

4. 检索系部信息以及系部所开设的课程的课程名、课程分类、教师、上课时间。

5. 检索学生的学号、姓名以及学生的选课的课程名称、课程号、教师情况，并消除相同列。 6. 检索报名人数多于20人或者小于15人的课程的信息以及系部信息，并消除相同列。 7. 检索各系部信息以及系部的班级信息，消除相同列，并为表起别名。 8. 检索“计算机应用工程系”周二上课的课程号、课程名称和教师名。 9. 检索班级名称为“00多媒体”的班级中的学生学号、姓名。 10.检索没有学生选报的课程所在系部名称、课程号和课程名。 11.检索学生信息以及学生的选课的课程情况。

08计应2班 时间：2010年10月27日星期三 8:00-9：40 08计应1班 时间：2010年10月29日星期五 8:00-9：40

20

参考答案：

1. 从course表中检索分类为“信息技术”的课程的名称，课程分类和报名人数，并计算其平均报名人数，要求平均报名人数作为附加新行放在检索结果中。

Select couname,kind,willnum From course

Where kind=’信息技术’ Compute avg(willnum)

2. 检索课程表的所有信息，并计算最大报名人数。

Select * From course

Coppute max(willnum)

3. 从course表中检索所有课程的名称，报名人数及平均报名人数。

Select couname,willnum From course

Compute avg(willnum)

4、检索系部信息以及系部所开设的课程的课程名、课程分类、教师、上课时间。 select department.*, couname, kind, teacher, schooltime from department,course

where department.departno=course.departno

5、检索学生的学号、姓名以及学生的选课的课程名称、课程号、教师情况，并消除相同列。 Select student.stuno, stuname,course.couno, couname, teacher from student,stucou,course

where student.stuno=stucou.stuno and stucou.couno=course.couno

6、检索报名人数多于20人或者小于15人的课程的信息以及系部信息，并修改列标题，并消除相同列。 select department.*,course.couno,couname,kind,credit from department,course

where department.departno=course.departno and (willnum>20 or willnum<15)

7、检索各系部信息以及系部的班级信息，消除相同列，并为表起别名 select d.*, classno,classname

21

from department d,class c where d.departno=c.departno

8、检索“计算机应用工程系”周二上课的课程号、课程名称和教师名。 select couno,couname,teacher from department,course

where department.departno=course.departno and departname='计算机应用工程系' and schooltime like '周二%' 9、检索班级名称为“00多媒体”的班级中的学生学号、姓名。 select stuno,stuname from class,student

where class.classno=student.classno and classname='00多媒体' 10、检索没有学生选报的课程所在系部名称、课程号和课程名。 select couno,couname,departname from department,course

where department.departno=course.departno and willnum = 0 11、检索学生信息以及学生所选课程的情况。 Select student.*，course.* from student,stucou,course where student.stuno=stucou.stuno and stucou.couno=course.couno

实验八 数据库表的管理及简单查询（第九章）

实验内容：使用Select语句进行简单查询 实验目的：Select查询语句的简单使用 实验要求：

一、使用Select语句进行高级查询（查询语句保留好） （1）附加XK数据库；

（2）对XK数据库表中的数据进行如下查询：

1. 从course表中检索分类为“信息技术”的课程的名称，课程分类和报名人数，并计算其平均报名人数，要

22

求平均报名人数作为附加新行放在检索结果中。 2. 检索课程表的所有信息，并计算最大报名人数。

3. 从course表中检索所有课程的名称，报名人数及平均报名人数。

4. 检索系部信息以及系部所开设的课程的课程名、课程分类、教师、上课时间。

5. 检索学生的学号、姓名以及学生的选课的课程名称、课程号、教师情况，并消除相同列。 6. 检索报名人数多于20人或者小于15人的课程的信息以及系部信息，并消除相同列。 7. 检索各系部信息以及系部的班级信息，消除相同列，并为表起别名。 8. 检索“计算机应用工程系”周二上课的课程号、课程名称和教师名。 9. 检索班级名称为“00多媒体”的班级中的学生学号、姓名。 10.检索没有学生选报的课程所在系部名称、课程号和课程名。 11.检索学生信息以及学生的选课的课程情况。

08计应3班 时间：2010年11月1日星期一 14:30-16:10

实验八 数据库表的管理及简单查询（第九章第四章）

实验内容：使用Select语句进行简单查询和向表中插入数据 实验目的：Select查询语句的简单使用和向表中插入数据的方法 实验要求：

一、使用Select语句进行高级查询（查询语句保留好） （1）附加XK数据库；

（2）对XK数据库表中的数据进行如下查询： 1.检索报名人数小于平均报名人数的课程信息。 2.检索已经报了选修课的学生的学号和姓名。 二、向表中输入数据

1.向班级表中插入3行数据，分别为

（班级编码：20020007，系部编码：03，班级名称：03旅游管理）， （班级编码：20020008，系部编码：03，班级名称：04旅游管理），

（班级编码：20020009，系部编码：03，班级名称：05旅游管理），分别用三种不同的方法实现。

23

2.向课程表中任意添加一门课程的信息，方法不限。 3.使用select子句将class表中的数据插入到Newclass表中。

4.将course表中由”计算机应用工程系”开设的课程插入到Newcourse表中。（使用select into） 5.将学号为00000020的学生的姓名改为庄严。

6.将已经选修了课程且学号为0000005的同学的密码改为12345678。

08计应2班 时间：2010年11月3日星期三 8:00-9：40 参考答案：

1.检索报名人数小于平均报名人数的课程信息。

Select * From course

Where willnum<(select avg(willnum) from course) 2.检索已经报了选修课的学生的学号和姓名。

SELECT StuNo，StuName FROM Student

Where EXISTS（select stuno from stucou where stuno=student.stuno） 或

SELECT StuNo，StuName FROM Student

Where StuNo IN（select stuno from stucou ）

二、向表中输入数据

1.向班级表中插入3行数据，分别为

（班级编码：20020007，系部编码：03，班级名称：03旅游管理）， （班级编码：20020008，系部编码：03，班级名称：04旅游管理），

（班级编码：20020009，系部编码：03，班级名称：05旅游管理），分别用三种不同的方法实现。 Insert class

Values（?20020007?， ?03?， ?03旅游管理?） Insert class（classno,departno,classname） Values(?20020008?， ?03?， ?04旅游管理?） Insert class（classno, classname ,departno）

24

Values(?20020009?， ?05旅游管理?，? 03?） 2.向课程表中任意添加一门课程的信息，方法不限。 3.使用select子句将class表中的数据插入到Newclass表中。 Create table newclass (newclassno char(8) not null, Newdepartno char(2) not null, Newclassname char(20) not null)

Insert newclass Select * From class

4.将course表中由”计算机应用工程系”开设的课程插入到Newcourse表中。（使用select into） Select a.* Into newcourse

From course a,department b

Where a.departno=b.departno and departname=? 计算机应用工程系? 5.将学号为00000020的学生的姓名改为庄严。 Update student Set stuname=?庄严? Where stuno=? 00000020?

6.将已经选修了课程且学号为0000005的同学的密码改为12345678。 Update student Set pwd=?123456789?

Where stuno in (select stuno from stucou) and stuno=? 0000005?

实验八 数据库表的管理及简单查询（第九章第四章）

实验内容：使用Select语句进行简单查询和向表中插入数据 实验目的：Select查询语句的简单使用和向表中插入数据的方法

25

实验要求：

一、使用Select语句进行高级查询（查询语句保留好） （1）附加XK数据库；

（2）对XK数据库表中的数据进行如下查询： 1.检索报名人数小于平均报名人数的课程信息。 2.检索已经报了选修课的学生的学号和姓名。 二、向表中输入数据

1.向班级表中插入3行数据，分别为

（班级编码：20020007，系部编码：03，班级名称：03旅游管理）， （班级编码：20020008，系部编码：03，班级名称：04旅游管理），

（班级编码：20020009，系部编码：03，班级名称：05旅游管理），分别用三种不同的方法实现。 2.向课程表中任意添加一门课程的信息，方法不限。 3.使用select子句将class表中的数据插入到Newclass表中。

4.将course表中由”计算机应用工程系”开设的课程插入到Newcourse表中。（使用select into）

08计应1班 时间：2010年11月5日星期五 8:00-9：40 08计应3班 时间：2010年11月8日星期一 14:00-15：40

实验九 表的数据操作及常用函数的使用

实验内容：修改、删除表中数据及常用函数的使用

实验目的：掌握修改、删除表中数据的方法及使用常用函数取得信息的方法 实验要求： 附加XK数据库：

1.学号为“00000005”的同学因故取消课程号为“017”的选修课，在StuCou表中删除此行。 2.学号为“00000003”的同学因故取消“JAVA技术的开发应用”选修课，在表中进行删除处理。 3.在student表中删除学号为00000001的学生信息。 4.计算stucou表的总行数. 5.计算Course表中课程的门数。 6.根据本人身份证号，返回如下结果：

26

08计应2班 时间：2010年11月10日星期三 8:00-9：40

参考答案： 1.

学号为“00000005”的同学因故取消课程号为“017”的选修课，在StuCou表中删除此行。 DELETE StuCou

WHERE StuNo='00000005' AND CouNo='017' UPDATE Course

Set WillNum=WillNum-1 Where CouNo='017'

2. 学号为“00000003”的同学因故取消“JAVA技术的开发应用”选修课，在表中进行删除处理。

DELETE StuCou

WHERE StuNo=‘00000003’ AND

CouNo=（select couno from course where couname=‘JAVA技术的开发应用‘） UPDATE Course

Set WillNum=WillNum-1

Where CouName=‘JAVA技术的开发应用‘

3. 在student表中删除学号为00000001的学生信息。

update course

set willnum=willnum-1

where couno in(select couno from stucou where stuno='00000001') delete stucou

where stuno='00000001' delete student

where stuno='00000001'

4.计算stucou表的总行数.

select count(couno) from stucou go

27

5.计算Course表中课程的门数。

select count(couno) from course go

6.根据本人身份证号，返回如下结果：

SELECT '张三' as '姓名',

SUBSTRING('410881198010012031',7,4),'年', SUBSTRING('410881198010012031',11,2),'月', SUBSTRING('410881198010012031',13,2),'日'

实验九 表的数据操作及变量的使用

实验内容：修改、删除表中数据及变量的使用

实验目的：掌握修改、删除表中数据的方法及变量的使用方法 实验要求： 附加XK数据库：

1.将学号为00000020的学生的姓名改为庄严。

2.将已经选修了课程且学号为0000005的同学的密码改为12345678。

3.学号为“00000005”的同学因故取消课程号为“017”的选修课，在StuCou表中删除此行。 4.学号为“00000003”的同学因故取消“JAVA技术的开发应用”选修课，在表中进行删除处理。 5.在student表中删除学号为00000001的学生信息。 6.计算stucou表的总行数. 7.计算Course表中课程的门数。

08计应1班 时间：2010年11月12日星期五 8:00-9：40 08计应3班 时间：2010年11月15日星期一 14:00-15：40

参考答案：

1.将学号为00000020的学生的姓名改为庄严。

28

Update student Set stuname=?庄严? Where stuno=? 00000020?

2.将已经选修了课程且学号为0000005的同学的密码改为12345678。

Update student Set pwd=?123456789?

3.

Where stuno in (select stuno from stucou) and stuno=? 0000005?

学号为“00000005”的同学因故取消课程号为“017”的选修课，在StuCou表中删除此行。 DELETE StuCou

WHERE StuNo='00000005' AND CouNo='017' UPDATE Course

Set WillNum=WillNum-1 Where CouNo='017'

4. 学号为“00000003”的同学因故取消“JAVA技术的开发应用”选修课，在表中进行删除处理。

DELETE StuCou

WHERE StuNo=‘00000003’ AND

CouNo=（select couno from course where couname=‘JAVA技术的开发应用‘） UPDATE Course

Set WillNum=WillNum-1

Where CouName=‘JAVA技术的开发应用‘

5. 在student表中删除学号为00000001的学生信息。

update course

set willnum=willnum-1

where couno in(select couno from stucou where stuno='00000001') delete stucou

where stuno='00000001' delete student

where stuno='00000001'

6.计算stucou表的总行数.

select count(couno) from stucou go

7.计算Course表中课程的门数。

select count(couno) from course go

29

实验十 函数和流控语句的使用以及约束的创建

实验内容：常用函数的使用，流控语句的使用以及约束的创建 实验目的：常用函数的使用，流控语句的使用以及约束的创建 实验要求： 附加XK数据库：

1.给出系统当前的日期和时间。 2.使用日期函数计算自己的年龄。 3.使用流控语句计算1+2+3+4+?+500=？

4.在XK数据库中显示班级编码、班级名称和系部名称（当系部编码为01时，显示系部名称为“计算机应用工程系”；当系部编码为02时，显示系部名称为“建筑工程系”；当系部编码为03时，显示系部名称为“旅游系”），要求：1.使用CASE语句； 2.不使用CASE语句两种方法实现。

5.使用SSMS为Student、StuCou、Department表创建主键，使用SQL语句为Class、Course表创建主键。 6. 使用SSMS为Student、StuCou表创建外键，使用SQL语句为Class、Course表创建外键。 08计应2班 时间：2010年11月17日星期三 8:00-9：40 08计应1班 时间：2010年11月19日星期五 8:00-9：40 08计应3班 时间：2010年11月22日星期一 14:00-15：40

参考答案：

1.给出系统当前的日期和时间。

Select getdate（）

2.使用日期函数计算自己的年龄。

Select datediff（yy,?1990-1-2?,getdate()） 3.使用流控语句计算1+2+3+4+?+500=？

DECLARE @i int,@sum int SELECT @i=1,@sum=0 WHILE @i<=500 BEGIN

SELECT @sum=@sum+@i SELECT @i=@i+1 END GO

SELECT @sum

4.在XK数据库中显示班级编码、班级名称和系部名称（当系部编码为01时，显示系部名称为“计算机应用工

30

程系”；当系部编码为02时，显示系部名称为“建筑工程系”；当系部编码为03时，显示系部名称为“旅游系”），要求：1.使用CASE语句； 2.不使用CASE语句两种方法实现。

(1) select case departno

when '01' then '计算机应用工程系' when '02' then '建筑工程系' when '03' then '旅游系' end,

classno,classname

from class go

(2) select classno, classname, departname From class, department

Where class.departno=department.departno

5.使用SSMS为Student、StuCou、Department表创建主键，使用SQL语句为Class、Course表创建主键。 (1)ALTER TABLE class

ADD CONSTRAINT PK_classno

PRIMARY KEY (classno)

(2)ALTER TABLE Course

ADD CONSTRAINT PK_Couno

PRIMARY KEY (Couno)

6. 使用SSMS为Student、StuCou表创建外键，使用SQL语句为Class、Course表创建外键。 (1) ALTER TABLE Class

ADD CONSTRAINT FK_departno FOREIGN KEY (departno)

REFERENCES department(departno) (2) ALTER TABLE Course

ADD CONSTRAINT FK_depart FOREIGN KEY (departno)

REFERENCES department(departno)

实验十一 约束、规则、默认、视图的创建和维护

实验内容：创建、修改约束、规则、默认和视图

实验目的：掌握创建、修改约束、规则、默认和视图的方法 实验要求： 附加XK数据库：

1.使用SQL语句为student表创建名为UK_Pwd的基于Pwd列的唯一约束：

2.使用SSMS为Course表创建名为CK_couno的Check约束，限制Couno列中只允许3位数字，并且三位数字

31

都不为0；

3.使用SQL语句为stucou表创建名为DF_Stucou_State的Default约束，该约束使state列的值默认为“报名”。 4.使用T-SQL语句在Xk数据库中创建名为CreditRule的规则（大于等于1，小于等于5），并将其绑定到Course表的Credit列。

5.创建一个名为UnsureDefault的默认值，默认值为? 待定?，然后绑定到表Course的列Teacher。 6.创建视图v_Class，只显示Class表中系部编码为01的系部的班级信息。 7.创建视图v_StuCouSub，只显示StuCou表中的StuNo列和CouNo列。 8.创建视图v_StuCou，显示学生的学号、姓名和所选的课程名称。

9.创建视图v_CouByCmpt，显示计算机应用工程系承担的选修课程的信息（课程名称、授课老师、上课时间）。 10.创建视图v_CouBydep，显示各系部开设选修课的门数。

11.对v_StuCouSub进行修改，使其显示学生的学号、姓名、课程名称和教师 12.从视图v_StuCou中查找学号为‘00000001’的学生所选的课程名称 13.删除视图v_Class

08计应2班 时间：2010年11月24日星期三 8:00-9：40 08计应1班 时间：2010年11月26日星期五 8:00-9：40 08计应3班 时间：2010年11月29日星期一 14:00-15：40

参考答案：

1.使用SQL语句为student表创建名为UK_Pwd的基于Pwd列的唯一约束：

Alter Table Student ADD Constraint

UK_Pwd UNIQUE (Pwd)

2.使用SSMS为Course表创建名为CK_couno的Check约束，限制Couno列中只允许3位数字，并且三位数字都不为0； ALTER TABLE Student ADD CONSTRAINT CK_StuNo

CHECK (StuNo like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' AND StuNo<>'00000000')

3.使用SQL语句为stucou表创建名为DF_Stucou_State的Default约束，该约束使state列的值默认为“报名”。

32

ALTER TABLE StuCou

ADD CONSTRAINT DF_StuCou_State DEFAULT ('报名') FOR State

4.使用T-SQL语句在Xk数据库中创建名为CreditRule的规则（大于等于1，小于等于5），并将其绑定到Course表的Credit列。

CREATE RULE CreditRule AS @x>=1 AND @x<=5 EXEC sp_bindrule CreditRule, ‘Course.Credit’

5.创建一个名为UnsureDefault的默认值，默认值为? 待定?，然后绑定到表Course的列Teacher。 CREATE DEFAULT UnsureDefault AS ‘待定’ EXEC sp_bindefault UnsureDefault, ‘Course.Teacher’

6.创建视图v_Class，只显示Class表中系部编码为01的系部的班级信息。 Create view v_Class As Select * From class

Where departno=?01?

7.创建视图v_StuCouSub，只显示StuCou表中的StuNo列和CouNo列。 Create view v_StuCouSub As

Select StuNo,CouNo From StuCou

8.创建视图v_StuCou，显示学生的学号、姓名和所选的课程名称。 Create view v_StuCou As

Select StuNo,stuname,CouName From student a, course b, StuCou c Where a.stuno=c.stuno and b.couno=c.couno

9.创建视图v_CouByCmpt，显示计算机应用工程系承担的选修课程的信息（课程名称、授课老师、上课时间）。 Create view v_CouByCmpt

33

As

Select CouName, teacher, schooltime From course a ,department b

Where a.departno=b. departno and departname=?计算机应用工程系? 10.创建视图v_CouBydep，显示各系部开设选修课的门数。 Create view v_CouBydep As

Select departno,count(couno) 门数 From course Group by departno

11.对v_StuCouSub进行修改，使其显示学生的学号、姓名、课程名称和教师 Alter view v_StuCouSub As

Select StuNo,stuname,CouName,teacher From student a, course b, StuCou c Where a.stuno=c.stuno and b.couno=c.couno

12.从视图v_StuCou中查找学号为‘00000001’的学生所选的课程名称 Select couname From v_StuCou Where stuno=?00000001? 13.删除视图v_Class Drop view v_class

34

本文来源：https://www.bwwdw.com/article/dazp.html