书店系列
数据库中有以下两个表:
- book (书号C(5)主索引,书名C(20),作者C(10),单价N(7, 2),数量I,出版日期D,版次 I,出版社C(2) )
- press (出版社,出版社名)
题目描述
基础
- 查询数量大于30的图书的信息。
- 查询单价大于20元,并且出版社为02的图书的书名、作者。
- 查询版次为第一版的,或者“张小玲”编写的图书信息。
- 查询数量大于30的图书的信息。
- 查询《面试方法与技巧》的作者、单价、数量。
- 查询单价在25元至30元之目的图书信息。
- 查询2019年出版的图书的书号、书名、作者。
- 查询所有图书信息,并输出到屏幕上。
- 查询不同的出版社有几个,显示为“出版社个数”。
- 查询不同的出版社都有哪些。
- 查询所有图书信息,并按照出版日期排序。
- 查询每个出版社出版图书的总数里,列出出版社和总数量。
- 查询数量最多的前3本图书。
- 查询2008年以后出版的图书的书名、作者,八折单价。
- 查询版次为第一版的图书记录的条数,显示为标题"第一版数量"
多表查询
- 查询所有图书的书号、书名、作者、出版日期、出版社名。
- 查询“北大出版社”出版的图书信息。
- 查询“大学语文”的作者、单价、出版曰期和出版社名。
- 查询每个出版社出版图书的单价的最高值、最低值、平均值,要求显示出版社名。
- 查询单价大于30,或者数量少于20的图书书名、作者、单价、数量、出版社名。
外连接
- 查询press表中所有出版社出版的图书的情況,按出版社排序
- 查询press表和book表中所有出版社及图书的情况,按书号排序
嵌套查询
- 查询数量大于作者“张小玲”所写的书的库存数量的书号,出版社和数量。
- 查询单价大于平均单价的那些书的书名,单价,并按单价降序排列。
- 使用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) )
题目描述
单表查询
- 查询病人的姓名和年龄,用“年龄”作为列标题,并按年龄降序排列。
- 查询处于工作中的女医生的全部信息。
- 查询处方表所含的各种药品编号、药名以及总数量。
- 查询病人表中的病人属于几个科室。
- 查询处方表金额(价格*数量)排在前三位的药品编号、医生编号和金额。
- 在病人表中查询每个医生看病人数并列出医生编号。
- 查询D003医生所开药品的总金额和医生编号。
- 查询 “白” 姓女病人的信息。
内、外连接
- 查询40到50岁的病人姓名,所属的科室名
- 查询每位病人的姓名、性别、所开药品的总金额(价格*数量)。
- 列出所有doctor中的医生信息及其所看病人信息。
- 查询Medicine表的所有药品的药品编号,药名,数量(标题为“库存”)以及该药品被医生开出的金额合计(标题为“总金额”,用价格*数量求得)
嵌套查询
- 用not in和not exist两种方法查询prescription表里没有开出的medicine表的药品信息。
- 查询超过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")
Comments | NOTHING