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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
-- TPC-H/TPC-R Minimum Cost Supplier Query (Q2)
-- using 1680793381 as a seed to the RNG
$r = (select r_regionkey from
plato.region
where r_name='AMERICA');
$j1 = (select n_name,n_nationkey
from plato.nation as n
join $r as r on
n.n_regionkey = r.r_regionkey);
$j2 = (select s_acctbal,s_name,s_address,s_phone,s_comment,n_name,s_suppkey
from plato.supplier as s
join $j1 as j on
s.s_nationkey = j.n_nationkey
);
$j3 = (select ps_partkey,ps_supplycost,s_acctbal,s_name,s_address,s_phone,s_comment,n_name
from plato.partsupp as ps
join $j2 as j on
ps.ps_suppkey = j.s_suppkey
);
$min_ps_supplycost = (select min(ps_supplycost) as min_ps_supplycost,ps_partkey
from $j3
group by ps_partkey
);
$p = (select p_partkey,p_mfgr
from plato.part
where
p_size = 10
and p_type like '%COPPER'
);
$j4 = (select s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from $p as p
join $j3 as j on p.p_partkey = j.ps_partkey
join $min_ps_supplycost as m on p.p_partkey = m.ps_partkey
where min_ps_supplycost=ps_supplycost
);
select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from $j4
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
limit 100;
|
