[DB/P2] Solved exercises

1 files changed, 34 insertions, 0 deletions

diff --git a/Year 3/Databases/Practice02.sql b/Year 3/Databases/Practice02.sql
new file mode 100644
index 0000000..9fa8725
--- /dev/null
+++ b/Year 3/Databases/Practice02.sql
@@ -0,0 +1,34 @@
+-- MOVIES
+use MOVIES;
+-- Ex 1
+select MOVIESTAR.NAME from MOVIESTAR join STARSIN on STARSIN.STARNAME = MOVIESTAR.NAME and STARSIN.MOVIETITLE = "Terms of Endearment" and MOVIESTAR.GENDER = "M";
+-- Ex 2
+select si.STARNAME from STARSIN si join MOVIE m on m.TITLE = si.MOVIETITLE and m.STUDIONAME = "MGM" and m.YEAR = 1995;
+-- Ex 3
+select me.NAME from STUDIO s join MOVIEEXEC me on s.`PRESC#` = me.`CERT#` where s.NAME = 'MGM';
+-- Ex 4
+select m1.TITLE from MOVIE m1 join MOVIE m2 where m2.TITLE = "Star Wars" and m2.LENGTH < m1.LENGTH;
+
+-- PC
+use PC;
+-- Ex 1
+select p.maker, l.speed from product p join laptop l on p.model = l.model where l.hd >= 9;
+-- Ex 2
+select distinct p.model, pc.price from product p join pc on p.model = pc.model where p.maker = "B" union select distinct p.model, l.price from product p join laptop l on p.model = l.model where p.maker = "B" union select distinct p.model, pr.price from product p join printer pr on p.model = pr.model where p.maker = "B";
+-- Ex 3
+select distinct pc1.hd from pc pc1 join pc pc2 on pc1.hd = pc2.hd and pc1.code != pc2.code;
+-- Ex 4
+select pc1.model, pc2.model from pc pc1 join pc pc2 on pc1.model != pc2.model and pc1.speed = pc2.speed and pc1.ram = pc2.ram where pc1.model < pc2.model;
+-- Ex 5
+select distinct p1.maker from product p1 join product p2 on p1.maker = p2.maker and p1.model != p2.model join pc pc1 on pc1.model = p1.model join pc pc2 on pc2.model = p2.model where pc1.speed >= 500 and pc2.speed >= 500;
+
+-- SHIPS
+use SHIPS;
+-- Ex 1
+select s.NAME from SHIPS s join CLASSES c on s.CLASS = c.CLASS where c.DISPLACEMENT > 35000;
+-- Ex 2
+select s.NAME, c.BORE, c.NUMGUNS from CLASSES c join SHIPS s on s.CLASS = c.CLASS join OUTCOMES o on o.SHIP = s.NAME where BATTLE = "Guadalcanal";
+-- Ex 3
+select c.COUNTRY from CLASSES c join SHIPS s on s.CLASS = c.CLASS and c.TYPE = "bb" intersect select c.COUNTRY from CLASSES c join SHIPS s on s.CLASS = c.CLASS and c.TYPE = "bc";
+-- Ex 4
+select o1.SHIP from BATTLES b1 join BATTLES b2 on b1.DATE < b2.DATE join OUTCOMES o1 on o1.BATTLE = b1.NAME join OUTCOMES o2 on o2.BATTLE = b2.NAME and o2.SHIP = o1.SHIP where o1.RESULT = "damaged";