[DB/P5] Solved exercises

1 files changed, 39 insertions, 0 deletions

diff --git a/Year 3/Databases/Practice05.sql b/Year 3/Databases/Practice05.sql
new file mode 100644
index 0000000..cfa5f8c
--- /dev/null
+++ b/Year 3/Databases/Practice05.sql
@@ -0,0 +1,39 @@
+-- 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;