Test Queries
Query 1
select
l_returnflag
,l_linestatus
,sum(l_quantity) as sum_qty
,sum(l_extendedprice) as sum_base_price
,sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
,sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
,avg(l_quantity) as avg_qty
,avg(l_extendedprice) as avg_price
,avg(l_discount) as avg_disc
,count(*) as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '90' day
group by
l_returnflag
,l_linestatus
order by
l_returnflag
,l_linestatus;
Query 2
select first 100
s_acctbal
,s_name
,n_name
,p_partkey
,p_mfgr
,s_address
,s_phone
,s_comment
from
part
,supplier
,partsupp
,nation
,region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 15
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp
,supplier
,nation
,region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
)
order by
s_acctbal desc
,n_name
,s_name
,p_partkey;
Query 3
select first 10
l_orderkey
,sum(l_extendedprice * (1 - l_discount)) as revenue
,o_orderdate
,o_shippriority
from
customer
,orders
,lineitem
where
c_mktsegment = 'BUILDING'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < date '1995-03-15'
and l_shipdate > date '1995-03-15'
group by
l_orderkey
,o_orderdate
,o_shippriority
order by
revenue desc
,o_orderdate;
Query 4
select
o_orderpriority
,count(*) as order_count
from
orders
where
o_orderdate >= date '1993-07-01'
and o_orderdate < date '1993-07-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority;
Query 5
select
n_name
,sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer
,orders
,lineitem
,supplier
,nation
,region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= date '1994-01-01'
and o_orderdate < date '1994-01-01' + interval '1' year
group by
n_name
order by
revenue desc;
Query 6
select
sum(l_extendedprice * l_discount) as revenue
from
lineitem
where
l_shipdate >= date '1994-01-01'
and l_shipdate < date '1994-01-01' + interval '1' year
and l_discount between .06 - 0.01 and .06 + 0.01
and l_quantity < 24;
Query 7
select
supp_nation
,cust_nation
,l_year
,sum(volume) as revenue
from
(
select
n1.n_name as supp_nation
,n2.n_name as cust_nation
,extract(year from l_shipdate) as l_year
,l_extendedprice * (1 - l_discount) as volume
from
supplier
,lineitem
,orders
,customer
,nation n1
,nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
)
and l_shipdate between date '1995-01-01' and date '1996-12-31'
) as shipping
group by
supp_nation
,cust_nation
,l_year
order by
supp_nation
,cust_nation
,l_year;
Query 8
select
o_year
,sum(case
when nation = 'BRAZIL' then volume
else 0
end) / sum(volume) as mkt_share
from
(
select
extract(year from o_orderdate) as o_year
,l_extendedprice * (1 - l_discount) as volume
,n2.n_name as nation
from
part
,supplier
,lineitem
,orders
,customer
,nation n1
,nation n2
,region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA'
and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date '1996-12-31'
and p_type = 'ECONOMY ANODIZED STEEL'
) as all_nations
group by
o_year
order by
o_year;