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. 查出所有張姓學(xué)生借過的書名
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. 查出各年齡借書數(shù)量
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月份沒有借過書的學(xué)生
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);相當(dāng)于
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. 查出每月的熱門書籍（當(dāng)月被借閱超過一次）
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. 對(duì)書籍表進(jìn)行分頁查詢，根據(jù)rownum偽列和當(dāng)前頁數(shù)currentPage進(jìn)行查詢如叼；
假設(shè)每頁最多顯示5條記錄冰木，currentPage = 2，寫出查詢語句
select * from
(select b.*,rownum rn from iweb_tbl_book b
where rownum <=10)
where rn > 5;
8. 刪除重復(fù)數(shù)據(jù)（除主鍵列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型片酝。假設(shè)借書價(jià)格為 0.1元/本/天，計(jì)算每人借書應(yīng)繳費(fèi)用,并更新到列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;