【数据库原理】SQL语句练习

E1.

1．设有以下两个数据表，各表中的结果及字段名如下：
图书（Book）包括书号（BNo）、类型（BType）、书名（BName）、作者（BAuth）、单价（BPrice）、出版社号（PNo）；
出版社（Publish）包括出版社号（PNo）、出版社名称（PName）、所在城市（PCity）、电话（PTel）。
用SQL实现下述功能。
（1）在“高等教育出版社”出版、书名为“操作系统”的图书的作者名；
（2）查找为作者“张欣”出版全部“小说”类图书的出版社的电话；
（3）查询“电子工业出版社”出版的“计算机”类图书的价格，同时输出出版社名称及图书类别；
（4）查找比“人民邮电出版社”出版的“高等数学”价格低的同名书的有关信息；
（5）查找书名中有“计算机”一词的图书的书名及作者；
（6）在“图书”表中增加“出版时间”（BDate）项，其数据类型为日期型；
（7）在“图书”表中以“作者”建立一个索引。

//Problem 1
select BAuth
from Book
where BName='操作系统'
	and PNo=(select PNo 
				from Publish 
				where PName='高等教育出版社')

select BAuth
from Book,Publish
where Book.PNo=Publish.PNo
	and BName='操作系统'
	and PName='高等教育出版社'

//Problem 2
select PTel
from Publish,Book
where Publish.PNo=Book.PNo
	and BType='小说'
	and BAuth='张欣'

//Problem 3
select BPrice,PName,BType
from Book,Publish
where Book.PNo=Publish.PNo
	and BType='计算机'
	and PName='电子工业出版社'

// Problem 4
select *
from Book
where BName='高等数学' and 
	  BPrice<(select BPrice 
				from Book,Publish 
				where Book.PNo=Publish.PNo
					and BName='高等数学'
					and PName='人民邮电出版社')

//Problem 5
select BAuth,BName
from Book
where BName like '%计算机%'

//Problem 6
alter table Book
add
Bdate datetime

//Problem 7
create index AuthorIndex
on Book(BAuth)

E2.

假设有一个书店，书店的管理者要对书店的经营状况进行管理，需要建立一个数据库，其中包括两个表：
存书（书号，书名，出版社，版次，出版日期，作者，书价，进价，数量）
销售（日期，书号，数量，金额）
请用SQL实现书店管理者的下列要求。
（1）建立存书表和销售表；
（2）掌握书的库存情况，列出当前库存的所有书名、数量、余额（余额=进价×数量，即库存占用的资金）；
（3）统计总销售额；
（4）列出每天的销售报表，包括书名、数量和合计金额（每一种书的销售总额）；
（5）分析畅销书，即列出本期（从当前日期起，向前30天）销售数量大于100的书名、数量。

//Problem 1
create table StoreBook
(
	BNo varchar(6) Primary Key,
	BName nvarchar(20),
	Publish nvarchar(20),
	Edition int,
	PubDate datetime,
	Author nvarchar(20),
	SalePrice smallmoney,
	Inprice smallmoney,
	Count int
)

create table Sale
(
	Date datetime,
	BNo varchar(6) foreign key references Book(BNO),
	Count int,
	Price smallmoney,
	constraint Sale_Prim Primary Key(Date,Price)
)

//Problem 2
select BName Count,InPrice*Count as RestPrice
from Book

//Problem 3
select Date,SUM(Count*Price) as TotalMoney
from Sale
group by Date

//Problem 4
select BName,Count,Count*Price as TotalMoney,Date
from Book,Sale
where Book.BNo=Sale.BNo

//Problem 5
select BName,Count
from Book,Sale
where Book.BNo=Sale.BNo
	and Date+30>(select MAX(Date) from Sale)
	and Count>100

E3.

设有如下四个基本表S，C，SC，T，结构如图3-20所示。

（1）用SQL的DDL语言创建S表，S#为主码，SN不能为空。
（2）创建计算机系学生的视图，该视图的属性列由学号、姓名、课程号和任课教师号组成。
（3）检索计算机系年龄在20岁以上的学生学号。
（4）检索姓王的教师所讲课程的课程号及课程名称。
（5）检索张三同学所学课程的成绩，列出SN、C#和GR。
（6）检索选修总收入超过1000元的教师所讲课程的学生姓名、课程号和成绩。
（7）检索没有选修C1课程且选修课程数为两门的学生的姓名和平均成绩，并按平均成绩降序排列。
（8）检索选修和张三同学所选课程中任意一门相同的学生姓名、课程名。
（9）S1同学选修了C3，将此信息插入SC表中。
（10）删除S表中没有选修任何课程的学生记录。

//Problem 1
create table S
(
	S# varchar(6) Primary Key,
	SN nvarchar(20) not null,
	age int,
	dept navrchar(20)
)

//Problem 2
create view Com_View
as select S.S#,SN,SC.C#,T#
from S,SC,T
where S.S#=SC.S#
	and SC.C#=T.C#
	and dept='计算机'

//Problem 3
select S#
from S
where age>20 and dept='计算机'

//Problem 4
select T.C#,CN
from T,C
where T.C#=C.C#
	and TN like '王%'

//Problem 5
select SN,C#,GR
from S,SC
where S.S#=SC.S#
	and SN='张三'

//Problem 6
select SN,T.C#,GR
from T,SC,S
where T.C#=SC.C#
	and SC.S#=S.S#
	and (SAL+COMM)>1000

//Problem 7
select S.S#,SN,AVG(GR) as AvgScore
from SC,S
where SC.S#=S.S#
	and C# !='C1'
group by S#,SN
having count(*)=2
order by AVG(GR) desc

//Problem 8
select SN,CN
from S,C,SC
where SC.C#=C.C#
	and S.S#=SC.S#
	and C# in (select C# 
				from S,SC
				where S.S#=SC.S#
					and SN='张三')
	and SN != '张三'

//Problem 9
insert into table SC(S#,C#)
values('S1','C3')

//Problem 10
delete table S
where S# not in (select distinct S# from SC)

Some Tips.

• create table中，学号、书号等等编号，通常使用nchar(x)类型；
• 人名、书名、系别名通常使用nvarchar(x)类型，和nchar的区别在于前者非定长，而nchar是定长的，管你听没听懂，用就完了。
• 为多个键的组合定义约束时，作为表约束，需要写在属性列的定义之后，例如：

create table Sale
(
	Date datetime,
	BNo varchar(6) foreign key references Book(BNO),
	Count int,
	Price smallmoney,
	constraint Sale_Prim Primary Key(Date,Price)
)

创建XXX通常都对应于create语句：

• 创建数据库：create database db_name on ... log on ...
• 创建数据表：create table table_name (Attributes)
• 创建索引：create index index_name on tb_name(Attributes)
• 创建视图：create view view_name as select...
• 修改数据表的定义通常使用alter，其中更加细节的命令有add\alter\drop
• 约束分为列约束和表约束，前者追加在列定义(create语句中)之后，后者自成一行。