第八章

select * from 图书表

select  图书号,书名,作者,出版社,打折价=单价*0.7
from 图书表

select distinct 读者号
from 借阅表

select *
from 图书表
    where 出版社='电子工业出版' or 出版社='科学出版社' or 出版社='人民邮电出版社'

select 读者号,姓名
from 读者表
    where 姓名 like '_建_' and len(姓名)=3

select 读者号, 姓名
from 读者表
    where 姓名 like '王%'


select *
from 借阅表
    where 归还日期 is  null

select 书名,单价
from 图书表
    where 单价>20 and 单价<30

select 人数 =count(distinct 读者号)
from 读者表

select 平均值=avg(单价),最高价=max(单价),最低价=min(单价)
from 图书表
    where 出版社='电子工业出版社'

select 读者号,总本数=COUNT(图书号)
from 借阅表
    group by  读者号 having count(图书号)>2
    order by count(图书号) desc
    
select 读者表.读者号,读者表.姓名,图书表.书名,借阅表.归还日期,借阅表.借出日期
from 读者表,借阅表,图书表
    where 读者表.读者号=借阅表.读者号
    and 借阅表.图书号=图书表.图书号

select 读者表.读者号,姓名,书名,出版社,借出日期,归还日期
from 读者表,借阅表,图书表
    where 读者表.读者号=借阅表.读者号
    and 借阅表.图书号=图书表.图书号
and 出版社='电子工业出版社' and 书名 like '%数据库%'


select 借阅表.读者号,姓名=max(姓名),借阅本数=count(借阅表.读者号)
from 读者表,借阅表,图书表
    where 读者表.读者号=借阅表.读者号
    and 借阅表.图书号=图书表.图书号
    and 出版社='电子工业出版社'
    group by 借阅表.读者号 having count(借阅表.图书号)>=1
    order by count(借阅表.读者号) desc

select 姓名
from 读者表 
    where 办公电话 = (select 办公电话
                        from 读者表
                        where 姓名='王平'
                        )

select 图书号,书名,出版社
from 图书表
    where 单价<(select avg(单价)
                from 图书表
                )


select 书名,单价
from 图书表 
    where 出版社='科学出版社'and 单价>all(select 单价
                                        from 图书表
                                            where 出版社='电子工业出版'
                                        )

select 借阅表.读者号,姓名,借阅本数=count(借阅表.读者号) into #tmp4
from 读者表,借阅表,图书表
    where 读者表.读者号=借阅表.读者号
    and 借阅表.图书号=图书表.图书号
    and 出版社='电子工业出版社'
    group by 借阅表.读者号,姓名  having count(借阅表.图书号)>=1
    order by count(借阅表.读者号) desc
    
select 借阅表.读者号,姓名,书名,借阅本数=count(借阅表.读者号) into #tmp5
from 读者表,借阅表,图书表
    where 读者表.读者号=借阅表.读者号
    and 借阅表.图书号=图书表.图书号
    and 出版社='电子工业出版社'
    group by 借阅表.读者号,姓名,书名 having count(借阅表.图书号)>=1
    order by count(借阅表.读者号) desc

select *
from #tmp4

select *
from #tmp5

select #tmp4.读者号,#tmp4.姓名,书名,#tmp5.借阅本数,总借阅本数=#tmp4.借阅本数
from #tmp4,#tmp5
    where #tmp4.读者号=#tmp5.读者号