DROP TABLE IF EXISTS product;
CREATE TABLE product(
	maker varchar(10),
	model varchar(50) PRIMARY KEY,
	type varchar(50)
);

INSERT INTO product VALUES
('A', '1001', 'pc'),
('A', '1002', 'pc'),
('A', '1003', 'pc'),
('A', '2004', 'laptop'),
('A', '2005', 'laptop'),
('A', '2006', 'laptop'),
('B', '1004', 'pc'),
('B', '1005', 'pc'),
('B', '1006', 'pc'),
('B', '2007', 'laptop'),
('C', '1007', 'pc'),
('D', '1008', 'pc'),
('D', '1009', 'pc'),
('D', '1010', 'pc'),
('D', '3004', 'printer'),
('D', '3005', 'printer'),
('E', '1011', 'pc'),
('E', '1012', 'pc'),
('E', '1013', 'pc'),
('E', '2001', 'laptop'),
('E', '2002', 'laptop'),
('E', '2003', 'laptop'),
('E', '3001', 'printer'),
('E', '3002', 'printer'),
('E', '3003', 'printer'),
('F', '2008', 'laptop'),
('F', '2009', 'laptop'),
('G', '2010', 'laptop'),
('H', '3006', 'printer'),
('H', '3007', 'printer');

DROP TABLE IF EXISTS pc;
CREATE TABLE pc(
	code INT PRIMARY KEY,
	model varchar(50),
	speed real,
	ram smallint,
	hd real,
	price real
);

INSERT INTO pc VALUES
(1, '1001',2.66,1024,250,2114),
(2, '1002',2.10,512,250,995),
(3, '1003',1.42,512,80,478),
(4, '1004',2.80,1024,250,649),
(5, '1005',3.20,512,250,630),
(6, '1006',3.20,1024,320,1049),
(7, '1007',2.20,1024,200,510),
(8, '1008',2.20,2048,250,770),
(9, '1009',2.00,1024,250,650),
(10, '1010',2.80,2048,300,770),
(11, '1011',1.86,2048,160,959),
(12, '1012',2.80,1024,160,649),
(13, '1013',3.06,512,80,529);

DROP TABLE IF EXISTS laptop;
CREATE TABLE laptop(
	code int PRIMARY KEY,
	model varchar(50),
	speed real,
	ram smallint,
	hd real,
	screen real,
	price real
);

INSERT INTO laptop VALUES 
(1, '2001', 2.00, 2048, 240, 20.1, 3673), 
(2, '2002', 1.73, 1024, 80, 17.0, 949), 
(3, '2003', 1.80, 512, 60, 15.4, 549),
(4, '2004', 2.00, 512, 60, 13.3, 1150),
(5, '2005', 2.16, 1024, 120, 17.0, 2500),
(6, '2006', 2.0, 2048, 80, 15.4, 1700),
(7, '2007', 1.83, 1024, 120, 13.3, 1429),
(8, '2008', 1.60, 1024, 100, 15.4, 900),
(9, '2009', 1.60, 512, 80, 14.1, 680),
(10, '2010', 2.00, 2048, 160, 15.4, 2300);

DROP TABLE IF EXISTS printer;
CREATE TABLE printer(
	code int PRIMARY KEY,
	model varchar(50),
	color boolean,
	type varchar(10),
	price real
);

INSERT INTO printer VALUES
(1, '3001', true, 'ink-jet', 99),
(2, '3002', false, 'laser', 239),
(3, '3003', true, 'laser', 899),
(4, '3004', true, 'ink-jet', 120),
(5, '3005', false, 'laser', 120),
(6, '3006', true, 'ink-jet', 100),
(7, '3007', true, 'laser', 200);


SELECT model, speed, hd FROM pc WHERE price < 700;
SELECT DISTINCT maker FROM product WHERE type = 'printer';
SELECT model, hd, screen FROM laptop WHERE price > 1000;
SELECT * FROM printer WHERE color;

SELECT maker, speed FROM product JOIN laptop ON product.model = laptop.model
WHERE hd >= 10;
(SELECT product.model, price FROM product JOIN pc ON product.model = pc.model WHERE maker = 'B'
UNION
SELECT product.model, price FROM product JOIN laptop ON product.model = laptop.model WHERE maker = 'B'
UNION
SELECT product.model, price FROM product JOIN printer ON product.model = printer.model WHERE maker = 'B') ORDER BY model;
SELECT DISTINCT maker FROM product WHERE type = 'pc'
EXCEPT
SELECT DISTINCT maker FROM product WHERE type = 'laptop';

SELECT model FROM printer WHERE price = (SELECT MAX(price) FROM printer);
SELECT AVG(speed) FROM pc;
SELECT AVG(speed) FROM laptop WHERE price > 100;
SELECT AVG(speed) FROM product JOIN pc ON product.model = pc.model WHERE maker = 'A';
SELECT DISTINCT maker, type FROM product WHERE maker in 
(SELECT maker FROM product GROUP BY maker HAVING COUNT(DISTINCT type) = 1);

SELECT hd FROM pc GROUP BY hd HAVING COUNT(hd) >= 2 ORDER BY hd;
SELECT model, speed FROM laptop WHERE speed < (SELECT MIN(speed) FROM pc);
SELECT maker, price FROM printer JOIN product ON printer.model = product.model WHERE price = (SELECT MIN(price) FROM printer WHERE color) AND color;
SELECT maker, AVG(screen) FROM laptop JOIN product ON laptop.model = product.model GROUP BY maker ORDER BY maker;
SELECT maker FROM product WHERE type = 'pc' GROUP BY maker HAVING COUNT(type) >= 3;

SELECT maker, MAX(price) FROM product JOIN pc ON pc.model = product.model GROUP BY maker ORDER BY maker;
WITH MAX_PRICES as (
	SELECT MAX(price) as price FROM laptop UNION SELECT MAX(price) as price FROM pc UNION SELECT MAX(price) as price FROM printer
)
SELECT model FROM laptop WHERE price = (SELECT MAX(price) FROM MAX_PRICES)
UNION
SELECT model FROM PC WHERE price = (SELECT MAX(price) FROM MAX_PRICES)
UNION 
SELECT model FROM printer WHERE price = (SELECT MAX(price) FROM MAX_PRICES);
WITH LEAST_RAM as (
	SELECT MIN(ram) as ram FROM pc
)
SELECT DISTINCT maker FROM product JOIN pc on product.model = pc.model WHERE
ram = (SELECT ram FROM LEAST_RAM) AND speed = (SELECT MAX(speed) FROM pc WHERE ram = (SELECT ram FROM LEAST_RAM))
INTERSECT
SELECT DISTINCT maker FROM product WHERE type='printer';
SELECT AVG(price) FROM (SELECT price FROM pc JOIN product ON pc.model = product.model WHERE maker = 'A' 
						UNION 
						SELECT price FROM laptop JOIN product ON laptop.model = product.model WHERE maker = 'A') as foo;