--a
SELECT DISTINCT B.AUTHOR
FROM BOOKS B,ORDERS O,CUSTOMERS C
WHERE B.BID=O.BID AND O.CID=C.CID AND O.QUANTITY>=50 AND C.ZIPCODE='02125';


--b
SELECT C.CNAME
FROM ORDERS O,CUSTOMERS C
WHERE O.CID=C.CID AND C.CID NOT IN
(SELECT O1.CID FROM BOOKS B1,ORDERS O1
	WHERE B1.BID=O1.BID AND B1.PRICE>=100);

--c
SELECT O.CID, AVG(B.PRICE)
FROM BOOKS B,ORDERS O
WHERE B.BID=O.BID
GROUP BY O.CID 
HAVING 20 <= (
	SELECT COUNT(*) 
	FROM ORDERS O1, BOOKS B1
	WHERE O1.BID=B1.BID AND O1.CID=O.CID AND B1.PRICE >= 20);


--d
SELECT C.CNAME
FROM CUSTOMERS C
WHERE NOT EXISTS(
	SELECT B.BID FROM BOOKS B WHERE B.AUTHOR='Edgar Codd'
	MINUS
	SELECT O.BID FROM ORDERS O WHERE O.CID=C.CID
);

--e
SELECT TEMP.AUTHOR
FROM(
	SELECT O.BID, B.AUTHOR, SUM(O.QUANTITY) AS SOLD_COPIES
	FROM ORDERS O, BOOKS B
	WHERE B.BID=O.BID 
	GROUP BY O.BID, B.AUTHOR) TEMP
WHERE TEMP.SOLD_COPIES = (SELECT MAX (TEMP.SOLD_COPIES) 
	FROM(
        	SELECT O.BID, B.AUTHOR, SUM(O.QUANTITY) AS SOLD_COPIES
	      	FROM ORDERS O, BOOKS B
		WHERE B.BID=O.BID
		GROUP BY O.BID, B.AUTHOR) TEMP);

--f
SELECT TEMP.CNAME
FROM(
        SELECT C.CID, C.CNAME, SUM(O.QUANTITY*B.PRICE) AS PURCHASED_AMOUNT
        FROM ORDERS O, BOOKS B, CUSTOMERS C	
        WHERE B.BID=O.BID AND O.CID=C.CID
	GROUP BY C.CID, C.CNAME) TEMP
WHERE TEMP.PURCHASED_AMOUNT = (SELECT MAX (TEMP.PURCHASED_AMOUNT)
	FROM(
	        SELECT C.CID, C.CNAME, SUM(O.QUANTITY*B.PRICE) AS PURCHASED_AMOUNT
	        FROM ORDERS O, BOOKS B, CUSTOMERS C
	        WHERE B.BID=O.BID AND O.CID=C.CID
	        GROUP BY C.CID, C.CNAME) TEMP);




