
CREATE TABLE S (
	SNO	CHAR(2),
	SNAME	VARCHAR(8),
	STATUS	INTEGER,
	CITY	VARCHAR(8));
COPY S FROM 'book_s.table';

CREATE TABLE P (
	PNO	CHAR(2),
	PNAME	VARCHAR(8),
	COLOR	VARCHAR(8),
	WEIGHT	INTEGER,
	CITY	VARCHAR(8));
COPY P FROM 'book_p.table';

CREATE TABLE SP (
	SNO	CHAR(2),
	PNO	CHAR(2),
	QTY	INTEGER);
COPY SP FROM 'book_sp.table';


-- chapter 11 page 139
SELECT FIRST.SNO AS XX, SECOND.SNO AS YY
FROM S AS FIRST, S AS SECOND
WHERE FIRST.CITY = SECOND.CITY AND FIRST.SNO < SECOND.SNO;

-- page 152
SELECT ROUND(AVG(SP.QTY),4) AS AQ, SUM(SP.QTY) AS SQ FROM SP;

-- page 154
SELECT PNO, SUM(SP.QTY) AS PQTY FROM SP GROUP BY SP.PNO ORDER BY SP.PNO;

-- page 155
SELECT DISTINCT SP.PNO FROM SP;

SELECT S.SNO FROM S WHERE S.CITY='Paris' AND S.STATUS > 20;

SELECT S.SNO, P.PNO FROM S,P WHERE S.CITY = P.CITY ORDER BY S.SNO, P.PNO;

-- page 156
SELECT S.SNO, P.PNO FROM S,P WHERE S.CITY > P.CITY ORDER BY S.SNO, P.PNO;

SELECT S.SNO, P.PNO FROM S,P 
WHERE S.CITY = P.CITY AND S.STATUS <> 20
ORDER BY S.SNO, P.PNO;

SELECT DISTINCT S.CITY AS SCITY, P.CITY AS PCITY
FROM S, SP, P
WHERE S.SNO = SP.SNO AND SP.PNO = P.PNO;

SELECT FIRST.SNO AS XX, SECOND.SNO AS YY
FROM S AS FIRST, S AS SECOND
WHERE FIRST.CITY = SECOND.CITY
ORDER BY XX,YY;

SELECT SP.PNO, MAX(SP.QTY) AS XXX, MIN(SP.QTY) AS YYY
FROM SP
WHERE SP.SNO <> 'S1' GROUP BY SP.PNO
ORDER BY SP.PNO;

SELECT SP.PNO FROM SP
GROUP BY SP.PNO HAVING COUNT(*) > 1
ORDER BY SP.PNO;

-- page 157
SELECT P.PNO, 'Weight in grams =' AS TEXT1, P.WEIGHT * 454 AS GMWT, P.COLOR,
	'Max quantity =' AS TEXT2, MAX(SP.QTY) AS MQY
FROM P, SP
WHERE P.PNO = SP.PNO AND (P.COLOR='Red' OR P.COLOR='Blue')
AND SP.QTY > 200
GROUP BY P.PNO, P.WEIGHT, P.COLOR
HAVING SUM(SP.QTY) > 350
ORDER BY P.PNO;

-- page 159
SELECT SNO, SNAME, STATUS, S.CITY, PCT, ROUND(APCT,1) AS apct
FROM S JOIN
	(SELECT SNO, COUNT(*) AS PCT FROM SP 
	 GROUP BY SP.SNO) AS POINTLESS1 USING (SNO) JOIN
	(SELECT CITY, AVG(PCT) AS APCT
	 FROM S JOIN
		(SELECT SNO, COUNT(*) AS PCT FROM SP
		 GROUP BY SNO) AS POINTLESS2 USING (SNO)
	 GROUP BY CITY) AS POINTLESS3 ON (S.CITY=POINTLESS3.CITY)
WHERE PCT > APCT;

-- page 160
SELECT DISTINCT SP.PNO, 
	(SELECT MAX(SPX.QTY) FROM SP AS SPX WHERE SPX.PNO = SP.PNO) AS XXX,
	(SELECT MIN(SPX.QTY) FROM SP AS SPX WHERE SPX.PNO = SP.PNO) AS YYY
FROM SP
WHERE SP.SNO <> 'S1';

SELECT DISTINCT SP.PNO
FROM SP
WHERE (SELECT COUNT(*) FROM SP AS SPX WHERE SPX.PNO = SP.PNO) > 1;

SELECT XX, YY FROM
	(SELECT SNO AS XX, CITY FROM S) AS POINTLESS1 JOIN
	(SELECT SNO AS YY, CITY FROM S) AS POINTLESS2 USING (CITY)
ORDER BY XX,YY;

-- page 161
SELECT ZZZ AS PNO, 
	(SELECT MAX(QTY) FROM SP WHERE PNO = ZZZ) AS XXX,
	(SELECT MIN(QTY) FROM SP WHERE PNO = ZZZ) AS YYY
FROM (SELECT DISTINCT PNO AS ZZZ FROM SP WHERE SNO <> 'S1') AS POINTLESS
ORDER BY PNO;

DROP TABLE S;
DROP TABLE P;
DROP TABLE SP;
