blob: eed69b08edfef713c3220cfb83ce85843fe69eec (
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
35
36
37
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;
|
