实验一 大型数据库的基本操作

一、实验目的

1. 熟悉大型数据库实验环境，以MS SQL SERVER为例。

2. 掌握MS SQL SERVER的企业管理器的用法。

3. 能够根据给定的设计要求，设计数据库和数据库表结构。

4. 能够使用MS SQL SERVER的企业管理器完成数据库创建和表创建等操作。

5. 能够往表中添加数据记录。

二、实验内容

（1）创建数据库

1． 数据库名字为：abc；

2． 此数据库包含两个数据文件，其中主数据文件的逻辑文件名为：Data1,物理文件名为Data1_data.mdf，初始大小为3MB，存放在新建目录下，增长方式为自动增长，每次增加1MB，最多增长到10MB。辅助数据文件的逻辑文件名为：Data2，物理文件名为Data2_data.ndf，初始大小为2MB，同主数据文件存放在相同的位置，不自动增长。

3． 日志文件的逻辑文件名为：log1,物理文件名为log1_log.ldf，初始大小为2MB，同主数据文件存放在相同的位置，增长方式为自动增长，每次增加15%。

（2） 创建表

为描述销售人员对产品的销售情况，定义了三张表：销售人员表、产品表和销售情况表，分别用于描述销售人员的基本信息、产品的基本信息和销售人员与产品之间的销售情况。写出在 “abc”数据库中定义这三张表的语句并创建这三张表，（请将语句以文件形式保存下来，文件名为“考生名.sql”，存放在新建的目录下。可以将下述所有的SQL语句均保存到此文件中，在文件中要注明题号），三张表的结构为：

1． 销售人员表（XSRYB）包括如下字段：

职工号（ZGH）：普通编码定长字符型，6位长，主码

姓名（XM）：普通编码定长字符型，10位长，非空

年龄（NL）：整型，取值范围为20 ~ 60，允许空

地区（DQ）：普通编码可变长字符型，10位长，允许空

邮政编码（YZhBM）：普通编码定长字符型，6位长，每一位必须是数字，允许空

2． 产品表（CPB）包括如下字段：

产品号（CPH）：普通编码定长字符型，6位长，主码

产品名（CPM）：普通编码可变长字符型，20位长，非空

生产厂家（SCCJ）：普通编码定长字符型，10位长，非空

价格（JG）：整型，大于0，允许空

生产日期（SCRQ）: 小日期时间型（SmallDatetime），允许空，默认为系统当前日期

3． 销售情况表（XSQKB）包括如下字段：

职工号（ZGH）：普通编码定长字符型，6位长，非空

产品号（CPH）：普通编码定长字符型，6位长，非空

销售日期（XSRQ）：小日期时间型，非空

销售数量（XSSL）：整型，允许空

其中：（职工号，产品号，销售日期）为主码

职工号为引用销售人员表的外码，产品号为引用产品表的外码。

（3） 在表中加入数据

销售人员表

'职工号','姓名','年龄','地区','邮政编码'

'G01','人员1',25,'北京','100101'

'G02','人员2',45,'北京','100108'

'G03','人员3',30,'天津','200211'

'G04','人员4',42,'天津','200322'

'G05','人员5',50,'南京','123456'

'G06','人员6',38,'上海','300310'

'G07','人员7',28,'上海','300123'

'G08','人员8',55,'上海','300456'

产品表 产品号

P02

P03

P04

P05

P06

P07 产品名 生产厂家 价格 电冰箱 计算机 音响 空调 洗衣机 笔记本 北京 北京 上海 上海 青岛 北京 生产日期 4800 2002/2/14 7900 2001/11/1 4000 2001/4/6 3800 2001/10/11 3100 2001/9/18 11230 2001/8/30

P08 微波炉 天津 1980 2002/1/20

销售情况表 职工号

G03

G03

G05

G06

G06

G03

G08 产品号 销售日期 P04 P05 P02 P07 P08 P02 P02 2001/11/20

2001/11/20 2002/5/2 2001/12/4 2002/4/4 2002/6/5 2001/12/5 销售数量 16 2 1 5 10 4 1

三、附加题

（1）在“表中加入数据”的过程，可否采用导入的方法，而不是手工输入？ 答：可以，可通过excel表格直接导入数据，只需要将其中的数字信息全部设置为文本格式即可。

（2）可否把完成的abc数据库进行备份？然后在另外一台数据库服务器上进行还原？

答：可以，只需要用到backup和restore两个语句备份所有数据/进行还原即可。

四、实验结果和代码

DROP TABLE IF EXISTS `cpb`;

CREATE TABLE `cpb` (

`CPH` varchar(255) DEFAULT NULL,

`CPM` varchar(255) NOT NULL,

`SCCJ` varchar(255) NOT NULL,

`JG` int(255) DEFAULT NULL,

`SCRQ` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

INSERT INTO `cpb` VALUES ('P02', '电冰箱', '北京', '4800', '2002-02-14 00:00:00'); INSERT INTO `cpb` VALUES ('P03', '计算机', '北京', '7900', '2001-11-01 00:00:00'); INSERT INTO `cpb` VALUES ('P04', '音响', '上海', '4000', '2001-04-06 00:00:00'); INSERT INTO `cpb` VALUES ('P05', '空调', '上海', '3800', '2001-10-11 00:00:00'); INSERT INTO `cpb` VALUES ('P06', '洗衣机', '青岛', '3100', '2001-09-18 00:00:00'); INSERT INTO `cpb` VALUES ('P07', '笔记本', '北京', '11230', '2001-08-30 00:00:00'); INSERT INTO `cpb` VALUES ('P08', '微波炉', '天津', '1980', '2002-01-20 00:00:00');

DROP TABLE IF EXISTS `xsqkb`;

CREATE TABLE `xsqkb` (

`ZGH` varchar(255) DEFAULT NULL,

`CPH` varchar(255) DEFAULT NULL,

`XSRQ` datetime DEFAULT NULL,

`XSSL` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

INSERT INTO `xsqkb` VALUES ('G03', 'P04', '2001-11-20 00:00:00', '16'); INSERT INTO `xsqkb` VALUES ('G03', 'P05', '2001-11-20 00:00:00', '2'); INSERT INTO `xsqkb` VALUES ('G05', 'P02', '2002-05-02 00:00:00', '1'); INSERT INTO `xsqkb` VALUES ('G06', 'P07', '2001-12-04 00:00:00', '5'); INSERT INTO `xsqkb` VALUES ('G06', 'P08', '2002-04-04 00:00:00', '10'); INSERT INTO `xsqkb` VALUES ('G03', 'P02', '2002-06-05 00:00:00', '4'); INSERT INTO `xsqkb` VALUES ('G08', 'P02', '2001-12-05 00:00:00', '1');

DROP TABLE IF EXISTS `xsryb`;

CREATE TABLE `xsryb` (

`ZGH` varchar(255) NOT NULL,

`XM` varchar(255) NOT NULL,

`NL` varchar(255) NOT NULL,

`DQ` varchar(255) NOT NULL,

`YZhBM` varchar(255) NOT NULL,

PRIMARY KEY (`ZGH`)

) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

INSERT INTO `xsryb` VALUES ('G01', '人员1', '25', '北京', '100101'); INSERT INTO `xsryb` VALUES ('G02', '人员2', '45', '北京', '100108'); INSERT INTO `xsryb` VALUES ('G03', '人员3', '30', '天津', '200211'); INSERT INTO `xsryb` VALUES ('G04', '人员4', '42', '天津', '200322'); INSERT INTO `xsryb` VALUES ('G05', '人员5', '50', '南京', '123456'); INSERT INTO `xsryb` VALUES ('G06', '人员6', '38', '上海', '300310'); INSERT INTO `xsryb` VALUES ('G07', '人员7', '28', '上海', '030123'); INSERT INTO `xsryb` VALUES ('G08', '人员8', '55', '上海', '300456');

五、心得体会

第一次用MySQL写SQL语句，感觉初次使用还不是很习惯，以后需要多加练习

第二篇：中南大学数据库试题

一、填空（10空，每空1分，共10分）

1．SQL语言有四类语句，分别是            、             、DML和

             。（中文及英文简写均可）

2．基本表中，“关键字”概念应该体现其值的          和          两个特征。

3．数据库系统的模式结构由            、         和            三级组成。

4．SQL有两种使用方式：                 和                  。

二、选择（10题，每题 1.5分，共15分）

1．数据是指所有能输入到计算机中并被计算机程序所处理的     的总称。

   A）文字      B）字母          C）符号       D）数字

2．数据模型的三要素是指          。

A） 数据结构、数据对象和数据共享

B） 数据结构、数据操作和数据控制

C） 数据结构、数据对象和完整性

D） 数据结构、数据操作和完整性约束条件

3．一般说来，两个实体之间的联系可分为        种。

   A）四        B）三             C）二        D）六

4．某个用户对某类数据有何种操作权力由            决定。

A）用户       B）系统分析员     C）DBA       D）应用程序员

5．若关系R（U）的每个属性值都是不可分的原子值，且每个非主属性完全函数依赖于关键字，则称R满足           。

A）1NF        B）2NF            C）3NF       D）BCNF

6．数据库系统中，数据独立性是指              。

  A）数据之间相互独立      B）应用程序与数据库的结构之间相互独立

C）数据的逻辑结构与物理结构相互独立

D）数据与磁盘之间相互独立

7．对数据库中数据的操作分成两大类            。

  A）查询和更新  B）检索和修改   C）查询和修改   D）插入和修改

8．SQL中，“SALARY IN（1000，2000）”的语义是         。

A） SALARY<=2000 AND SALARY>=1000

B） SALARY<2000 AND SALARY>1000

C） SALARY=1000 AND SALARY=2000

D） SALARY=1000 OR SALARY=2000

9．下列式子中，不正确的是         。

  A）R-S=R-(R∩S)           B）R=(R-S) ∪(R∩S)

  C）R∩S=S-(S-R)           D）R∩S=S-(R-S)

10．数据库设计人员和用户之间沟通信息的桥梁是          。

A）程序流程图  B）实体联系图  C）模块结构图  D）数据流图(DFD)

三、简答题（5×3′）

1、 数据库系统阶段的数据库管理有哪些特点？

2、 关系三完整性规则是什么？

3、 为什么要进行关系模式的分解？分解的依据的是什么？

4、 基于数据库系统生存期的数据库设计分成哪些阶段？

5、 简述层次模型、网状模型、关系模型的特点？

四、名词解释(5×2′)

1、 3NF

2、 视图 

3、DBMS 

4、实体  

5、模式

五、E-R模型设计题（24分）

1． 设有商务数据库用以处理销售记帐，它涉及的数据包括顾客、商品信息。试设计一个E-R模型，并将之转换为关系模型。（注：关键字用下划线标出）（10分）

2． 设要为银行的储蓄业务（活期）设计一个数据库。数据库中有储户、存款单、取款单信息。试设计一个E-R模型，并将之转换为关系模型。（注：关键字用下划线标出）（14分）

六、程序设计题(26分)

对于教学数据库的3个基表（S表示学生表，C表示课程表，SC表示学生选课表，假设基表中已存在记录，AGE、GRADE为数值型，其余字段均为字符型）：

S(SNO,SNAME,AGE,SEX) C(CNO,CNAME,TEACHER)  SC(SNO,CNO,GRADE)

1、 创建S表，且以SNO为主键。（2）

2、 查询LI老师所授课程的课程号与课程名。（2）

3、 插入C(C01,高等数学，LI)记录。（2）

4、 查询年龄大于21岁的男学生的学号与姓名。（2）

5、 查询学号为S1的学生所学课程的课程名与任课教师名。（3）

6、 查询至少选修LI老师所授课程中一门课程的男学生姓名。（3）

7、 查询WANG同学不选的课程的课程号与课程名。（4）

8、 统计选修每一门课程的男女学生的人数与平均成绩。（4）

9、 查询至少选修两门课程的学生姓名与学号。（4）

一、填空题（本题10分，每小题1分）

1. SQL语言有四类语句，分别是            、             、DML和             （中文及英文简写均可）。

2．SQL有两种使用方式：                 和                  。

3．数据库系统一般由            、_____________、_____________和用户组成。

4. 关系模型的基本数据结构是                 ，其数据库存储时的基本组织方式

是                 。

二、选择（本题15分，每题 1.5分）

    1．对DB中数据的操作分成两大类     。

       A）查询和更新      B）检索和修改    C）查询和修改     D）插入和修改

2．SQL中，下列操作不正确的是          。

 A）AGE IS NOT NULL              B）NOT（AGE IS NULL）

C）SNAME=‘王五’               D）SNAME=‘王%’

3．SQL Server是一种        数据库管理系统。

       A）层次型        B）网络型         C）关系型        D）链状型

4．某个用户对某类数据有何种操作权力由            决定。

A）用户       B）系统分析员     C）DBA       D）应用程序员

5．若关系模式R（U）的每个非主属性都不部分函数依赖、也不传递函数依赖于关键字，则称R满足           。

A）1NF        B）2NF            C）3NF       D）BCNF

6．当多个用户的              同时存取或修改数据时，可能会发生相互干扰而造成得到错误的结果。

       A）应用程序     B）操作        C）数据库     D）并发进程

7．数据库系统中，数据独立性是指              。

      A）数据之间相互独立      B）应用程序与数据库的结构之间相互独立

C）数据的逻辑结构与物理结构相互独立

D）数据与磁盘之间相互独立

8．SQL中，“AGE IN（20，22）”的语义是         。

A）AGE<=22 AND AGE >=20               B）AGE <22 AND AGE >20

C）AGE =20 AND AGE =22                D）AGE =20 OR AGE =22

9．下列式子中，不正确的是         。

       A）R∩S=S-(R-S)               B）R∩S=S-(S-R)

       C）R=(R-S) ∪(R∩S)           D）R-S=R-(R∩S)

10．概念模型表示方法最常用的是          。

A）关系模型  B）实体联系方法  C）面向对象方法  D）数据模型

三、简答题（本题16分，每小题4分）

1．关系数据库包含哪三级模式？它们分别对应数据库的哪一部分？

2．什么是数据库管理系统（DBMS）？其主要功能有哪些？

3．关系模型的数据完整性包含哪些内容?分别说明每一种完整性的作用。

4．关系数据库的规范化理论是为了解决什么问题？一个关系模式从1NF到2NF，消除了原关系模式的什么问题？

四、名词解释（本题10分，每小题2分）

1．实体集R、S之间1:N的联系：

2．数据库：

3．基表：

4．完全函数依赖：

5．外模式：

五、E-R模型设计题（本题25分）

    1．设有商务数据库用以处理销售记帐，它涉及的数据包括顾客、商品信息。试设计一个E-R模型，并将之转换为关系模型。（注：关键字用下划线标出）（10分）

2. 学校课程管理子系统主要包括的实体有学生、课程、教师、教室，试设计该子系统E-R模型，并将之转换为关系模型。（注：关键字用下划线标出）（15分）

六、对于某图书出版管理数据库，其中有两个表：

publishers(pub_id，pub_name，city)

titles(title_id，title_name，type，pub_id，price，pubdate)

含义为：出版商表（出版商代号，出版商名字，所在城市），书籍表（书号，书名，书的种类，出版商代号，价格，出版日期），并规定每一出版商有唯一的代号，每一种书以书号标识。写出实现下列各功能的SQL语句。（共24分）

1. 用SQL的数据定义语言建立titles表，并定义其主码与外码。（4分）

2.  把某一出版商的信息（0735，人民邮电出版社，北京）录入到publishers表中。（2分）

3.  把书的种类为“计算机”类的书的价格降低30%。（2分）

4.  查询书的价格在20至50元之间的书的书号、种类和价格。（2分）

5.  按书的种类分类，查询各类书籍的平均价格，并按平均价格降序排列。（3分）

6.  从publishers 表和titles表中查询书的书号、书名、种类及出版商名字。（3分）

7.  查询所有价格高于平均价格的书。（3分）

8.  列出“机械工业出版社”自20##年以来出版的各种书籍情况。（3分）

9.  删除所在城市为“沈阳”的出版商的信息。（2分）

一 填空题（本题10分，每小题1分）

1. SQL语言有四类语句，分别是   DDL      、     DQL     、DML和    DCL     （中文及英文简写均可）。

2．SQL有两种使用方式：  自含式 SQL       和        嵌入式SQL      。

3．数据库系统一般由  DB       、应用系统、__ DBMS ___、__DBA______和用户组成。

4. 关系模型的基本数据结构是     基表        ，其数据库存储时的基本组织方式

是     文件        。

二、选择（本题15分，每题 1.5分）

    1．对DB中数据的操作分成两大类  A   。

       A）查询和更新      B）检索和修改    C）查询和修改     D）插入和修改

2．SQL中，下列操作不正确的是    B      。

 A）AGE IS NOT NULL              B）NOT（AGE IS NULL）

C）SNAME=‘王五’               D）SNAME=‘王%’

3．SQL Server是一种   C     数据库管理系统。

       A）层次型        B）网络型         C）关系型        D）链状型

4．某个用户对某类数据有何种操作权力由   C         决定。

A）用户       B）系统分析员     C）DBA       D）应用程序员

5．若关系模式R（U）的每个非主属性都不部分函数依赖、也不传递函数依赖于关键字，则称R满足     C      。

A）1NF        B）2NF            C）3NF       D）BCNF

6．当多个用户的     D        同时存取或修改数据时，可能会发生相互干扰而造成得到错误的结果。

       A）应用程序     B）操作        C）数据库     D）并发进程

7．数据库系统中，数据独立性是指     B         。

      A）数据之间相互独立      B）应用程序与数据库的结构之间相互独立

C）数据的逻辑结构与物理结构相互独立

D）数据与磁盘之间相互独立

8．SQL中，“AGE IN（20，22）”的语义是   D      。

A）AGE<=22 AND AGE >=20               B）AGE <22 AND AGE >20

C）AGE =20 AND AGE =22                D）AGE =20 OR AGE =22

9．下列式子中，不正确的是    A     。

       A）R∩S=S-(R-S)               B）R∩S=S-(S-R)

       C）R=(R-S) ∪(R∩S)           D）R-S=R-(R∩S)

10．概念模型表示方法最常用的是    B      。

A）关系模型  B）实体联系方法  C）面向对象方法  D）数据模型

三、简答题（本题16分，每小题4分）

1．关系数据库包含哪三级模式？它们分别对应数据库的哪一部分？

答：关系数据库包含内模式、模式、外模式三级模式。

    它们分别对应数据库的存储文件、基表、视图。

2．什么是数据库管理系统（DBMS）？其主要功能有哪些？

答：数据库管理系统是负责具体实施DB的定义、创建、维护、管理等各项功能的一组专用软件。

   其主要功能有：DB的定义功能；数据操纵功能；DB运行管理；DB的建立和维护功能。

3．关系模型的数据完整性包含哪些内容?分别说明每一种完整性的作用。

答：关系模型的数据完整性包含：

    实体完整性：保证RM元组唯一性；

    参照完整性：定义外码与主码之间的引用规则，实质是不允许引用不存在的实体；

    用户自定义完整性：针对某一具体RDB的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求。

4．关系数据库的规范化理论是为了解决什么问题？一个关系模式从1NF到2NF，消除了原关系模式的什么问题？

答：由于数据之间存在着联系和约束，在关系模式的关系中可能会存在数据冗余和操作异常现象，因此，需把关系模式进行分解，以消除冗余和异常现象。

   一个关系模式从1NF到2NF，消除了原关系模式中的部分函数依赖。

四、名词解释（本题10分，每小题2分）

1．实体集R、S之间1:N的联系：对于R中每一实体，S中有N个实体与之联系；而对于S中每一实体，R中至多只有一个实体与之联系。

2．数据库：按一定的组织结构集成存储在计算机存储介质上的相关数据及数据之间逻辑关系的集合。

3．基表：实表。独立存在的表，不由其它表导出。

4．完全函数依赖：若R（U）中，已知X→Y，且对于任何X的真子集X’都有X’→Y，则称Y完全函数依赖于X，记作X→Y。

5．外模式：也称“子模式，用户模式”，是DB用户看到的（或用到的）那部分数据视图。

五、E-R模型设计题（本题25分）

1．设有商务数据库用以处理销售记帐，它涉及的数据包括顾客、商品信息。试设计一个E-R模型，并将之转换为关系模型。（注：关键字用下划线标出）（10分）

（1）E-R图（属性略）：

 

（2）关系模型：顾客（顾客号，姓名，地址，电话，…）；

               商品（商品号，品名，型号规格，单价，…）；

               购买（顾客号，商品号，数量，日期，…）。

2. 学校课程管理子系统主要包括的实体有学生、课程、教师、教室，试设计该子系统E-R模型，并将之转换为关系模型。（注：关键字用下划线标出）（15分）

（1）E-R图（属性略）：

 

（2）关系模型：学生（学号，姓名，年龄，…）；

               课程（课程号，课程名，课时，…）；

                   教师（教师号，姓名，职称，…）；

教室（楼名，教室号，…）；

选课（学号，课程号，成绩，…）；

授课（教师号，课程号，楼名，教室号，时间，…）。

六、对于某图书出版管理数据库，其中有两个表：

publishers(pub_id，pub_name，city)

titles(title_id，title_name，type，pub_id，price，pubdate)

含义为：出版商表（出版商代号，出版商名字，所在城市），书籍表（书号，书名，书的种类，出版商代号，价格，出版日期），并规定每一出版商有唯一的代号，每一种书以书号标识。写出实现下列各功能的SQL语句。（共24分）

10. 用SQL的数据定义语言建立titles表，并定义其主码与外码。（4分）

Create table title(title_id char(10),

title_name char(20),

type char(10),

pub_id char(10),

price numeric(6,2),

pubdate datetime,

primary key(title_id),

foreign key(pub_id) references publishers(pud_id))；

11. 把某一出版商的信息（0735，人民邮电出版社，北京）录入到publishers表中。（2分）

Insert into publishers values(‘0735’,’人民邮电出版社’,’北京’)；

12. 把书的种类为“计算机”类的书的价格降低30%。（2分）

Update titles set price=price*(1-0.3)

              Where type=’计算机’；

13. 查询书的价格在20至50元之间的书的书号、种类和价格。（2分）

Select title_id,type,price from titles where price between 20 and 50；

14. 按书的种类分类，查询各类书籍的平均价格，并按平均价格降序排列。（3分）

Select avg(price) from titles group by type

Order by avg(price) desc；

15. 从publishers 表和titles表中查询书的书号、书名、种类及出版商名字。（3分）

Select title_id,title_name,type,pub_name

from titles,publishers

where pub_id= publishers. pub_id；

16. 查询所有价格高于平均价格的书。（3分）

Select * from titles where price>(select avg(price) from titles)；

17. 列出“机械工业出版社”自20##年以来出版的各种书籍情况。（3分）

Select * from titles where pub_id=(select pub_id from publishers

Where pub_name=’机械工业出版社’)

And pubdate>=’2001/01/01’；

18. 删除所在城市为“沈阳”的出版商的信息。（2分）

Delete from publishers where city=’沈阳’；

1、 对于教学数据库（TEACH）构造3个基本表：

S(SNUM,SNAME,SDEPT,AGE,SEX)；( 注：SDEPT为“学生所属系/院名”)

C(CNUM,CNAME,TEACHER,PCNUM)；( 注：PCNUM为“先修课程编号”)

SC(SNUM,CNUM,GRADE)；

(1) 创建教学数据库：学生信息表、课程信息表、学生选课表；

(2) 为每个基表添加多条记录（自己添加，所添加的数据要能够满足以下各题的查询要求）；

(3) 检索年龄大于23岁的男学生的学号和姓名；

(4) 检索’liu’老师所授课程的课程号和课程名；

(5) 检索学号为’S3’的学生所学课程的课程名与任课教师名；

(6) 检索至少选修’liu’老师所授课程中一门课程的男学生姓名；

(7) 检索没有选修’liu’老师所授课程的女学生姓名和学号；

(8) 检索’wang’同学不学的课程的课程号；

(9) 检索至少选修两门课程的学生的姓名和学号；

(10)      检索全部学生都选修的课程的课程号与课程名；

(11)      检索选修课程包含’liu’老师所授课程的学生学号；

(12)      在表C中统计开设课程的教师人数；

(13)      求选修’C4’课程的女学生的平均年龄；

(14)      求每个学生选修课程（已有成绩）的门数和平均成绩；

(15)      统计每个学生选修课程的门数（超过5门的学生才统计），要求输出学生学号和选修门数，查询结果按门数降序，若门数相同，按学号升序；

(16)      检索学号比’wang’同学大，而年龄比他小的学生姓名；

(17)      在表SC中检索成绩为空的学生学号和课程号；

(18)      检索姓名以L打头的所有学生的姓名和年龄；

(19)      求年龄大于女同学平均年龄的男学生姓名和年龄；

(20)      求年龄大于所有女同学年龄的男学生姓名和年龄；

(21)      检索所授课程平均成绩大于80分的教师姓名，并将检索到的值送往另一个表FACULTY(TNAME)；（注：该表由学生自己创建）

(22)      在SC中删除尚无成绩的选课元组；

(23)      将选修’liu’老师课程的女同学选课元组全部删除；

(24)      将’MATHS’课不及格的成绩全改为60分；

(25)      将低于所有课程总平均成绩的女同学成绩提高5％；

(26)      在表SC中修改’C4’课程的成绩，若成绩小于等于70分时提高5％，若成绩大于70分时提高4％；

(27)      在表SC中，当某个成绩低于全部课程的平均成绩时，提高5％；

(28)      求男同学每一年龄的人数；

(29)      求女同学的人数和平均年龄及平均成绩；

(30)      检索开设两门以上课程的教师姓名；

(31)      检索未选修’wang’老师所授全部课程的学生学号；

(32)      统计选修每一门课程的男女学生人数和平均成绩，要求显示(C#,CNAME,TEACHER,SEX,NUM,AVG_GRADE)，其中NUM和AVG_GRADE为人数和平均成绩。

2、 对于图书发行数据库（B_PUBLISH）构造4个基本表：

STORE(SNO,SNAME,ADDRESS) ；( 注：STORE为“书店”)

LIBRARY(LNO,LNAME,CITY,TEL)；( 注：LIBRARY为“图书馆”)

BOOK(BNO,BNAME,PRICE) ；( 注：BOOK为“图书”)

LBS(LNO,BNO,SNO,QTY)； ( 注：LBS为“馆藏”；QTY为“数量”)

（1）创建图书发行数据库：书店信息表、图书馆信息表、图书信息表、馆藏信息表；

（2）为每个基表添加多条记录（自己添加，所添加的数据要能够满足以下各题的查询要求）；

（3）查找’L1’从书店’S1’购买的图书书号及其册数；

（4）取出馆址在’Shanghai’的馆名及电话号码；

（5）取出’S3’发行的图书书名和数量；

（6）取出已发行图书中最贵和最便宜的那种书的书名和定价；

（7）查找购买图书’B4’最多的图书馆馆名；

（8）取出’L4’ 收藏图书的书名；

（9）取出收藏图书’情报检索语言’的馆名；

（10）将’B5’的单价更改为32.50元；

（11）删去’B2’及其相关的馆藏记录；

（12）将(‘B6’,’人工智能原理’,42.00)登入关系BOOK。

3、实验结束时提交《课程设计报告》，内容包含：

（1）列出每个基表的结构及记录构成；

（2）对第2大题的每小题，均要求用关系代数表示；

（3）用SQL Server 2000语句完成每一题；

（4）每题的结果。

3、 对于教学数据库（TEACH）构造3个基本表：

S(SNUM,SNAME,SDEPT,AGE,SEX)；( 注：SDEPT为“学生所属系/院名”)

C(CNUM,CNAME,TEACHER,PCNUM)；( 注：PCNUM为“先修课程编号”)

SC(SNUM,CNUM,GRADE)；

(33)      创建教学数据库：学生信息表、课程信息表、学生选课表；

(34)      为每个基表添加多条记录（自己添加，所添加的数据要能够满足以下各题的查询要求）；

(35)      检索年龄大于23岁的男学生的学号和姓名；

select snum,sname from s where age>23 and sex='男'

(36)      检索’liu’老师所授课程的课程号和课程名；

  select cnum,cname from c where teacher='liu'

(37)      检索学号为’S3’的学生所学课程的课程名与任课教师名；

  select c.cnum,c.teacher from c,sc where c.cnum=sc.cnum and sc.snum='s3'

(38)      检索至少选修’liu’老师所授课程中一门课程的男学生姓名；

  select sname from s where snum in

(select snum from sc where cnum in

(select cnum from c where teacher='liu'))

 and sex='男'

(39)      检索没有选修’liu’老师所授课程的女学生姓名和学号；

  select sname,snum from s where snum not in

(select snum from sc where cnum in

(select cnum from c where teacher='liu'))

and sex='女'

(40)      检索’wang’同学不学的课程的课程号；

  select cnum from c where  cnum not in

(select cnum from sc where  snum in

 (select snum from s where  sname='wang'))

(41)      检索至少选修两门课程的学生的姓名和学号；

  select sname,snum from s where  snum in

(select snum from sc group by snum having count(*)>=2)

(42)      检索全部学生都选修的课程的课程号与课程名；

  select cnum,cname from c where  not exists

(select * from s  where  not exists

(select * from sc where snum=s.snum and cnum=c.cnum))

(43)      检索选修课程包含’liu’老师所授课程的学生学号；

  select distinct sc.snum from sc,c where sc.cnum=c.cnum and c.teacher='liu'

(44)      在表C中统计开设课程的教师人数；

  select count(distinct teacher) teacher_num from c

(45)      求选修’C4’课程的女学生的平均年龄；

  select avg(age) avg_age from sc,s

where sc.snum=s.snum and sc.cnum='c4' and sex='女'

(46)      求每个学生选修课程（已有成绩）的门数和平均成绩；

  select snum,count(*) num,avg(grade) avg_grade from sc

where grade is not null group by snum

(47)      统计每个学生选修课程的门数（超过5门的学生才统计），要求输出学生学号和选修门数，查询结果按门数降序，若门数相同，按学号升序；

  select snum,count(*) from sc

group by snum having count(*)>5

order by count(*) desc,snum

(48)      检索学号比’wang’同学大，而年龄比他小的学生姓名；

  select sname from s where snum>(select snum from s where sname='wang')

and age<(select age from s  where sname='wang')

(49)      在表SC中检索成绩为空的学生学号和课程号；

  select snum,cnum from sc where grade is null

(50)      检索姓名以L打头的所有学生的姓名和年龄；

  select sname,age from s where sname like 'l%'

(51)      求年龄大于女同学平均年龄的男学生姓名和年龄；

  select sname,age from s where sex='男'

and age>(select avg(age) from s where sex='女')

(52)      求年龄大于所有女同学年龄的男学生姓名和年龄；

  select sname,age from s where sex='男'

and age>(select max(age) from s where sex='女')

(53)      检索所授课程平均成绩大于80分的教师姓名，并将检索到的值送往另一个表FACULTY(TNAME)；（注：该表由学生自己创建）

  delete from faculty;

insert into faculty

       select distinct teacher from c where cnum in

(select cnum from sc where grade is not null

group by cnum having avg(grade)>80);

select * from faculty;

(54)      在SC中删除尚无成绩的选课元组；

  delete from sc where grade is null

(55)      将选修’liu’老师课程的女同学选课元组全部删除；

  delete from sc where snum in

(select snum from s where sex='女')

and cnum in (select cnum from c where teacher='liu')

(56)      将’MATHS’课不及格的成绩全改为60分；

  update sc set grade=60 where grade<60

and cnum in(select cnum from c where cname='maths')

(57)      将低于所有课程总平均成绩的女同学成绩提高5％；

  update sc set grade=grade*(1+0.05)

where grade<(select avg(grade) from sc where grade is not null)

and snum in(select snum from s where sex='女')

(58)      在表SC中修改’C4’课程的成绩，若成绩小于等于70分时提高5％，若成绩大于70分时提高4％；

  update sc set grade=grade*(1+0.05) where cnum='c4' and grade<70;

update sc set grade=grade*(1+0.04) where cnum='c4' and grade>70;

(59)      在表SC中，当某个成绩低于全部课程的平均成绩时，提高5％；

  update sc set grade=grade*(1+0.05)

where grade<(select avg(grade) from sc where grade is not null)

(60)      求男同学每一年龄的人数；

  select age,count(age) from s where sex='男' group by age

(61)      求女同学的人数和平均年龄及平均成绩；

  select count( sex) num,avg(age) avg_age,avg(grade) avg_grade from s,sc

where sc.snum=s.snum and grade is not null and sex='女' group by sex

(62)      检索开设两门以上课程的教师姓名；

  select teacher from c group by teacher having count(*)>2

(63)      检索未选修’wang’老师所授全部课程的学生学号；

  select snum from s where  snum not in

(select snum from sc where  cnum in

 (select cnum from c where  teacher='wang'))

(64)      统计选修每一门课程的男女学生人数和平均成绩，要求显示(CNUM,CNAME,TEACHER,SEX,NUM,AVG_GRADE)，其中NUM和AVG_GRADE为人数和平均成绩。

select sc.cnum,c.cname,c.teacher,s.sex,count(*) num,avg(grade) avg_grade

from s,c,sc

where  sc.snum=s.snum and sc.cnum=c.cnum and grade is not null

group by s.sex,sc.cnum,c.cname,c.teacher

4、 对于图书发行数据库（B_PUBLISH）构造4个基本表：

STORE(SNO,SNAME,ADDRESS) ；( 注：STORE为“书店”)

LIBRARY(LNO,LNAME,CITY,TEL)；( 注：LIBRARY为“图书馆”)

BOOK(BNO,BNAME,PRICE) ；( 注：BOOK为“图书”)

LBS(LNO,BNO,SNO,QTY)； ( 注：LBS为“馆藏”；QTY为“数量”)

（1）创建图书发行数据库：书店信息表、图书馆信息表、图书信息表、馆藏信息表；

（2）为每个基表添加多条记录（自己添加，所添加的数据要能够满足以下各题的查询要求）；

（3）查找’L1’从书店’S1’购买的图书书号及其册数；

     Select bno,qty from lbs where sno=’S1’ and lno=’L1’;

（4）取出馆址在’Shanghai’的馆名及电话号码；

     Select lname,tel from library where city=’shanghai’;

（5）取出’S3’发行的图书书名和数量；

     Select distinct bname,qty from book,lbs where book.bno=lbs.bno and sno=’S3’;

（6）取出已发行图书中最贵和最便宜的那种书的书名和定价；

     Select bno,price from book where price=(select max(price) from book)

or price=(select min(price) from book);

（7）查找购买图书’B4’最多的图书馆馆名；

     Select lname from library where lno in(select lno from lbs where bno=’B4’

and qty=(select max(qty) from lbs));

（8）取出’L4’ 收藏图书的书名；

     Select bname from book where bno in

          (select bno from lbs where lno=’L4’);

（9）取出收藏图书’情报检索语言’的馆名；

     Select lname from library where lno in

(select lno from lbs where bno in

(select bno from book where bname=’情报检索语言’))；

（10）将’B5’的单价更改为32.50元；

     Update book set price=32.50 where bno=’B5’;

（11）删去’B2’及其相关的馆藏记录；

     Delete from book where bno=’B2’;

     Delete from lbs where bno=’B2’;

（12）将(‘B6’,’人工智能原理’,42.00)登入关系BOOK。

     Insert into book values(‘B6’,’人工智能原理’,42.00)；

3、实验结束时提交《课程设计报告》，内容包含：

（1）列出每个基表的结构及记录构成；

（2）对第2大题的每小题，均要求用关系代数表示；

（3）用SQL Server 2000语句完成每一题；

（4）每题的结果。