queries.sql

It includes information about book titles in the library, information about library members, about t

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,callnumber
FROM (SELECT title4.name, title4.isbn, title4.callnumber,sum(purchase_order4.qty) 
	FROM title4, ordered4, purchase_order4 
	where title4.callnumber = ordered4.callnumber AND 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
     ) AS FOO;
------------------------------------------------------------------
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(r1.timesread)
			 FROM read_by4 r1)*0.05
ORDER BY SUM(r.timesread) DESC;
--------------------------------------------------------------------
4.
SELECT DISTINCT m.fname,m.libid
FROM member4 m,read_by4 r
WHERE m.gender= 'F' AND m.libid = r.libid AND r.timesread > 1
ORDER BY m.fname;
--------------------------------------------------------------------
5.
SELECT name, AVG(qty)

FROM supplier4 s, ordered4 o, purchase_order4 p

WHERE p.ponum = o.ponum AND o.supplier_id = s.supplier_ID

GROUP BY s.supplier_id, s.name

HAVING AVG(qty) > 1;
----------------------------------------------------------------------
6.
SELECT fname, m.libid

FROM Member4 m, read_by4 r

WHERE gender in ('M', 'm') AND join_date < TO_DATE('1995/10/10', 'YYYY/MM/DD') AND m.libid = r.libid AND timesread <=1

GROUP BY m.libid, m.fname, m.libid

HAVING COUNT(*) <= 5;