queries.sql

ssd7的练习3

1.
Answer:
	Select libid, fname, join_date
	From member4
	where (libid like 'A&%B%' escape '&' or libid like 'A&B%')
	  and join_date < to_date('1997/11/30', 'YYYY/MM/DD');

2.
Answer:
	Select distinct name, isbn, callnumber, sum(qty)
	From title4  natural join ordered4 natural join purchase_order4
	where title4.callnumber in (select callnumber
                       from purchase_order4 natural join ordered4
                       group by callnumber
                       having sum(qty)>10)
	group by name,isbn,callnumber
	having count(Supplier_Id)>1
	order by sum(qty) asc;

3.
Answer:
	Select libid, fname, sum(timesread)
	From member4 natural join read_by4
	group by libid,fname
	having sum(timesread) < (select 0.05*sum(timesread)
                         from read_by4)
	order by sum(timesread);

4.
Answer:
	Select libid, fname
	From member4 
	Where member4.gender='F'
	and member4.libid in (select libid
                from read_by4
                where timesread>1)
	order by fname;

5.
Answer:
	Select fname, supplier_id, avg(qty)  
	From supplier4 natural join ordered4 o natural join purchase_order4 p
	group by supplier4.supplier_id, supplier4.fname
	having avg(qty)>(select avg(qty) 
                 from purchase_order4)
	order by 3;

6.
	select fname, libid,count(distinct isbn), timesread
	from member4  natural join title4 t natural join read_by4 r
	where member4.Gender='M' 
	and Join_date<=to_date('1995/10/01', 'YYYY/MM/DD') 
	and timesread<2
	group by libid,fname,timesread
	having count(distinct isbn)<=5;