queries.sql

自己做的一些练习答案

1.
select libid,fname,join_date from member4 where
  ((libid like 'A%B%' ) or 
   (libid like 'A&B%' ));

2.
select title4.name,title4.isbn,title4.callnumber
   from title4,ordered4,purchase_order4
     where title4.callnumber=ordered4.callnumber and 
       ordered4.ponum=purchase_order4.ponum
        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 myavg
  from supplier4,ordered4,purchase_order4
   where supplier4.supplier_id=ordered4.supplier_id and 
     ordered4.ponum=purchase_order4.ponum
   group by supplier4.name;

6.
select  m4.fname,r4.libid  from member4 m4,read_by4 r4
  where m4.libid=r4.libid and m4.gender = 'M' and 
   join_date < TO_DATE('1995-10-10', 'YYYY-MM-DD') 
   and r4.timesread < 2  group by r4.libid,m4.fname
   having count(r4.callnumber) <= 5 order by fname,libid ASC;