实验类型

验证型实验

实验目的

  • 掌握查询语句的一般格式；
  • 熟练掌握单表查询、连接查询、集合查询、统计查询和嵌套查询。

实验内容

  • 单表查询
  • 连接查询
  • 嵌套查询
  • 集合查询

实验步骤

  1. 查询“红楼梦”目前可借的各图书编号，及所属版本信息。（是否借出为‘否‘的图书）
  2. 查找高等教育出版社的所有书目及单价，结果按单价降序排序。
  3. 统计“红楼梦”各版的藏书数量（ISBN不同则版本不同）。
  4. 查询学号“20061234”号借书证借阅未还的图书的信息。
  5. 查询各个出版社的图书最高单价、平均单价。
  6. 要查询借阅了两本和两本以上图书的读者的个人信息。
  7. 查询“王菲”的单位、所借图书的书名和借阅日期。
  8. 查询每类图书的册数和平均单价。
  9. 统计从未借书的读者人数。
  10. 统计参与借书的人数。
  11. 找出所有借书未还的读者的信息及所借图书编号及名称。
  12. 检索书名是以“红”开头的所有图书的书名和作者。
  13. 查询各图书的罚款总数。
  14. 查询借阅及罚款分类信息，如果有罚款则显示借阅信息及罚款名称、罚金，如果没有罚款则罚款名称、罚金显示空（左外连接）
  15. 查询借阅了所有“文学”类书目的读者的姓名、单位。

实验扩展

  1. 在书目关系中新增“出版年份”，并在该属性下添加数据。（使用SQL完成）

    ISBN 书名 作者 出版单位 出版年份 单价 图书分类号
    7040195836 数据库系统概论 王珊 高等教育出版社 2005 39.00 200
    9787508040110 红楼梦 曹雪芹 人民出版社 1983 20.00 100
    9787506336239 红楼梦 曹雪芹 作家出版社 2008 34.30 100
    9787010073750 心学之路 张立文 人民出版社 2009 33.80 300

  2. 求总藏书量、藏书总金额，总库存册数、最高价、最低价。

  3. 列出藏书在5本以上的书目（书名、作者、出版社、出版年份）。

  4. 列出年份最久远的书?

  5. 目前实际已借出多少册书？

  6. 哪一年的图书最多？

  7. 哪本借书证未归还的图书最多？

  8. 平均每本借书证的借书册数。

  9. 哪个单位的读者平均借书册数最多？

  10. 最近两年都未被借过的书。
    注意：Oracle 系统日期时间 sysdate，更多关于oracle日期函数请自行查阅。

  11. 今年未借过书的借书证。

实验结果

实验内容

  1. 查询“红楼梦”目前可借的各图书编号，及所属版本信息。（是否借出为‘否‘的图书）
1
2
3
4
5
select *
from 书目,图书
where 书目.ISBN=图书.ISBN
and 书名='红楼梦'
and 是否借出='否'
  1. 查找高等教育出版社的所有书目及单价，结果按单价降序排序。
1
2
3
4
select *
from 书目
where 出版单位='高等教育出版社'
order by 单价 desc;
  1. 统计“红楼梦”各版的藏书数量（ISBN不同则版本不同）。
1
2
3
4
select count(ISBN),ISBN
from 书目
where 书名='红楼梦'
group by ISBN;
  1. 查询学号“20061234”号借书证借阅未还的图书的信息。
1
2
3
4
5
6
7
select 借书证号,书名,书目.ISBN ,出版单位,作者,单价,    图书分类号
from 书目,借阅,图书
where 书目.ISBN=图书.ISBN
and 借阅.图书编号=图书.图书编号 
and 是否借出='是'
and  归还日期 is NULL
and 借书证号=20061234;
  1. 查询各个出版社的图书最高单价、平均单价。（同上）
1
2
3
select 出版单位,max(单价),avg(单价)
from 书目
group by 出版单位;
  1. 要查询借阅了两本和两本以上图书的读者的个人信息。
1
2
3
4
5
6
7
select * from 读者 
where 借书证号 in
(
select 借书证号
from 借阅
group by 借书证号 
having count(借书证号)>=2);
  1. 查询“王菲”的单位、所借图书的书名和借阅日期。
1
2
3
4
5
6
select 单位,书名,借书日期
from 读者,借阅,书目,图书
where  姓名='王菲'
and 读者.借书证号=借阅.借书证号
and 借阅.图书编号=图书.图书编号
and 图书.ISBN=书目.ISBN ;
  1. 查询每类图书的册数和平均单价。
1
2
3
select count(图书分类号),avg(单价)
from 书目
group by 图书分类号;
  1. 统计从未借书的读者人数。
1
2
3
4
select count(姓名)
from 读者
where not exists (select * from 借阅 
where 读者.借书证号=借阅.借书证号);
  1. 统计参与借书的人数。
1
2
3
4
5
select count(姓名)
from 读者
where exists (
select * from 借阅 
where 读者.借书证号=借阅.借书证号);
  1. 找出所有借书未还的读者的信息及所借图书编号及名称。
1
2
3
4
5
6
select distinct 借阅.借书证号,姓名,单位,性别,地址,联系电话,身份证编号,借阅.图书编号,书名
from 借阅,书目,读者,图书
where 借阅.借书证号=读者.借书证号
and 借阅.图书编号=图书.图书编号
and 书目.ISBN=图书.ISBN
and 归还日期 is null;
  1. 检索书名是以“红”开头的所有图书的书名和作者。
1
2
3
select 书名,作者
from 书目
where 书名 like '红%';
  1. 查询各图书的罚款总数。（分组，图书编号+罚款总数）
1
2
3
4
5
select 书目.ISBN,sum(罚金)
from 借阅,罚款分类,书目,图书
where 借阅.罚款分类号=罚款分类.罚款分类号
and 借阅.图书编号=图书.图书编号
group by 书目.ISBN
  1. 查询借阅及罚款分类信息，如果有罚款则显示借阅信息及罚款名称、罚金，如果没有罚款则罚款名称、罚金显示空
1
select * from 借阅  left outer join 罚款分类 on(借阅.罚款分类号=罚款分类.罚款分类号)
  1. 查询借阅了所有“文学”类书目的读者的姓名、单位。（判断条件）
1
2
3
4
5
6
7
8
9
10
11
12
select 读者.姓名,读者.单位
from 读者
where not exists(
      select *
      from 书目,图书分类
      where 书目.图书分类号=图书分类.图书分类号 and 图书分类.类名='文学' and not exists
      (
            select *
            from 借阅,图书
            where 借阅.图书编号=图书.图书编号 and 借阅.借书证号=读者.借书证号 and 书目.ISBN=图书.ISBN
      )
)

实验扩展

  1. 在书目关系中新增“出版年份”，并在该属性下添加数据。（使用SQL完成）
    ISBN 书名 作者 出版单位 出版年份 单价 图书分类号
    7040195836 数据库系统概论 王珊 高等教育出版社 2005 39.00 200
    9787508040110 红楼梦 曹雪芹 人民出版社 1983 20.00 100
    9787506336239 红楼梦 曹雪芹 作家出版社 2008 34.30 100
    9787010073750 心学之路 张立文 人民出版社 2009 33.80 300 
1
2
3
4
5
6
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;

2、求总藏书量、藏书总金额，总库存册数、最高价、最低价。

1
2
3
select count(图书编号),sum(单价),max(单价),min(单价)
from 书目,图书
where 书目.ISBN=图书.ISBN;

3、列出藏书在5本以上的书目（书名、作者、出版社、出版年份）。

1
2
3
4
5
6
7
8
select 书名,作者,出版单位,出版年份
from 书目
where 书目.ISBN in
(
select 图书.ISBN
from 图书
group by 图书.ISBN having count( 图书.ISBN)>5
);

4、列出年份最久远的书?

1
2
3
4
5
6
7
select 书名,作者,出版单位,出版年份
from 书目
where 书目.出版年份 in
(
select min(书目.出版年份)
from 书目
)

5、目前实际已借出多少册书？

1
2
3
select count(借阅.借阅流水号) 借出数量
from 借阅
where 归还日期 is null;

6、哪一年的图书最多？

1
2
3
4
5
6
7
select * from (
select 出版年份,count(出版年份) 数量 
from 图书,书目 
where 图书.ISBN = 书目.ISBN
group by 出版年份 
order by count(出版年份) desc 
) where rownum=1;

7、 哪本借书证未归还的图书最多？

1
2
3
4
5
6
7
select * from (
select 借书证号
from 借阅
where 归还日期 is null
group by 借书证号
order by count(借书证号) desc
) where rownum=1

8、平均每本借书证的借书册数。

1
2
3
select 借书证号,count(借书证号)
from 借阅 
group by 借书证号;

9、哪个单位的读者平均借书册数最多？

1
2
3
4
5
6
7
8
9
select 读者.单位
from 读者
where 读者.借书证号 in (
select 借书证号 from (
select 借书证号, count(借书证号)
from 借阅
group by 借书证号
order by  count(借书证号) desc 
) where rownum=1);

10、最近两年都未被借过的书。

1
2
3
4
5
6
7
select distinct(书目.ISBN),书目.书名,书目.出版单位
from 图书,书目,借阅
where 图书.图书编号=借阅.图书编号
and 书目.ISBN=图书.ISBN
and 借阅.借书日期
not between to_date('20180101','yyyy/mm/dd')
and to_date('20191231','yyyy/mm/dd');