1. 查出所有男生的借阅记录，包括姓名，书名
select name,title
from iweb_tbl_record r,iweb_tbl_book b,iweb_tbl_user u
where r.book_id = b.id and r.user_id = u.id and u.sex = '男';
2. 查出所有张姓学生借过的书名
select title
from iweb_tbl_record r,iweb_tbl_user u,iweb_tbl_book b
where r.user_id = u.id and r.book_id = b.id and u.name like '张%';
3. 查出各年龄借书数量
select age,count(1) from
(select * from iweb_tbl_record r,iweb_tbl_user u
where r.user_id = u.id)
group by age order by age;
4. 查出所有10月份没有借过书的学生
select distinct u.id,name
from iweb_tbl_record r,iweb_tbl_user u
where to_char(r.borrowdate,'mm') != 10
or u.id not in (select user_id from iweb_tbl_record);相当于
select distinct u.id,name
from iweb_tbl_record r,iweb_tbl_user u
where to_char(r.borrowdate,'mm') != 10 or u.id != r.user_id;
5. 查出2012年10月的所有借阅记录，包括姓名，书名
select name,title
from iweb_tbl_record r,iweb_tbl_book b,iweb_tbl_user u
where r.book_id = b.id and r.user_id = u.id
and to_char(r.borrowdate,'yyyy-mm') = '2018-03';
6. 查出每月的热门书籍（当月被借阅超过一次）
select distinct r.book_id,to_char(r.borrowdate,'mm')
from iweb_tbl_record r,
(select book_id,count(1) from iweb_tbl_record
group by book_id
having count(1) > 1) m
where m.book_id = r.book_id;
7. 对书籍表进行分页查询，根据rownum伪列和当前页数currentPage进行查询；
假设每页最多显示5条记录，currentPage = 2，写出查询语句
select * from
(select b.*,rownum rn from iweb_tbl_book b
where rownum <=10)
where rn > 5;
8. 删除重复数据（除主键列ID值不同，其它列相同）
select * from iweb_tbl_book
delete from iweb_tbl_book where id in
(select id from
(select b.*,
row_number() over (partition by isbn order by id) rn
from iweb_tbl_book b)where rn >1)
9. 给表 records 增加列fee, number型。假设借书价格为 0.1元/本/天，计算每人借书应缴费用,并更新到列fee中
alter table iweb_tbl_record add fee number;
select r.*, (returndate - borrowdate)* 0.1 from iweb_tbl_record r;
update iweb_tbl_record set fee = (returndate - borrowdate)*0.1;