[DB/P3] Solved exercises

1 files changed, 38 insertions, 0 deletions

diff --git a/Year 3/Databases/Practice03.sql b/Year 3/Databases/Practice03.sql
new file mode 100644
index 0000000..eed69b0
--- /dev/null
+++ b/Year 3/Databases/Practice03.sql
@@ -0,0 +1,38 @@
+-- MOVIES
+use MOVIES;
+-- Ex 1
+select ms.NAME from MOVIESTAR ms where ms.GENDER = "F" and ms.NAME = ANY (select me.NAME from MOVIEEXEC me where me.NETWORTH > 10000000);
+-- Ex 2
+select ms.NAME from MOVIESTAR ms where ms.NAME != ALL (select NAME from MOVIEEXEC);
+-- Ex 3
+select TITLE from MOVIE where LENGTH > (select LENGTH from MOVIE where TITLE = "Gone With the Wind");
+-- Ex 4
+select me.NAME, m.TITLE from MOVIEEXEC me join MOVIE m on m.`PRODUCERC#` = me.`CERT#` where me.NETWORTH > (select NETWORTH from MOVIEEXEC where NAME = "Merv Griffin");
+
+-- PC
+use PC;
+-- Ex 1
+select p.maker from product p join pc on pc.model = p.model where pc.speed >= 500;
+-- Ex 2
+select * from printer p where p.price >= ALL (select price from printer);
+-- Ex 3
+select * from laptop l where l.speed < ANY (select price from pc);
+-- Ex 4
+select p.model from (select model, price from pc union select model, price from laptop union select model, price from printer) p where p.price >= ALL (select price from pc union select price from laptop union select price from printer);
+-- Ex 5
+select p.maker from product p join printer pr on pr.model = p.model where pr.color = "y" and pr.price <= ALL (select price from printer where color = "y");
+-- Ex 6
+select p.maker from product p join (select * from pc where ram <= ALL (select ram from pc)) pcc on pcc.model = p.model where pcc.speed >= ALL (select speed from pc where ram <= ALL (select ram from pc));
+
+-- SHIPS
+use SHIPS;
+-- Ex 1
+select distinct c.COUNTRY from CLASSES c where c.NUMGUNS >= ALL (select NUMGUNS from CLASSES);
+-- Ex 2
+select distinct s.CLASS from SHIPS s join OUTCOMES o on o.SHIP = s.NAME where o.RESULT = "sunk";
+-- Ex 3
+select s.NAME from SHIPS s join CLASSES c on c.CLASS = s.CLASS where c.bore = 16;
+-- Ex 4
+select distinct o.BATTLE from OUTCOMES o join SHIPS s on s.NAME = o.SHIP where s.CLASS = "Kongo";
+-- Ex 5
+select s.NAME from SHIPS s join (select * from CLASSES c where c.NUMGUNS >= ALL (select NUMGUNS from CLASSES where BORE = c.BORE)) cl on cl.CLASS = s.CLASS;