queries.sql

SSD7 练习三答案

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 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 m.libid,m.fname,sum(timesread)
FROM MEMBER4 m natural join READ_BY4 r
GROUP BY libid,fname
HAVING sum(timesread) < (select 0.05*sum(timesread)
                         from read_by4)
ORDER BY sum(timesread) asc;

4.
SELECT m.fname,m.libid
FROM MEMBER4 m
WHERE m.gender = 'F' AND m.libid IN (SELECT libid
					FROM read_by4
					WHERE timesread>1)
ORDER BY m.fname;

5.
SELECT s.supplier_id,s.name, avg(qty)
FROM ORDERED4 o natural join PURCHASE_ORDER4 p natural join SUPPLIER4 s
GROUP BY s.supplier_id,s.name
HAVING avg(qty) > (SELECT avg(qty) 
                 FROM PURCHASE_ORDER4);

6.
SELECT m.fname,m.libid,count(distinct isbn),r.timesread
FROM MEMBER4 m natural join READ_BY4 r natural join TITLE4 t
WHERE m.gender='M' 
	AND m.join_date < TO_DATE('1995/10/10', 'YYYY/MM/DD') 
	AND r.timesread < 2
GROUP BY m.fname,m.libid,r.timesread
HAVING count(distinct isbn)<=5;