数据库原理及应用教程第三章作业

三、设计题

1、设有以下两个数据表，各表的结果及字段名如下：

图书（Book）包括书名（BNo）、类型（BType）、书名（BName）、作者（BAuth）、单价（BPrice）、出版社（PNo）

出版社（Publish）包括出版社号（PNo）、出版社名称（PName）、所在城市（PCity）、电话（PTel）。

用SQL实现下述功能：

（1）在“”高等教育出版社出版、书名为“操作系统”的图书的作者名；

答：select BAuth

from Book,Publish

where Book.PNo =Publish.PNo

and BName='操作系统'

and PName='高等教育出版社出版'

（2）查找为作者“张欣”出版全部“小说”类图书的出版社的电话；答：select PTel

from Book,Publish

where Book.PNo=Publish.PNo

and BAuth='张欣'

and BType='小说'

（3）查询“电子工业出版社”出版的“计算机”类的图书的价格，同时输出版社名称及图书类别；

答：select BPrice,PName,BType

from Book,Publish

where Book.PNo=Publish.PNo

and PName='电子工业出版社'

and BType='BType'

（4）查找比“人民邮电出版社”出版的“高等数学”价格低的同名书的有关信息；

答：select *

from Book

where BName='高等数学'

and BPrice< ANY (select BPrice

from Book,Publish

where PName='人民邮电出版社'

and BName='高等数学'

and Publish.PNo=Book.PNo )

（5）查找书名中有“”计算机一词的图书的书名及作者；

答：select BName,BAuth

from Book

where BName like '%计算机%'

（6）在“图书”表中正增加“出版时间”（BDate）项，其数据类型为日期型；

答：alter table Book

add

BDate datetime

（7）在“图书”表中以“作者”建立一个索引。

答：create unique index BAuth_Book on Book(BAuth)

2、假设有一个书店，书店的管理者要对书店的经营状况进行管理，需要建立一个数据库，其中包括两个表：

存书（书号，书名，出版社，版次，出版日期，作者，进价，数量）销售（日期，书号，数量，金额）

请用SQL实现书店管理者的下列要求：

（1）建立存书表和销售表；

答：create table Book

(

BNo int,

BName char(10),

Publish char(20),

Edition int,

Date datetime,

Auth char(10),

Price int,

Inprice int,

Number int

)

create table Sale

(

Date datetime,

BNo int,

Number int,

Money int

)

（2）掌握书的库存情况，列出当前库存的所有书名、数量、余额（余额=进价数量，即库存占用的资金）；

答：select BName,Number,Inprice*Number as '余额（进价X数量）' from Book

（3）统计总销售额；

答：select SUM(Number*Money) as '总销售额'

from Sale

（4）列出每天的销售报表，包括书名、数量、和合计金额（每一种书的销售总额）；

答：select BName,Sale.Number,Sale.Number*Money as '合计金额' from Book,Sale

where Book.BNo=Sale.BNo

（5）分析畅销书，即列出本期（从当前日期起，向前30天）销售数量大于100的书名、数量。

答：select BName,Sale.Number

from Book,Sale

group by BName,Sale.Number,Sale.Date

having count(Sale.Number)>100 and Sale.Date>=getdate()-30

四、简答题

1、简述SQL支持的三级逻辑结构。

答：1、SQL语言支持的关系数据库三级逻辑结构由外层、概念层和内存构成。

2、在概念层，对应于概念模式的概念记录型的是基本表。基本表是这样的一种表，它本身实际存在。一个基本表就是一个关系，它不是由其他表导出的表。基本表是使用CREATETABLE语句建立的。

3、在外层，用户所看到的可以是基本表，也可以是视图，也可以是基本表＋视图。视图是一个虚拟表，它是由一个或几个基本表导出的表，它不直接存在于物理存储器上的表。视图是使用CREATESQLVIEW语句建立的。

3、解释本章所涉及的有关基本概念的定义：基本表、视图、索引、系统权限、对象权限、角色、并说明视图、角色的作用。

答：基本表：一个关系对应一个基本表；一个或多个基本表对应一个存储文件

视图：视图是从一个或几个基本表导出的表，是一个虚拟的表

索引：记录的关键字与其相应地址的对应表

系统权限：数据库用户能够对数据库系统进行某种特定的操作的权利

对象权限：数据库用户在指定的数据库对象上进行某种特定的操作的权利

角色：是多种权限的集合，可以把角色授予用户或其他角色

视图的作用：通过构造视图，能够在用户和应用程序与实际的基本表之间提供更好的数据独立性。

角色的作用：当把某个角色授予用户（和角色）或从用户（或角色）处收回时，就同时授予或收回了该角色代表的全部权

限。这样就避免了为每个用户重复授予每一项权限的

繁琐工作，可以大大简化管理数据库用户权限的工作。

5、设有如下四个基本表S，C，SC，T，结构如图所示：

（1）用SQL的DDL语言创建S表，S#为主键，SN不能为空。答：create table S

(

S# char(10) primary key not null,

SN varchar(10) not null,

AGE tinyint null,

DEPT nchar(10)

)

create table C

(

C# char(6) primary key,

CN varchar(20) not null

)

create table SC

(

S# char(10) primary key not null,

C# char(6) primary key not null,

GR tinyint

)

create table T

(

T# char(5) primary key not null,

TN varchar(10) not null,

PROF varchar(10),

SAL money,

COMM money,

C# char(6)

)

（2）创建计算机系学生的视图，该视图的属性列由学号、姓名、课程号和任课教师号组成。

答：)create view ComputerDEPT(S#,SN,C#,T#)

as

select S#,SN,C#,T# from S,C,SC,T

where DEPT in ('计算机')

and S.S#=SC.S#

and C.C#=SC.C#

and T.C#=C.C#

（3）检索计算机系年龄在20岁以上的学生学号。

答：select S# from S

where DEPT in ('计算机')

and AGE>20

（4）检索姓王的教师所讲课程的课程号及课程名称。

答：select C#,CN from C,T

where T# like '王%'

and C.C#=T.C#

（5）检索张三同学所学课程的成绩，列出SN，C#，GR。

答：select SN,C#,GR from S,C,SC

where SN='张三'

and S.S#=SC.S#

and C.C#=SC.C#

（6）检索选修总收入超过1000元的教师所讲课程的学生姓名、课程号和成绩。

答：select SN,C#,GR from S,SC,T

where SAL+COMM>1000

and S.S#=SC.S#

and SC.C#=T.C#

（7）检索没有选修C1课程且选修课程数为两门的学生的姓名和平均成绩，并按平均成绩降序排列。

答：select SN,avg(GR) from S,SC

where (S.S#=SC.S#)and('C1' not in (select C# from SC where S.S#=SC.S#))

group by SN

having count(SC.S#)=2

order by avg(gr)desc

（8）检索选修和张三同学所课程中任意一门相同的学生姓名，课程名。

答：select SN,CN from S,C,SC

where S.S#=SC.S# and C.C#=SC.C#

and C# in (select C# from SC where S#='S3')

（9）S1同学选修了C3,将此信息插入SC表中。

答：insert into SC(S#,C#)

values('S1','C3')

（10）删除S表中没有选修任何课程的学生记录。

答：delete from S

where S# not in(select S# from SC)