-- PC
use PC;
-- Ex 1
select AVG(speed) from pc;
-- Ex 2
select p.maker, AVG(l.screen) from product p join laptop l where p.model = l.model group by p.maker;
-- Ex 3
select AVG(l.price) from laptop l where l.price > 1000;
-- Ex 4
select AVG(l.price) from laptop l join product p on p.model = l.model where p.maker = "A";
-- Ex 5
select AVG(l.price) from (select price, model from laptop union select price, model from pc) l join product p on p.model = l.model where p.maker = "B";
-- Ex 6
select pc.speed, AVG(pc.price) from pc group by pc.speed;
-- Ex 7
select p.maker from product p join pc on pc.model = p.model group by p.maker having COUNT(*) >= 3;
-- Ex 8
select p.maker from product p join pc on pc.model = p.model where pc.price = (select MAX(price) from pc) group by p.maker;
-- Ex 9
select pc.speed, AVG(pc.price) from pc where pc.price > 800 group by pc.speed;
-- Ex 10
select AVG(pc.hd) from pc join product p on p.model = pc.model where p.maker = ANY (select distinct maker from product where type = "Printer");
-- Ex 11
select distinct l.screen, ((select MAX(price) from laptop where screen = l.screen) - (select MIN(price) from laptop where screen = l.screen)) diff from laptop l;

-- SHIPS
use SHIPS;
-- Ex 1
select COUNT(*) from CLASSES;
-- Ex 2
select AVG(c.NUMGUNS) from CLASSES c join SHIPS s on s.CLASS = c.CLASS;
-- Ex 3
select c.CLASS, (select MIN(LAUNCHED) from SHIPS where CLASS = c.CLASS) earliest, (select MAX(LAUNCHED) from SHIPS where CLASS = c.CLASS) latest from CLASSES c;
-- Ex 4
select c.CLASS, (select COUNT(*) from OUTCOMES o join SHIPS s on o.SHIP = s.NAME join CLASSES cl on cl.CLASS = s.CLASS where cl.CLASS = c.CLASS and o.RESULT = "sunk") sank from CLASSES c;
-- Ex 5
select c.CLASS, (select COUNT(*) from OUTCOMES o join SHIPS s on o.SHIP = s.NAME join CLASSES cl on cl.CLASS = s.CLASS where cl.CLASS = c.CLASS and o.RESULT = "sunk" having COUNT(LAUNCHED) > 4) sank from CLASSES c;
-- Ex 6
select c.COUNTRY, AVG(c.DISPLACEMENT) from CLASSES c group by c.COUNTRY;