?? queries.sql
字號:
1.
select libid,fname,join_date
from member4
where ((libid like 'A#%B%'escape'#' ) or (libid like 'A&B%'escape'#' )) and join_date <=to_date('1997-11-30','YYYY-MM-DD');
2.
select title4.name,title4.isbn,title4.callnumber
from title4,ordered4,purchase_order4
where title4.callnumber=ordered4.callnumber and ordered4.ponum=purchase_order4.ponum
group by title4.name,title4.isbn,title4.callnumber
having count(ordered4.supplier_id)>1 and sum(purchase_order4.qty)>10
order by sum(purchase_order4.qty) desc;
3.
select member4.libid,member4.fname
from member4,read_by4
where member4.libid=read_by4.libid
group by member4.libid,member4.fname
having sum(read_by4.timesread)<(0.05 *(select sum(timesread)
from read_by4))
order by sum(read_by4.timesread);
4.
select distinct member4.libid,member4.fname
from member4,read_by4
where member4.gender = 'F' and member4.libid = read_by4.libid and read_by4.timesread > 1
order by member4.fname;
5.
select supplier4.name ,avg(purchase_order4.qty) as myave
from supplier4,ordered4,purchase_order4
where supplier4.supplier_id=ordered4.supplier_id and ordered4.ponum=purchase_order4.ponum
group by supplier4.name
having avg(purchase_order4.qty)>(select avg(purchase_order4.qty)
from purchase_order4);
6.
select distinct member4.fname,read_by4.libid
from member4,read_by4
where member4.libid=read_by4.libid and member4.gender = 'M' and join_date < TO_DATE('1995-10-10', 'YYYY-MM-DD')
and read_by4.timesread < 2
group by read_by4.libid,member4.fname
having count(read_by4.callnumber) <= 5
order by fname,libid ASC;
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -