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

文章目录

E1.
E2.
E3.
Some Tips.

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.PNoand BName='操作系统'and PName='高等教育出版社'//Problem 2
select PTel
from Publish,Book
where Publish.PNo=Book.PNoand BType='小说'and BAuth='张欣'//Problem 3
select BPrice,PName,BType
from Book,Publish
where Book.PNo=Publish.PNoand BType='计算机'and PName='电子工业出版社'// Problem 4
select *
from Book
where BName='高等数学' and BPrice<(select BPrice from Book,Publish where Book.PNo=Publish.PNoand 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,BNo)
)//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.BNoand 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(S#,SN,C#,T#)
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,SCwhere 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,BNo)
)