queries.sql

ssd7 exercise 3 ,绝对有用~欢迎下载

/*
#@author:wangzhesi
#053597
*/

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

--3:
SELECT MEMBER4.libid, MEMBER4.fname
FROM MEMBER4, READ_BY4
WHERE READ_BY4.libid = MEMBER4.libid
GROUP BY MEMBER4.libid, MEMBER4.fname
HAVING SUM(READ_BY4.timeread) < 0.05 * (SELECT SUM(READ_BY4.timeread) 
                                        FROM READ_BY4)
ORDER BY SUM(READ_BY4.timeread);

--4:
SELECT MEMBER4.fname, MEMBER4.libid
FROM MEMBER4, READ_BY4
WHERE READ_BY4.timeread > 1 AND MEMBER4.gender = 'F' AND READ_BY4.libid = MEMBER4.libid
ORDER BY MEMBER4.fname;

--5:
SELECT SUPPLIER4.name, AVG(PURCHASE_ORDER4.qty) AS avg
FROM SUPPLIER4, PURCHASE_ORDER4, ORDERED4
WHERE ORDERED4.ponum = PURCHASE_ORDER4.ponum AND ORDERED4.supplier_id = SUPPLIER4.supplier_id
GROUP BY SUPPLIER4.name
HAVING AVG(PURCHASE_ORDER4.qty) > (SELECT AVG(PURCHASE_ORDER4.qty)
                                     FROM PURCHASE_ORDER4);
--6:
SELECT DISTINCT MEMBER4.fname, READ_BY4.libid
FROM MEMBER4, READ_BY4 
WHERE READ_BY4.libid = MEMBER4.libid AND MEMBER4.gender = 'M' 
      AND join_date < TO_DATE('1995/10/10', 'YYYY/MM/DD') 
      AND READ_BY4.timeread < 2 
GROUP BY MEMBER4.fname, READ_BY4.libid
HAVING COUNT(READ_BY4.callnumber) <= 5
ORDER BY MEMBER4.fname, READ_BY4.libid ASC;