queries.sql

ssd8练习答案

--5
--5.1
SELECT LibId, Fname, Lname FROM MEMBER4
	WHERE LibId LIKE 'A\\%B%' or LibId LIKE 'A&B%' AND Join_date >= to_date('1997/11/30','YYYY/MM/DD');
--5.2
SELECT r.name,r.isbn,r.callnumber      
FROM   (SELECT t.name,t.isbn,t.callnumber,COUNT(o.supplier_id) AS suppliercount,SUM(p.qty) AS qty
        FROM TITLE4 t,ordered4 o,purchase_order4 p
        WHERE t.callnumber=o.callnumber AND o.ponum=p.ponum
        GROUP BY t.isbn
        ORDER BY qty DESC) AS r
WHERE r.suppliercount>1 AND r.qty>10;

--5.3
select m.libid,m.fname,sum(timesread)
from member4 m natural join read_by4
group by libid,fname
having sum(timesread) < (select 0.05*sum(timesread)
                         from read_by4)
order by sum(timesread);

--5.4
SELECT DISTINCT(s.fname),s.libid
FROM (SELECT m.fname,m.libid,r.timesread,r.callnumber,m.gender
       FROM member4 m,read_by4 r
       WHERE m.libid=r.libid AND r.timesread>1) AS s
WHERE s.gender='F'
ORDER BY s.fname;

--5.5
select s.name, s.supplier_id,avg(qty)  
from supplier4 s natural join ordered4 o natural join purchase_order4 p
group by s.supplier_id,s.name
having avg(qty)>(select avg(qty) 
                 from purchase_order4)
order by 3;


--5.6
select m.fname,m.libid,count(distinct isbn),r.timesread
from member4 m natural join title4 t natural join read_by4 r
where m.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;