
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 12 page 169
SELECT S.SNO FROM S
WHERE S.CITY = (SELECT S.CITY FROM S WHERE S.SNO='S1');

SELECT S.SNO FROM S
WHERE (20,S.CITY) = (SELECT S.STATUS, S.CITY FROM S WHERE S.SNO='S1');

SELECT P.PNO FROM P WHERE P.WEIGHT BETWEEN 16 AND 19;

SELECT P.PNO, P.PNAME FROM P WHERE P.PNAME LIKE 'C%';

-- page 172
SELECT DISTINCT S.SNAME FROM S
WHERE S.SNO IN (SELECT SP.SNO FROM SP WHERE SP.PNO='P2')
ORDER BY SNAME;

SELECT DISTINCT S.SNAME FROM S
WHERE S.SNO IN (SELECT SP.SNO FROM SP
		WHERE SP.PNO IN (SELECT P.PNO FROM P WHERE P.COLOR='Red'))
ORDER BY SNAME;

SELECT DISTINCT SNAME FROM S
WHERE SNO IN (SELECT SNO FROM SP
	      WHERE PNO IN (SELECT PNO FROM P WHERE COLOR='Red'))
ORDER BY SNAME;

-- page 173
SELECT DISTINCT S.SNAME FROM S
WHERE 'P2' IN (SELECT SP.PNO FROM SP WHERE SP.SNO = S.SNO)
ORDER BY SNAME;

SELECT DISTINCT SPX.SNO FROM SP AS SPX
WHERE SPX.PNO IN (SELECT SPY.PNO FROM SP AS SPY WHERE SPY.SNO='S2');

-- page 174
SELECT DISTINCT SP.SNO FROM SP
WHERE SP.PNO IN (SELECT SP.PNO FROM SP WHERE SP.SNO='S2');

SELECT P.PNO FROM P
WHERE P.WEIGHT IN (12,16,17);

-- page 176
SELECT DISTINCT PX.PNAME FROM P AS PX
WHERE PX.WEIGHT >ALL (SELECT DISTINCT PY.WEIGHT FROM P AS PY 
		      WHERE PY.COLOR='Blue');

-- page 177
SELECT DISTINCT S.SNAME FROM S
WHERE EXISTS (SELECT * FROM SP WHERE SP.SNO=S.SNO)
ORDER BY SNAME;

SELECT DISTINCT S.SNAME FROM S
WHERE NOT EXISTS (SELECT * FROM SP WHERE SP.SNO=S.SNO)
ORDER BY SNAME;

SELECT DISTINCT S.SNAME FROM S
WHERE NOT EXISTS (SELECT * FROM P
		  WHERE NOT EXISTS (SELECT * FROM SP
				    WHERE S.SNO=SP.SNO AND SP.PNO=P.PNO))
ORDER BY SNAME;

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