queries.sql

ssd7 ex3 答案

1.

SELECT libid,fname,join_date
FROM member4
WHERE ((libid LIKE 'A#%B%'ESCAPE'#' ) OR 
      (libid LIKE 'A&B%')) AND
       join_date <='1997-11-30';

















2.

SELECT name,isbn,title4.callnumber
FROM title4,ordered4,purchase_order4
WHERE title4.callnumber=ordered4.callnumber AND
      ordered4.ponum=purchase_order4.ponum
GROUP BY title4.callnumber,title4.name,title4.isbn
HAVING COUNT (*)>1 AND SUM(purchase_order4.qty)>10
ORDER BY SUM(purchase_order4.qty) DESC;














3.

 SELECT libid,fname
 FROM book4,member4
 WHERE book4.borrowerid=member4.libid
 GROUP BY member4.libid,member4.fname
 HAVING COUNT(*) >
 (SELECT COUNT(*)
  FROM book4)*0.05
 ORDER BY COUNT(*);
 







4.
 SELECT DISTINCT member4.libid,fname
 FROM member4,read_by4
 WHERE member4.libid=read_by4.libid AND
       gender='F' AND
       timesread>1
 ORDER BY fname;
5.
 SELECT name,AVG(qty)
 FROM supplier4,purchase_order4,ordered4
 WHERE supplier4.supplier_id=ordered4.supplier_id AND
       purchase_order4.ponum=ordered4.ponum 
 GROUP BY supplier4.name
 HAVING AVG(qty)>(SELECT AVG(qty)
		  FROM purchase_order4);
6.



 SELECT fname,member4.libid
 FROM member4,read_by4
 WHERE member4.libid=read_by4.libid AND
       gender='M' AND
       join_date <'1995-10-10' AND
       timesread<2
 GROUP BY member4.fname,member4.libid
 HAVING COUNT(callnumber)<5
 ORDER BY fname;