queries.sql

卡耐基的SSD7-Exercise3~~答案部分~~

/*1*/
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*/
select distinct t.name,t.isbn,t.callnumber,sum(qty)
from title4 t natural join ordered4 o natural join purchase_order4 p
where t.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) desc;


/*3*/
SELECT MEMBER4.libid, MEMBER4.fname
FROM MEMBER4, READ_BY4
WHERE READ_BY4.libid = MEMBER4.libid
GROUP BY MEMBER4.libid, MEMBER4.fname
HAVING SUM(READ_BY4.timeread) < 0.05 * (SELECT SUM(READ_BY4.timeread) 
                                        FROM READ_BY4)
ORDER BY SUM(READ_BY4.timeread);


/*4*/
SELECT DISTINCT M.FNAME, R.LIBID
FROM MEMBER4 M, READ_BY4 R
WHERE M.GENDER = 'F' AND M.LIBID = R.LIBID AND R.TIMEREAD > 1		
ORDER BY M.FNAME;


/*5*/
SELECT SUPPLIER4.name, AVG(PURCHASE_ORDER4.qty) AS avg
FROM SUPPLIER4, PURCHASE_ORDER4, ORDERED4
WHERE ORDERED4.ponum = PURCHASE_ORDER4.ponum AND ORDERED4.supplier_id = SUPPLIER4.supplier_id
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.timeread < 2
group by read_by4.libid,member4.fname
having count(read_by4.callnumber) <= 5
order by fname,libid;