queries.sql

ssd7 exercise 3 ,绝对有用~欢迎下载

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;