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

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

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


-- chapter 7 page 94
SELECT COUNT(*) AS N1 FROM S;

SELECT COUNT(DISTINCT SP.SNO) AS N2 FROM SP;

SELECT COUNT(*) AS N3 FROM SP WHERE SP.PNO='P2';

SELECT SUM(SP.QTY) AS TOT1 FROM SP WHERE SP.PNO='P2';

SELECT S.SNO FROM S
WHERE S.STATUS < (SELECT MAX(S.STATUS) FROM S);

SELECT SX.SNO FROM S SX
WHERE SX.STATUS >= (SELECT AVG(SY.STATUS) FROM S SY WHERE SY.CITY=SX.CITY);


-- chapter 9 page 113
UPDATE S SET STATUS = 2 * S.STATUS WHERE S.CITY = 'Paris';
SELECT * FROM S ORDER BY SNO;

UPDATE P SET CITY = (SELECT S.CITY FROM S WHERE S.SNO='S5')
WHERE P.COLOR = 'Red';
SELECT * FROM P ORDER BY PNO;

DELETE FROM P WHERE P.WEIGHT > 13;
SELECT * FROM P ORDER BY PNO;

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