select bname,bpub,bpri from book
where bpub = '机械工业出版社' or bpub = '科学出版社'
16）求读者的总人数
select count (*) 总人数 from rea
17）求借阅了图书的读者的总人数
select count (distinct rnum ) 借阅总人数 from br
18）求机械工业出版社图书的平均价格、最高价、最低价。
select AVG (bpri) avg , MAX (bpri) max , MIN (bpri) min from book
where bpub = '机械工业出版社'
19）查询借阅图书本数超过2本的读者号、总本数。并按借阅本数值从大到小排序。
select rnum,COUNT (bnum) 总本数 from br Group by rnum
having COUNT(bnum) >2 order by (count (bnum)) desc
（6）针对以上三个表，用SQL语言完成以下各项多表连接查询、子查询、组
合查询
1）查询读者的基本信息以及他/她借阅的情况。
select rea.* ,br.bnum ,br.brback ,br.brdate from rea,br
where rea.rnum = br.rnum
2）查询读者的读者号、姓名、借阅的图书名、借出日期、归还日期。
select br.bnum ,rea.rname,book.bname,br.brback ,br.brdate from rea,br,book
where rea.rnum = br.rnum and book.bnum=br.bnum
3）查询借阅了机械工业出版社出版,并且书名中包含’数据库’三个字的图书的读者，显示读者号、姓名、书名、出版社，借出日期、归还日期。
select rea.rnum,rea.rname,book.bname,br.brback ,br.brdate from rea,br,book
where book.bname like '%数据库%' and bpub = '机械工业出版社' and rea.rnum = br.rnum and book.bnum=br.bnum
4）查询至少借阅过1本机械工业出版社出版的书的读者的读者号、姓名、书名，借阅本数，并按借阅本数多少降序排列。
select br.rnum,rea.rname,bname,COUNT(br.bnum )本数 from book,rea,br
where rea.rnum = br.rnum and book.bnum = br.bnum and book.bnum = ' 机械工业出版社' Group by br.rnum ,rname,bname
having COUNT(bname) >=1 order by COUNT(br.bnum) desc
5）查询与’王平’的办公电话相同的读者的姓名。
select r1.rname from rea r1,rea r2
where r1.rname = '王平' and r1.rphone = r2.rphone
6）查询办公电话为’88320701’的所有读者的借阅情况，要求包括借阅了书籍的读者和没有借阅的读者，显示他们的读者号、姓名、书名、借阅日期。
select rea.rnum,rname,bname,brdate from book,rea,br
where rphone = '88320701' and rea.rnum = br.rnum
and book.bnum = br.bnum
7）查询所有单价小于平均单价的图书号、书名、出版社
select bnum,bname,bpub from book
where bpri < (select AVG(bpri)
from book )
8）查询’科学出版社’的图书中单价比’机械工业出版社’最高单价还高的的图书书名、单价。
select bname ,bpri from book
where bpub = '科学出版社' and bpri > (select MAX (bpri)
9）查询'科学出版社'的图书中单价比'机械工业出版社'最低单价高的的图书书名、单价。
rom book
where bpub = '机械工业出版社')
select bname ,bpri from book
where bpub = '科学出版社' and bpri > (select Min (bpri)
from book
where bpub = '机械工业出版社')
10）查询已被借阅过并已归还的图书信息。
select * from book
where bnum in ( select bnum
from br
where brback is not null)
11）查询从未被借阅过的图书信息。
select * from book
where bnum not in ( select bnum
from br