ssd7 exercise3答案

SELECT libid,fname,Join_date FROM MEMBER4 WHERE Join_date < 19971130 AND (libid LIKE 'A\%B%' or libid LIKE 'A&B%'); #---------------------------------------------------------------------------------------- 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; #---------------------------------------------------------------------------------------- SELECT s.libid,s.fname FROM (SELECT m.libid,m.fname,COUNT(r.callnumber) AS readcount FROM member4 m LEFT JOIN read_by4 r ON m.libid=r.libid GROUP BY libid) AS s, (SELECT COUNT(callnumber) AS bookcount FROM title4) AS q WHERE s.readcount<q.bookcount*0.05; #---------------------------------------------------------------------------------------- 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; #---------------------------------------------------------------------------------------- SELECT a.name,a.supaverageqty FROM (SELECT s.supplier_id,s.name,COUNT(o.ponum) AS suppocount,SUM(p.qty) AS supqtycount,(SUM(p.qty)/COUNT(o.ponum)) AS supaverageqty FROM supplier4 s,ordered4 o,purchase_order4 p WHERE s.supplier_id=o.supplier_id AND o.ponum=p.ponum GROUP BY s.supplier_id) AS a, (SELECT COUNT(ponum) AS allponumcount,SUM(qty) AS allqtycount, SUM(qty)/COUNT(ponum) AS allaverageqty FROM purchase_order4) AS b WHERE a.supaverageqty>b.allaverageqty; #---------------------------------------------------------------------------------------- SELECT a.fname,a.libid FROM (SELECT m.libid,m.fname,r.callnumber,r.timesread FROM member4 m,read_by4 r WHERE m.libid=r.libid AND m.gender='M' AND m.Join_date<19951010 AND timesread<2 ) AS a GROUP BY a.fname HAVING COUNT(a.callnumber)<5 ORDER BY a.fname;