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
FROM TITLE4 t,ORDERED4 o,PURCHASE_ORDER4 p
WHERE t.CallNumber=o.CallNumber AND o.PoNum=p.PoNum
GROUP BY t.Name,t.ISBN,t.CallNumber
HAVING COUNT(o.Supplier_Id)>1 AND SUM(p.Qty)>10
ORDER BY SUM(p.Qty) desc;                                                       

/*3*/
SELECT m.LibId,m.Fname
FROM MEMBER4 m,READ_BY4 r
WHERE m.LibId=r.LibId
GROUP BY m.LibId,m.Fname
HAVING SUM(r.TimeSread)<(SELECT SUM(TimeSread)
			 FROM read_by4)*0.05;

/*4*/
SELECT DISTINCT m.Fname,m.LibId
FROM MEMBER4 m,READ_BY4 r
WHERE m.LibId=r.LibId AND m.Gender='F' AND r.TimesRead>1

/*5*/
SELECT s.Name,AVG(p.Qty) AS avgNum
FROM SUPPLIER4 s,ORDERED4 o,PURCHASE_ORDER4 p
WHERE s.Supplier_Id=o.Supplier_Id AND o.PoNum=p.PoNum
GROUP BY s.Name
HAVING AVG(p.Qty)>(SELECT AVG(Qty)
               FROM PURCHASE_ORDER4);

/*6*/
SELECT m.Fname,m.LibId
FROM MEMBER4 m,READ_BY4 r
WHERE m.Gender='M' 
      AND m.join_date<=TO_DATE('1995/10/10', 'YYYY/MM/DD') 
      AND m.LibId=r.LibId 
      AND r.TimesRead<2
GROUP BY m.LibId,m.Fname
HAVING COUNT(r.CallNumber) <= 5
ORDER BY m.Fname,m.LibId ASC;