queries.sql

SSD7 EXERCISE3 答案绝对正确 放心下吧

/*1*/
select libid,fname,join_date
from member4
where  libid LIKE 'A/%B%' escape '/' and join_date<='1997-11-30'
union
select libid,fname,join_date
from member4
where  libid LIKE 'A&B%'  and join_date<='1997-11-30';


/*2*/
select distinct title4.name,title4.ISBN,title4.callnumber
from title4 natural join ordered4 natural join purchase_order4
where title4.callnumber in
     (
       select ordered4.callnumber
       from  ordered4 natural join purchase_order4
       group by callnumber
       having sum(qty)>10 and count(supplier_id)>1   
       order by sum(qty) desc   
     );


/*3*/
select member4.libid,member4.fname
from member4 natural join read_by4
group by member4.libid,member4.fname
having sum(read_by4.timesread)<
      (
        select 0.05*sum(read_by4.timesread)
        from read_by4
      )
order by sum(read_by4.timesread) asc;


/*4*/
select member4.libid,member4.fname
from member4 
where member4.gender='F' and member4.libid in
      (
        select member4.libid
        from member4 natural join read_by4
        where read_by4.timesread>1
      )
order by member4.fname;

/*5*/
select distinct supplier4.name,avg(purchase_order4.qty)
from supplier4 natural join ordered4 natural join purchase_order4
group by supplier4.name
having avg(purchase_order4.qty)>(select avg(purchase_order4.qty)
                                 from purchase_order4);


/*6*/
select member4.fname,member4.libid,count(read_by4.timesread)
from member4 natural join read_by4
where member4.gender='M' and member4.join_date<='1995-10-10' 
      and member4.libid not in
     (
         select read_by4.libid
         from read_by4
         where read_by4.timesread>1
     )
group by member4.fname,member4.libid
having count(read_by4.timesread)<=5
order by member4.fname;