CMU / backend / dec15.sql

-- CREATE
INSERT INTO
	SUPPLY_CHAIN.SUPPLIER
VALUES
	(
		'amaz',
		'amazon',
		'410 Terry Ave N',
		'Seattle',
		0,
		'SILVER'
	),
	(
		'goog',
		'google',
		'1600 Amphitheatre Parkway',
		'Mountain View',
		0,
		'GOLD'
	);

INSERT INTO
	SUPPLY_CHAIN.PRODUCT
VALUES
	('whwine', 'white wine', 'white', 100, ''),
	('rewine', 'red wine', 'red', 150, ''),
	('rowine', 'rose wine', 'rose', 0, ''),
	('prosec', 'prosecco', 'sparkling', 500, ''),
	('clubso', 'club soda', 'sparkling', 200, '');

INSERT INTO
	SUPPLY_CHAIN.SUPPLIES
VALUES
	('amaz', 'whwine', 10.22, '17:00'),
	('goog', 'rowine', 5.16, '23:00'),
	('amaz', 'rewine', 7.12, '19:00'),
	('goog', 'clubso', 3.07, '03:00'),
	('amaz', 'prosec', 9.99, '05:00');

INSERT INTO
	SUPPLY_CHAIN.PURCHASE_ORDER
VALUES
	('aaaaaaa', '2005-01-01', 'amaz'),
	('bbbbbbb', '2017-05-24', 'goog'),
	('ccccccc', '2013-09-30', 'amaz'),
	('ddddddd', '2020-12-01', 'goog'),
	('eeeeeee', '2025-12-16', 'amaz');

INSERT INTO
	SUPPLY_CHAIN.PO_LINE
VALUES
	('aaaaaaa', 'whwine', 5),
	('bbbbbbb', 'rowine', 50),
	('ccccccc', 'rewine', 10),
	('ddddddd', 'clubso', 150),
	('eeeeeee', 'prosec', 200);

-- READ
SELECT
	*
FROM
	SUPPLY_CHAIN.SUPPLIER;

SELECT
	*
FROM
	SUPPLY_CHAIN.PRODUCT;

SELECT
	*
FROM
	SUPPLY_CHAIN.SUPPLIES;

SELECT
	*
FROM
	SUPPLY_CHAIN.PURCHASE_ORDER;

SELECT
	P.PRODNR,
	P.PRODNAME,
	P.PRODTYPE,
	P.AVAILABLE_QUANTITY,
	PL.PONR,
	PL.QUANTITY
FROM
	SUPPLY_CHAIN.PRODUCT P
	RIGHT JOIN SUPPLY_CHAIN.PO_LINE PL ON P.PRODNR = PL.PRODNR
WHERE
	P.AVAILABLE_QUANTITY > 10;

-- UPDATE
UPDATE SUPPLY_CHAIN.supplier
SET
	SUPSTATUS = 100
WHERE
	SUPNR = 'amaz';

UPDATE SUPPLY_CHAIN.PRODUCT
SET
	"available_quantity" = 10
WHERE
	AVAILABLE_QUANTITY < 10;

-- DELETE
DELETE FROM SUPPLY_CHAIN.PURCHASE_ORDER WHERE PONR = 'aaaaaaa';

DELETE FROM SUPPLY_CHAIN.SUPPLIER;

DELETE FROM SUPPLY_CHAIN.PRODUCT;

DELETE FROM SUPPLY_CHAIN.PO_LINE;

DELETE FROM SUPPLY_CHAIN.PURCHASE_ORDER;

DELETE FROM SUPPLY_CHAIN.SUPPLIES;