SQL基础查询语句练习题解

书店系列

数据库中有以下两个表：

• book (书号C(5)主索引，书名C(20)，作者C(10)，单价N(7, 2)，数量I，出版日期D，版次 I，出版社C(2) )
• press (出版社，出版社名）

题目描述

基础

1. 查询数量大于30的图书的信息。
2. 查询单价大于20元，并且出版社为02的图书的书名、作者。
3. 查询版次为第一版的，或者“张小玲”编写的图书信息。
4. 查询数量大于30的图书的信息。
5. 查询《面试方法与技巧》的作者、单价、数量。
6. 查询单价在25元至30元之目的图书信息。
7. 查询2019年出版的图书的书号、书名、作者。
8. 查询所有图书信息，并输出到屏幕上。
9. 查询不同的出版社有几个，显示为“出版社个数”。
10. 查询不同的出版社都有哪些。
11. 查询所有图书信息，并按照出版日期排序。
12. 查询每个出版社出版图书的总数里，列出出版社和总数量。
13. 查询数量最多的前3本图书。
14. 查询2008年以后出版的图书的书名、作者，八折单价。
15. 查询版次为第一版的图书记录的条数，显示为标题"第一版数量"

多表查询

1. 查询所有图书的书号、书名、作者、出版日期、出版社名。
2. 查询“北大出版社”出版的图书信息。
3. 查询“大学语文”的作者、单价、出版曰期和出版社名。
4. 查询每个出版社出版图书的单价的最高值、最低值、平均值，要求显示出版社名。
5. 查询单价大于30，或者数量少于20的图书书名、作者、单价、数量、出版社名。

外连接

1. 查询press表中所有出版社出版的图书的情況，按出版社排序
2. 查询press表和book表中所有出版社及图书的情况，按书号排序

嵌套查询

1. 查询数量大于作者“张小玲”所写的书的库存数量的书号，出版社和数量。
2. 查询单价大于平均单价的那些书的书名，单价，并按单价降序排列。
3. 使用not EXISTS查询没有出版图书的出版社信息

题解

基础

(1)查询数量大于30的图书的信息。

select * from book where 数量 > 30

(2)查询单价大于20元，并且出版社为02的图书的书名、作者。

select 书名, 作者 from book where 单价 > 20 and 出版社 = "02" 

(3)查询版次为第一版的，或者“张小玲”编写的图书信息。

select * from book where 版次 = 1 or 作者 = "张小玲"

(4)查询并计算所有图书的最高单价、最低单价、平均单价。

select max(单价) as "最高单价", min(单价) as "最低单价", avg(单价) as "平均单价"
from book

(5)查询《面试方法与技巧》的作者、单价、数量。

select 作者, 单价, 数量 from book where 书名 = "面试方法与技巧"

(6)查询单价在25元到30元之间的图书信息。

select * from book where 单价 between 25 and 30

(7)查询2019年出版的图书的书号、书名、作者。

select 书号, 书名, 作者 from book where year(出版日期) = 2009

(8)查询所有图书信息，并输出到屏幕上。

select * from book

(9)查询不同的出版社有几个，显示为“出版社个数”。

select count(distinct 出版社) as "出版社个数" from press

(10)查询不同的出版社都有哪些。

select distinct 出版社名 from press

(11)查询所有图书信息，并按照出版日期排序。

没说是升序降序，我按照降序排了。

select * from book order by 出版日期 desc

(12)查询每个出版社出版图书的总数里，列出出版社和总数量。

（总数量表述出版的图书有多少种，而不是印了多少本）

select 出版社, count(书号) as "总数量" from book group by 出版社

(13)查询数量最多的前3本图书。

select top 3 * from book order by 数量 desc

MySQL可以这么写：

select * from book order by 数量 desc limit 3

(14)查询2008年以后出版的图书的书名、作者，八折单价。

select 书名, 作者, 单价*0.8 from book where year(出版日期) >= 2008

(15)查询版次为第一版的图书记录的条数，显示为标题"第一版数量"

select count(书号) as "第一版数量" from book where 版次 = 1

多表查询

(1)查询所有图书的书号、书名、作者、出版日期、出版社名。

select 书号, 书名, 作者, 出版日期, 出版社名
from book join press on book.出版社 = press.出版社

(2)查询“北大出版社”出版的图书信息。

select * from book join press on book.出版社 = press.出版社 where 出版社名 = "北大出版社"

(3)查询“大学语文”的作者、单价、出版日期和出版社名。

select 作者, 单价, 出版日期, 出版社名
from book join press on book.出版社 = press.出版社
where 书名 = "大学语文"

(4)查询每个出版社出版图书的单价的最高值、最低值、平均值，要求显示出版社名。

select press.出版社名, max(单价), min(单价), avg(单价)
from book join press on book.出版社 = press.出版社
group by press.出版社名

(5)查询单价大于30，或者数量少于20的图书书名、作者、单价、数量、出版社名。

select 书名, 作者, 单价, 数量, 出版社名
from book join press on book.出版社 = press.出版社
where 单价 > 30 or 数量 < 30

外连接

(1)查询press表中所有出版社出版的图书的情況，按出版社排序

select *
from book right join press on book.出版社 = press.出版社
order by press.出版社

(2)查询press表和book表中所有出版社及图书的情况，按书号排序

select * 
from book left join press on book.出版社 = press.出版社
order by 书号

嵌套查询

(1)查询数量大于作者“张小玲”所写的书的库存数量的书号，出版社和数量。

select 书号, 出版社, 数量 from book
where 数量 > (select sum(数量) from book where 作者 = "张小玲")

(2)查询单价大于平均单价的那些书的书名，单价，并按单价降序排列。

select 书名, 单价 from book
where 单价 > (select avg(单价) from book)
order by 单价 desc

(3)使用not EXISTS查询没有出版图书的出版社信息

select * from press
where press.出版社 not exists (select distinct(book.出版社) from book)

医院系列

有5个表

• Doctor (医生编号C(4) (主索引， 索引名 DD)，姓名C(8)，性别C(2)，科室C(10) ，工作中 L)
• Patient (病人编号C(4) ( 主索引，索引名 HD)，姓名C(8)，性别C(2)，出生日期(D) ，所属科室 C(2) ，主治医生C(4))
• Medicine(药品编号C(4) (主索引 ，索引名 MD)，药品名称C(10)，价格N(7,2) ，库存 I，备注M)
• Department (科室编号C(2)，科室名C(10))
• prescription (药品编号C(4) , 病人编号 C(4)，医生编号C (4) , 药品名称C(10)，价格 N(7,2)，数量I， 日期(D) )

题目描述

单表查询

1. 查询病人的姓名和年龄，用“年龄”作为列标题，并按年龄降序排列。
2. 查询处于工作中的女医生的全部信息。
3. 查询处方表所含的各种药品编号、药名以及总数量。
4. 查询病人表中的病人属于几个科室。
5. 查询处方表金额（价格*数量）排在前三位的药品编号、医生编号和金额。
6. 在病人表中查询每个医生看病人数并列出医生编号。
7. 查询D003医生所开药品的总金额和医生编号。
8. 查询 “白” 姓女病人的信息。

内、外连接

1. 查询40到50岁的病人姓名，所属的科室名
2. 查询每位病人的姓名、性别、所开药品的总金额（价格*数量）。
3. 列出所有doctor中的医生信息及其所看病人信息。
4. 查询Medicine表的所有药品的药品编号，药名，数量（标题为“库存”）以及该药品被医生开出的金额合计（标题为“总金额”，用价格*数量求得）

嵌套查询

1. 用not in和not exist两种方法查询prescription表里没有开出的medicine表的药品信息。
2. 查询超过M004库存（数量〉的药品信息。

题解

单表查询

(1)查询病人的姓名和年龄，用“年龄”作为列标题，并按年龄降序排列。

select 姓名, year(getdate())-year(出生日期) as 年龄 from Patient order by 出生日期

(2)查询处于工作中的女医生的全部信息。

假设工作中是1,不在中工作为0

select * from Doctor where 性别 = "女" and 工作中 = 1

(3)查询处方表所含的各种药品编号、药名以及总数量。

select 药品编号, 药名, sum(数量) as 总数量 from prescription group by 药品编号, 药名

(4)查询病人表中的病人属于几个科室。

select distinct 所属科室 from Patient

(5)查询处方表金额（价格*数量）排在前三位的药品编号、医生编号和金额。

select top 3 药品编号, 医生编号, 价格*数量 as 金额 from prescription
order by 价格*数量 desc

(6)在病人表中查询每个医生看病人数并列出医生编号。

select 主治医生, count(病人编号) from Patient group by 主治医生

(7)查询D003医生所开药品的总金额和医生编号。

select sum(价格*数量) as 总金额, 医生编号 where 医生编号 = "D003"

(8)查询 “白” 姓女病人的信息。

select * from Patient where 性别 = "女" and 姓名 like "白%"

内、外连接

(1)查询40到50岁的病人姓名，所属的科室名

select 姓名, 科室名
from Patient join Department on Patient.所属科室 = Department.科室编号
where year(getdate()-出生日期) between 40 and 50

(2)查询每位病人的姓名、性别、所开药品的总金额（价格*数量）。

select 姓名, 性别, sum(价格*数量) as 总金额
from Patient join prescription on Patient.病人编号 = prescription.病人编号
group by 姓名, 性别

(3)列出所有doctor中的医生信息及其所看病人信息。

select * from Doctor left join Patient
    on Doctor.医生编号 = Patient.主治医生

(4)查询Medicine表的所有药品的药品编号，药名，库存，以及该药品被医生开出的金额合计（标题为“总金额”，用价格*数量求得）。

select Medicine.药品编号, Medicine.药品名称, 库存, sum(价格) as 总金额
from Medicine left join prescription on Medicine.药品编号 = prescription.药品编号
group by Medicine.药品编号, Medicine.药品名称, 库存

嵌套查询

(1)用not in和not exist两种方法查询prescription表里没有开出的medicine表的药品信息。

select Medicine.*
from Medicine.药品编号 not in (select distinct 药品编号 from prescription)

或

select Medicine.*
from not exist (select * from prescription join Medicine on prescription.药品编号 = Medicine.药品编号)

(2)查询超过M004库存（数量〉的药品信息。

select * from Medicine where 库存 > (select 库存 from Medicine where 药品编号 = "M004")