text: #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_sub('1998-12-01', interval 107 day)
  group by
  	l_returnflag,
  	l_linestatus
  order by
  	l_returnflag,
  	l_linestatus;
  
  #2
  select
  	s_acctbal,
  	s_name,
  	n_name,
  	p_partkey,
  	p_mfgr,
  	s_address,
  	s_phone,
  	s_comment
  from
  	part,
  	supplier,
  	partsupp pso,
  	nation,
  	region
  where
  	p_partkey = pso.ps_partkey
  	and s_suppkey = pso.ps_suppkey
  	and p_size = 6
  	and p_type like '%COPPER'
  	and s_nationkey = n_nationkey
  	and n_regionkey = r_regionkey
  	and r_name = 'MIDDLE EAST'
  	and pso.ps_supplycost = (
  		select
  			min(psi.ps_supplycost)
  		from
  			partsupp psi,
  			supplier,
  			nation,
  			region
  		where
  		    pso.ps_partkey = psi.ps_partkey
  			and s_suppkey = psi.ps_suppkey
  			and s_nationkey = n_nationkey
  			and n_regionkey = r_regionkey
  			and r_name = 'MIDDLE EAST'
  	)
  order by
  	s_acctbal desc,
  	n_name,
  	s_name,
  	p_partkey
  LIMIT 100;
  
  #3
  select
  	l_orderkey,
  	sum(l_extendedprice * (1 - l_discount)) as revenue,
  	o_orderdate,
  	o_shippriority
  from
  	customer,
  	orders,
  	lineitem
  where
  	c_mktsegment = 'HOUSEHOLD'
  	and c_custkey = o_custkey
  	and l_orderkey = o_orderkey
  	and o_orderdate < '1995-03-08'
  	and l_shipdate > '1995-03-08'
  group by
  	l_orderkey,
  	o_orderdate,
  	o_shippriority
  order by
  	revenue desc,
  	o_orderdate
  LIMIT 10;
  
  #4
  select
  	o_orderpriority,
  	count(*) as order_count
  from
  	orders
  where
  	o_orderdate >= date '1995-06-01'
  	and o_orderdate < date_add( '1995-06-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;
  
  #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 + 0
  	and s_nationkey = n_nationkey
  	and n_regionkey = r_regionkey
  	and r_name = 'AMERICA'
  	and o_orderdate >= '1993-01-01'
  	and o_orderdate < date_add( '1993-01-01' , interval 1 year)
  group by
  	n_name
  order by
  	revenue desc;
  
  #6
  select
  	sum(l_extendedprice * l_discount) as revenue
  from
  	lineitem
  where
  	l_shipdate >= '1995-01-01'
  	and l_shipdate < date_add( '1995-01-01' , interval 1 year)
  	and l_discount between 0.06 - 0.01 and 0.06 + 0.01
  	and l_quantity < 24;
  
  #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,
  			year(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 = 'ETHIOPIA' and n2.n_name = 'JORDAN')
  				or (n1.n_name = 'JORDAN' and n2.n_name = 'ETHIOPIA')
  			)
  			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;
  
  #8
  select
  	o_year,
  	sum(case
  		when nation = 'UNITED STATES' then volume
  		else 0
  	end) / sum(volume) as mkt_share
  from
  	(
  		select
  			year(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 = 'LARGE PLATED COPPER'
  	) as all_nations
  group by
  	o_year
  order by
  	o_year;
  
  #9
  select
  	nation,
  	o_year,
  	sum(amount) as sum_profit
  from
  	(
  		select
  			n_name as nation,
  			year(o_orderdate) as o_year,
  			l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
  		from
  			part,
  			supplier,
  			lineitem,
  			partsupp,
  			orders,
  			nation
  		where
  			s_suppkey = l_suppkey
  			and ps_suppkey = l_suppkey
  			and ps_partkey = l_partkey
  			and p_partkey = l_partkey
  			and o_orderkey = l_orderkey
  			and s_nationkey = n_nationkey
  			and p_name like '%gainsboro%'
  	) as profit
  group by
  	nation,
  	o_year
  order by
  	nation,
  	o_year desc;
  
  #10
  select
  	c_custkey,
  	c_name,
  	sum(l_extendedprice * (1 - l_discount)) as revenue,
  	c_acctbal,
  	n_name,
  	c_address,
  	c_phone,
  	c_comment
  from
  	customer,
  	orders,
  	lineitem,
  	nation
  where
  	c_custkey = o_custkey
  	and l_orderkey = o_orderkey
  	and o_orderdate >= '1994-12-01'
  	and o_orderdate < date_add( '1994-12-01' , interval 3 month)
  	and l_returnflag = 'R'
  	and c_nationkey = n_nationkey
  group by
  	c_custkey,
  	c_name,
  	c_acctbal,
  	c_phone,
  	n_name,
  	c_address,
  	c_comment
  order by
  	revenue desc
  LIMIT 20;
  
  #11
  select
  	ps_partkey,
  	sum(ps_supplycost * ps_availqty) as value
  from
  	partsupp,
  	supplier,
  	nation
  where
  	ps_suppkey = s_suppkey
  	and s_nationkey = n_nationkey
  	and n_name = 'PERU'
  group by
  	ps_partkey having
  		sum(ps_supplycost * ps_availqty) > (
  			select
  				sum(ps_supplycost * ps_availqty) * 0.0001000000
  			from
  				partsupp,
  				supplier,
  				nation
  			where
  				ps_suppkey = s_suppkey
  				and s_nationkey = n_nationkey
  				and n_name = 'PERU'
  		)
  order by
  	value desc;
  
  #12
  select
  	l_shipmode,
  	sum(case
  		when o_orderpriority = '1-URGENT'
  			or o_orderpriority = '2-HIGH'
  			then 1
  		else 0
  	end) as high_line_count,
  	sum(case
  		when o_orderpriority <> '1-URGENT'
  			and o_orderpriority <> '2-HIGH'
  			then 1
  		else 0
  	end) as low_line_count
  from
  	orders,
  	lineitem
  where
  	o_orderkey = l_orderkey
  	and l_shipmode in ('REG AIR', 'RAIL')
  	and l_commitdate < l_receiptdate
  	and l_shipdate < l_commitdate
  	and l_receiptdate >= '1994-01-01'
  	and l_receiptdate < date_add( '1994-01-01' , interval 1 year)
  group by
  	l_shipmode
  order by
  	l_shipmode;
  
  #13
  select
  	c_count,
  	count(*) as custdist
  from
  	(
  		select
  			c_custkey,
  			count(o_orderkey) as c_count
  		from
  			customer left outer join orders on
  				c_custkey = o_custkey
  				and o_comment not like '%special%requests%'
  		group by
  			c_custkey
  	) as c_orders
  group by
  	c_count
  order by
  	custdist desc,
  	c_count desc;
  
  #14
  select
  	100.00 * sum(case
  		when p_type like 'PROMO%'
  			then l_extendedprice * (1 - l_discount)
  		else 0
  	end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
  from
  	lineitem,
  	part
  where
  	l_partkey = p_partkey
  	and l_shipdate >= '1995-02-01'
  	and l_shipdate < date_add( '1995-02-01' , interval 1 month);
  
  #15
  create or replace view revenue0 (supplier_no, total_revenue) as
  	select
  		l_suppkey,
  		sum(l_extendedprice * (1 - l_discount))
  	from
  		lineitem
  	where
  		l_shipdate >= '1993-03-01'
  		and l_shipdate < date_add('1993-03-01', interval 90 day)
  	group by
  		l_suppkey;
  
  select
  	s_suppkey,
  	s_name,
  	s_address,
  	s_phone,
  	total_revenue
  from
  	supplier,
  	revenue0
  where
  	s_suppkey = supplier_no
  	and total_revenue = (
  		select
  			max(total_revenue)
  		from
  			revenue0
  	)
  order by
  	s_suppkey;
  
  drop view revenue0;
  
  #16
  select
  	p_brand,
  	p_type,
  	p_size,
  	count(distinct ps_suppkey) as supplier_cnt
  from
  	partsupp,
  	part
  where
  	p_partkey = ps_partkey
  	and p_brand <> 'Brand#25'
  	and p_type not like 'MEDIUM BRUSHED%'
  	and p_size in (1, 37, 22, 30, 12, 26, 39, 43)
  	and ps_suppkey not in (
  		select
  			s_suppkey
  		from
  			supplier
  		where
  			s_comment like '%Customer%Complaints%'
  	)
  group by
  	p_brand,
  	p_type,
  	p_size
  order by
  	supplier_cnt desc,
  	p_brand,
  	p_type,
  	p_size;
  
  #17
  select
  	sum(l_extendedprice) / 7.0 as avg_yearly
  from
  	lineitem lo,
  	part
  where
  	p_partkey = l_partkey
  	and p_brand = 'Brand#15'
  	and p_container = 'JUMBO PACK'
  	and l_quantity < (
  		select
  			0.2 * avg(l_quantity)
  		from
  			lineitem
  		where
  			l_partkey = lo.l_partkey
  	);
  
  #18
  select
  	c_name,
  	c_custkey,
  	o_orderkey,
  	o_orderdate,
  	o_totalprice,
  	sum(l_quantity)
  from
  	customer,
  	orders,
  	lineitem
  where
  	o_orderkey in (
  		select
  			l_orderkey
  		from
  			lineitem
  		group by
  			l_orderkey having
  				sum(l_quantity) > 314
  	)
  	and c_custkey = o_custkey
  	and o_orderkey = l_orderkey
  group by
  	c_name,
  	c_custkey,
  	o_orderkey,
  	o_orderdate,
  	o_totalprice
  order by
  	o_totalprice desc,
  	o_orderdate
  LIMIT 100;
  
  #19
  select
  	sum(l_extendedprice* (1 - l_discount)) as revenue
  from
  	lineitem,
  	part
  where
      p_partkey = l_partkey
      and
  	((
  		p_brand = 'Brand#35'
  		and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
  		and l_quantity >= 8 and l_quantity <= 8+10
  		and p_size between 1 and 5
  		and l_shipmode in ('AIR', 'AIR REG')
  		and l_shipinstruct = 'DELIVER IN PERSON'
  	)
  	or
  	(
  		p_brand = 'Brand#34'
  		and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
  		and l_quantity >= 16 and l_quantity <= 16+10
  		and p_size between 1 and 10
  		and l_shipmode in ('AIR', 'AIR REG')
  		and l_shipinstruct = 'DELIVER IN PERSON'
  	)
  	or
  	(
  		p_brand = 'Brand#33'
  		and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
  		and l_quantity >= 22 and l_quantity <= 22+10
  		and p_size between 1 and 15
  		and l_shipmode in ('AIR', 'AIR REG')
  		and l_shipinstruct = 'DELIVER IN PERSON'
  	));
  
  #20
  select
  	s_name,
  	s_address
  from
  	supplier,
  	nation
  where
  	s_suppkey in (
  		select
  			distinct (ps_suppkey)
  		from
  			partsupp,
  			part
  		where
  			ps_partkey=p_partkey
  			and p_name like 'goldenrod%'
  			and ps_availqty > (
  				select
  					0.5 * sum(l_quantity)
  				from
  					lineitem
  				where
  					l_partkey = ps_partkey
  					and l_suppkey = ps_suppkey
  					and l_shipdate >= '1993-01-01'
  					and l_shipdate < date_ADD('1993-01-01',interval 1 year)
  			)
  	)
  	and s_nationkey = n_nationkey
  	and n_name = 'IRAN'
  order by
  	s_name;
  
  #21
  select
  	s_name,
  	count(*) as numwait
  from
  	supplier,
  	lineitem l1,
  	orders,
  	nation
  where
  	s_suppkey = l1.l_suppkey
  	and o_orderkey = l1.l_orderkey
  	and o_orderstatus = 'F'
  	and l1.l_receiptdate > l1.l_commitdate
  	and exists (
  		select
  			*
  		from
  			lineitem l2
  		where
  			l2.l_orderkey = l1.l_orderkey
  			and l2.l_suppkey <> l1.l_suppkey
  	)
  	and not exists (
  		select
  			*
  		from
  			lineitem l3
  		where
  			l3.l_orderkey = l1.l_orderkey
  			and l3.l_suppkey <> l1.l_suppkey
  			and l3.l_receiptdate > l3.l_commitdate
  	)
  	and s_nationkey = n_nationkey
  	and n_name = 'EGYPT'
  group by
  	s_name
  order by
  	numwait desc,
  	s_name
  LIMIT 100;
  
  #22
  select
  	cntrycode,
  	count(*) as numcust,
  	sum(c_acctbal) as totacctbal
  from
  	(
  		select
  			substr(c_phone, 1, 2) as cntrycode,
  			c_acctbal
  		from
  			customer
  		where
  			substr(c_phone, 1, 2) in
  				('30', '18', '15', '17', '19', '16', '27')
  			and c_acctbal > (
  				select
  					avg(c_acctbal)
  				from
  					customer
  				where
  					c_acctbal > 0.00
  					and substr(c_phone, 1, 2) in
  						('30', '18', '15', '17', '19', '16', '27')
  			)
  			and not exists (
  				select
  					*
  				from
  					orders
  				where
  					o_custkey = c_custkey
  			)
  	) as vip
  group by
  	cntrycode
  order by
  	cntrycode;
  