queries.sql

卡耐基 梅隆大学软件工程 第七门课程数据库管理系统ssd7 练习3

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 name,isbn,O.callnumber,SUM(qty) as qty 
FROM TITLE4 T,ORDERED4 O,PURCHASE_ORDER4 P
WHERE T.callnumber = O.callnumber AND O.ponum = P.ponum
GROUP BY O.callnumber,qty,name,isbn
HAVING COUNT(O.callnumber) > 1 AND SUM(qty) > 10
ORDER BY qty DESC

3.
SELECT M.libid,fname
FROM MEMBER4 M,READ_BY4 R
WHERE M.libid = R.libid 
GROUP BY M.libid,fname
HAVING SUM(timesread) < (SELECT SUM(timesread) FROM READ_BY4) * 0.05
ORDER BY SUM(timesread) DESC

4.
SELECT DISTINCT fname,M.libid
FROM MEMBER4 M,READ_BY4 R
WHERE M.libid = R.libid AND M.Gender = 'F' AND R.timesread > 1
ORDER BY fname

5.
SELECT name, AVG(P.qty)
FROM SUPPLIER4 S,ORDERED4 O,PURCHASE_ORDER4 P
WHERE O.ponum = P.ponum AND O.supplier_id = S.supplier_ID 
GROUP BY S.supplier_id,S.name
HAVING AVG(qty) > (SELECT AVG(qty) FROM PURCHASE_ORDER4)

6.
SELECT fname,M.libid
FROM MEMBER4 M,READ_BY4 R
WHERE M.libid = R.libid AND Gender = 'M' AND Join_date < TO_DATE('1995/10/10','YYYY/MM/DD')
GROUP BY M.libid,M.fname
HAVING COUNT(M.libid) <= 5 AND MAX(timesread) < 2
ORDER BY fname