[DB/P4] Solved exercises

1 files changed, 28 insertions, 0 deletions

diff --git a/Year 3/Databases/Practice04.sql b/Year 3/Databases/Practice04.sql
new file mode 100644
index 0000000..7aff19d
--- /dev/null
+++ b/Year 3/Databases/Practice04.sql
@@ -0,0 +1,28 @@
+-- MOVIES
+use MOVIES;
+-- Ex 1
+select m.TITLE, m.YEAR, m.STUDIONAME, s.ADDRESS from MOVIE m left outer join STUDIO s on s.NAME = m.STUDIONAME where m.LENGTH > 120;
+-- Ex 2
+select distinct s.NAME, st.STARNAME from STUDIO s join MOVIE m on m.STUDIONAME = s.NAME join STARSIN st on m.TITLE = st.MOVIETITLE order by s.NAME;
+-- Ex 3
+select distinct me.NAME from MOVIEEXEC me join (select * from MOVIE m join STARSIN st on m.TITLE = st.MOVIETITLE where st.STARNAME = "Harrison Ford") mo on mo.`PRODUCERC#` = me.`CERT#`;
+-- Ex 4
+select stt.STARNAME from (select * from STARSIN st join MOVIESTAR s on st.STARNAME = s.NAME where GENDER = "F") stt join MOVIE m on m.TITLE = stt.MOVIETITLE where m.STUDIONAME = "MGM";
+-- Ex 5
+select me.NAME, m.TITLE from (select * from MOVIEEXEC join MOVIE on `PRODUCERC#` = `CERT#` where TITLE = "Star Wars") me join MOVIE m on m.`PRODUCERC#` = me.`CERT#`;
+-- Ex 6
+select ms.NAME from MOVIESTAR ms where ms.NAME not in (select STARNAME from STARSIN);
+
+-- PC
+use PC;
+-- Ex 1
+select * from product p where p.model not in (select model from pc union select model from laptop union select model from printer);
+
+-- SHIPS
+use SHIPS;
+-- Ex 1
+select s.NAME, c.COUNTRY, c.NUMGUNS, s.LAUNCHED from SHIPS s join CLASSES c on c.CLASS = s.CLASS;
+-- Ex 2
+select o.SHIP from OUTCOMES o where BATTLE = ANY (select NAME from BATTLES where DATE >= '19440101' and DATE <= '19441231');
+-- Ex 3
+select NAME from SHIPS except select SHIP as NAME from OUTCOMES;