diff options
1 files changed, 34 insertions, 0 deletions
| -rw-r--r-- | Year 3/Databases/Practice02.sql | 34 |
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"; |