数据库-实验二-数据查询

--查询“红楼梦”目前可借的各图书编号，及所属版本信息
select 图书编号，出版单位 from 书目,图书 where 书目.isbn=图书.isbn 
and 书名='红楼梦'
and 是否借出='否';

--查找高等教育出版社的所有书目及单价，结果按单价降序排序。
select 书名，单价 from 书目
where 出版单位='高等教育出版社'
order by 单价 desc;

--统计“红楼梦”各版的藏书数量（ISBN不同则版本不同）。
select count(ISBN) from 书目
where 书名='红楼梦'
group by ISBN;

--查询学号“20061234”号借书证借阅未还的图书的信息。
select 借书证号,书名,书目.ISBN ,出版单位,作者,单价,    图书分类号
from 书目,借阅,图书
where 书目.ISBN=图书.ISBN
and 借阅.图书编号=图书.图书编号 
and 是否借出='是'
and  归还日期 is NULL
and 借书证号=20061234;

--查询各个出版社的图书最高单价、平均单价。
select max(单价),avg(单价) from 书目
group by 出版单位;

--要查询借阅了两本和两本以上图书的读者的个人信息。
select * from 读者 where 借书证号 in
(select 借书证号 from 借阅 group by 借书证号 
having count(借书证号)>=2);

--查询“王菲”的单位、所借图书的书名和借阅日期。
select 单位,书名,借书日期
from 读者,借阅,书目,图书
where  姓名='王菲'
and 读者.借书证号=借阅.借书证号
and 借阅.图书编号=图书.图书编号
and 图书.ISBN=书目.ISBN ;

--查询每类图书的册数和平均单价。
select count(图书分类号),avg(单价)
from 书目
group by 图书分类号;

--统计从未借书的读者人数。
select count(姓名)
from 读者
where not exists (select * from 借阅 
where 读者.借书证号=借阅.借书证号);

--统计参与借书的人数。
select count(姓名)
from 读者
where exists (select * from 借阅 
where 读者.借书证号=借阅.借书证号);

--找出所有借书未还的读者的信息及所借图书编号及名称。
select distinct 借阅.借书证号,姓名,单位,性别,地址,联系电话,身份证编号,借阅.图书编号,书名
from 借阅,书目,读者,图书
where 借阅.借书证号=读者.借书证号
and 借阅.图书编号=图书.图书编号
and 书目.ISBN=图书.ISBN
and 归还日期 is null;

--检索书名是以“Internet”开头的所有图书的书名和作者。
select 书名,作者
from 书目
where 书名='Internet%';

--查询各图书的罚款总数。
select sum(罚金) 
from 罚款分类,书目,借阅
where 罚款分类.罚款分类号=借阅.罚款分类号;

--查询借阅及罚款分类信息，如果有罚款则显示借阅信息及罚款名称、罚金，如果没有罚款则罚款名称、罚金显示空（左外连接）
select 罚款名称,罚金,借阅流水号,借书证号,图书编号,借书日期,归还日期,借阅.罚款分类号,备注
from 借阅
left outer join 罚款分类 on(借阅.罚款分类号=罚款分类.罚款分类号);

--查询借阅了所有“文学”类书目的读者的姓名、单位。
select 姓名,单位 ,类名
from 读者 ,图书分类 
where 类名='文学';

--扩展训练
---
---
---
/*alter table 书目
add 出版年份 number;
update 书目 set 出版年份=2005 where isbn=7040195836;

update 书目 set 出版年份=1983 where isbn=9787508040110;

update 书目 set 出版年份=2008 where isbn=9787506336239;

update 书目 set 出版年份=2009 where isbn=9787010073750;*/

--求总藏书量、藏书总金额、最高价、最低价。
select count(图书编号),sum(单价),max(单价),min(单价)
from 书目,图书
where 书目.ISBN=图书.ISBN;

--列出藏书在5本以上的书目（书名、作者、出版社、出版年份）。
select 书名,作者,出版单位,出版年份
from 书目
where 书目.isbn in(   select 图书.isbn from 图书 group by 图书.isbn having count( 图书.isbn)>5 );

--列出年份最久远的书?
select 书名,作者,出版年份
from 书目
where  书目.ISBN in (select min(书目.出版年份) from 书目);

--目前实际已借出多少册书？
select count(借阅.借阅流水号) 借出数量
 from 借阅 where 归还日期 is null;
 
--哪一年的图书最多？
select * from (

select 出版年份,count(出版年份) 数量 
from 图书,书目 
where 图书.isbn=书目.isbn
group by 出版年份 
order by count(出版年份) desc 

) where rownum=1;

--哪本借书证未归还的图书最多？
select 借书证号 
from 读者
where 读者.借书证号 
in(select 图书编号 
from 图书 where
是否借出='否' and 
图书.图书编号 in (select 借阅流水号 from 借阅 
where 归还日期 is null));

--平均每本借书证的借书册数。
select 借书证号,count(借书证号)
from 借阅 
group by 借书证号;

--哪个单位的读者平均借书册数最多？
/*select 读者.单位 from 读者 where 读者.借书证号 in (

select 借书证号 from (

select 借书证号, count(借书证号) from 借阅 group by 借书证号 order by  count(借书证号) desc 

) where rownum=1);*/

--最近两年都未被借过的书。
select * from 图书,书目,借阅 
where 图书.图书编号=借阅.图书编号 
and 书目.isbn=图书.isbn
and 借阅.借书日期 
between to_date('2017-1-1 00:00:00','yyyy-mm-dd hh24:mi:ss') 
and to_date('2018-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss');

--今年未借过书的借书证。
select 读者.借书证号 from 读者 where  not exists(

select 借阅.借书证号 from 借阅 where 借阅.借书日期 
between to_date('2018-1-1 00:00:00','yyyy-mm-dd hh24:mi:ss') 
and to_date('2018-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')

);