blob: 9fa8725b0ba5e1b9c23ff5732ae6a29664bc7693 (
plain) (
blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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";
|
