blob: cfa5f8c192ddb4114f9d322e664d55ed28e15870 (
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
39
|
-- PC
use PC;
-- Ex 1
select AVG(speed) from pc;
-- Ex 2
select p.maker, AVG(l.screen) from product p join laptop l where p.model = l.model group by p.maker;
-- Ex 3
select AVG(l.price) from laptop l where l.price > 1000;
-- Ex 4
select AVG(l.price) from laptop l join product p on p.model = l.model where p.maker = "A";
-- Ex 5
select AVG(l.price) from (select price, model from laptop union select price, model from pc) l join product p on p.model = l.model where p.maker = "B";
-- Ex 6
select pc.speed, AVG(pc.price) from pc group by pc.speed;
-- Ex 7
select p.maker from product p join pc on pc.model = p.model group by p.maker having COUNT(*) >= 3;
-- Ex 8
select p.maker from product p join pc on pc.model = p.model where pc.price = (select MAX(price) from pc) group by p.maker;
-- Ex 9
select pc.speed, AVG(pc.price) from pc where pc.price > 800 group by pc.speed;
-- Ex 10
select AVG(pc.hd) from pc join product p on p.model = pc.model where p.maker = ANY (select distinct maker from product where type = "Printer");
-- Ex 11
select distinct l.screen, ((select MAX(price) from laptop where screen = l.screen) - (select MIN(price) from laptop where screen = l.screen)) diff from laptop l;
-- SHIPS
use SHIPS;
-- Ex 1
select COUNT(*) from CLASSES;
-- Ex 2
select AVG(c.NUMGUNS) from CLASSES c join SHIPS s on s.CLASS = c.CLASS;
-- Ex 3
select c.CLASS, (select MIN(LAUNCHED) from SHIPS where CLASS = c.CLASS) earliest, (select MAX(LAUNCHED) from SHIPS where CLASS = c.CLASS) latest from CLASSES c;
-- Ex 4
select c.CLASS, (select COUNT(*) from OUTCOMES o join SHIPS s on o.SHIP = s.NAME join CLASSES cl on cl.CLASS = s.CLASS where cl.CLASS = c.CLASS and o.RESULT = "sunk") sank from CLASSES c;
-- Ex 5
select c.CLASS, (select COUNT(*) from OUTCOMES o join SHIPS s on o.SHIP = s.NAME join CLASSES cl on cl.CLASS = s.CLASS where cl.CLASS = c.CLASS and o.RESULT = "sunk" having COUNT(LAUNCHED) > 4) sank from CLASSES c;
-- Ex 6
select c.COUNTRY, AVG(c.DISPLACEMENT) from CLASSES c group by c.COUNTRY;
|
