queries.sql

icarnegie上数据库课程中的exercise3的答案

--5.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'

--5.2
SELECT TITLE4.Name, TITLE4.ISBN, TITLE4.CallNumber, SUM(PURCHASE_ORDER4.Qty) SumOfQty
FROM TITLE4, ORDERED4, PURCHASE_ORDER4
WHERE TITLE4.CallNumber = ORDERED4.CallNumber AND ORDERED4.PoNum = PURCHASE_ORDER4.PoNum
GROUP BY TITLE4.Name, TITLE4.ISBN, TITLE4.CallNumber, ORDERED4.CallNumber
HAVING SUM(PURCHASE_ORDER4.Qty) > 10 AND COUNT(ORDERED4.CallNumber) > 1 
ORDER BY SUM(PURCHASE_ORDER4.Qty) DESC

--5.3
SELECT MEMBER4.libid , MEMBER4.fname ,SUM(READ_BY4.TimesRead) SumOfTimesRead
FROM MEMBER4 ,READ_BY4
WHERE MEMBER4.libid = READ_BY4.LibId
GROUP BY MEMBER4.libid , MEMBER4.fname 
HAVING SUM(READ_BY4.TimesRead)<(SELECT SUM (TimesRead)*0.05
								FROM READ_BY4)
ORDER BY SumOfTimesRead DESC

--5.4
SELECT DISTINCT MEMBER4.Fname , MEMBER4.libid
FROM MEMBER4 , READ_BY4
WHERE MEMBER4.Gender ='F' AND MEMBER4.libid = READ_BY4.LibId AND READ_BY4.TimesRead>1
ORDER BY MEMBER4.Fname 

--5.5
SELECT S.Name , AVG (Qty) AvgOfQty
FROM SUPPLIER4 S , PURCHASE_ORDER4 P , ORDERED4 O
WHERE S.Supplier_Id = O.Supplier_Id AND O.PoNum = P.PoNum
GROUP BY S.Supplier_Id, S.Name
HAVING AVG(Qty) > (SELECT AVG(Qty)
				   FROM PURCHASE_ORDER4)

--5.6
SELECT DISTINCT M.Fname, M.LibId, COUNT(R.TimesRead) NumOfBooksRead
FROM MEMBER4 M, READ_BY4 R
WHERE M.Gender = 'M' AND M.Join_Date < '1995-10-10' AND M.LibId = R.LibId
GROUP BY R.LibId, M.Fname, M.LibId
HAVING COUNT(R.TimesRead) <= 5 AND MAX(R.TimesRead) <= 1
ORDER BY M.Fname