| # Copyright 2011-2015 Quickstep Technologies LLC. |
| # Copyright 2015 Pivotal Software, Inc. |
| # Copyright 2016, Quickstep Research Group, Computer Sciences Department, |
| # University of Wisconsin—Madison. |
| # |
| # Licensed under the Apache License, Version 2.0 (the "License"); |
| # you may not use this file except in compliance with the License. |
| # You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, software |
| # distributed under the License is distributed on an "AS IS" BASIS, |
| # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| # See the License for the specific language governing permissions and |
| # limitations under the License. |
| |
| # 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 '96 day' |
| GROUP BY |
| l_returnflag, |
| l_linestatus |
| ORDER BY |
| l_returnflag, |
| l_linestatus |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=l_returnflag] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=l_linestatus] |
| | +-SelectListItem[alias=sum_qty] |
| | | +-FunctionCall[name=SUM] |
| | | +-AttributeReference[attribute_name=l_quantity] |
| | +-SelectListItem[alias=sum_base_price] |
| | | +-FunctionCall[name=SUM] |
| | | +-AttributeReference[attribute_name=l_extendedprice] |
| | +-SelectListItem[alias=sum_disc_price] |
| | | +-FunctionCall[name=SUM] |
| | | +-Multiply |
| | | +-left_operand=AttributeReference[attribute_name=l_extendedprice] |
| | | +-right_operand=Subtract |
| | | +-left_operand=Literal |
| | | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | | +-right_operand=AttributeReference[attribute_name=l_discount] |
| | +-SelectListItem[alias=sum_charge] |
| | | +-FunctionCall[name=SUM] |
| | | +-Multiply |
| | | +-left_operand=Multiply |
| | | | +-left_operand=AttributeReference[attribute_name=l_extendedprice] |
| | | | +-right_operand=Subtract |
| | | | +-left_operand=Literal |
| | | | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | | | +-right_operand=AttributeReference[attribute_name=l_discount] |
| | | +-right_operand=Add |
| | | +-left_operand=Literal |
| | | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | | +-right_operand=AttributeReference[attribute_name=l_tax] |
| | +-SelectListItem[alias=avg_qty] |
| | | +-FunctionCall[name=AVG] |
| | | +-AttributeReference[attribute_name=l_quantity] |
| | +-SelectListItem[alias=avg_price] |
| | | +-FunctionCall[name=AVG] |
| | | +-AttributeReference[attribute_name=l_extendedprice] |
| | +-SelectListItem[alias=avg_disc] |
| | | +-FunctionCall[name=AVG] |
| | | +-AttributeReference[attribute_name=l_discount] |
| | +-SelectListItem[alias=count_order] |
| | +-FunctionCall[name=COUNT,is_star=true] |
| +-where_clause=LessOrEqual |
| | +-left_operand=AttributeReference[attribute_name=l_shipdate] |
| | +-right_operand=Subtract |
| | +-left_operand=Literal |
| | | +-StringLiteral[value=1998-12-01,explicit_type=Datetime] |
| | +-right_operand=Literal |
| | +-StringLiteral[value=96 day,explicit_type=DatetimeInterval] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=l_returnflag] |
| | +-AttributeReference[attribute_name=l_linestatus] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | | +-AttributeReference[attribute_name=l_returnflag] |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | +-AttributeReference[attribute_name=l_linestatus] |
| +-from_clause= |
| +-TableReference[table=lineitem] |
| == |
| |
| # Query 2 |
| SELECT |
| 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 = 48 |
| AND p_type LIKE '%NICKEL' |
| AND s_nationkey = n_nationkey |
| AND n_regionkey = r_regionkey |
| AND r_name = 'ASIA' |
| 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 = 'ASIA' |
| ) |
| ORDER BY |
| s_acctbal desc, |
| n_name, |
| s_name, |
| p_partkey |
| LIMIT 100 |
| -- |
| ERROR: syntax error (25 : 5) |
| SELECT |
| ^ |
| == |
| |
| # Query 3 |
| SELECT |
| l_orderkey, |
| SUM(l_extendedprice * (1 - l_discount)) AS revenue, |
| o_orderdate, |
| o_shippriority |
| FROM |
| customer, |
| orders, |
| lineitem |
| WHERE |
| c_mktsegment = 'AUTOMOBILE' |
| AND c_custkey = o_custkey |
| AND l_orderkey = o_orderkey |
| AND o_orderdate < DATE '1995-03-17' |
| AND l_shipdate > DATE '1995-03-17' |
| GROUP BY |
| l_orderkey, |
| o_orderdate, |
| o_shippriority |
| ORDER BY |
| revenue desc, |
| o_orderdate |
| LIMIT 10 |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=l_orderkey] |
| | +-SelectListItem[alias=revenue] |
| | | +-FunctionCall[name=SUM] |
| | | +-Multiply |
| | | +-left_operand=AttributeReference[attribute_name=l_extendedprice] |
| | | +-right_operand=Subtract |
| | | +-left_operand=Literal |
| | | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | | +-right_operand=AttributeReference[attribute_name=l_discount] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=o_orderdate] |
| | +-SelectListItem |
| | +-AttributeReference[attribute_name=o_shippriority] |
| +-where_clause=And |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=c_mktsegment] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=AUTOMOBILE] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=c_custkey] |
| | | +-right_operand=AttributeReference[attribute_name=o_custkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=l_orderkey] |
| | | +-right_operand=AttributeReference[attribute_name=o_orderkey] |
| | +-Less |
| | | +-left_operand=AttributeReference[attribute_name=o_orderdate] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=1995-03-17,explicit_type=Datetime] |
| | +-Greater |
| | +-left_operand=AttributeReference[attribute_name=l_shipdate] |
| | +-right_operand=Literal |
| | +-StringLiteral[value=1995-03-17,explicit_type=Datetime] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=l_orderkey] |
| | +-AttributeReference[attribute_name=o_orderdate] |
| | +-AttributeReference[attribute_name=o_shippriority] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=false,nulls_first=true] |
| | | +-AttributeReference[attribute_name=revenue] |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | +-AttributeReference[attribute_name=o_orderdate] |
| +-limit=LIMIT |
| | +-NumericLiteral[numeric_string=10,float_like=false] |
| +-from_clause= |
| +-TableReference[table=customer] |
| +-TableReference[table=orders] |
| +-TableReference[table=lineitem] |
| == |
| |
| # Query 4 |
| SELECT |
| o_orderpriority, |
| COUNT(*) AS order_count |
| FROM |
| orders |
| WHERE |
| o_orderdate >= DATE '1995-08-01' |
| AND o_orderdate < DATE '1995-08-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 |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=o_orderpriority] |
| | +-SelectListItem[alias=order_count] |
| | +-FunctionCall[name=COUNT,is_star=true] |
| +-where_clause=And |
| | +-GreaterOrEqual |
| | | +-left_operand=AttributeReference[attribute_name=o_orderdate] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=1995-08-01,explicit_type=Datetime] |
| | +-Less |
| | | +-left_operand=AttributeReference[attribute_name=o_orderdate] |
| | | +-right_operand=Add |
| | | +-left_operand=Literal |
| | | | +-StringLiteral[value=1995-08-01,explicit_type=Datetime] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=3 month,explicit_type=YearMonthInterval] |
| | +-Exists |
| | +-subquery=SubqueryExpression |
| | +-Select |
| | +-select_clause=SelectStar |
| | +-where_clause=And |
| | | +-Equal |
| | | | +-left_operand=AttributeReference[attribute_name=l_orderkey] |
| | | | +-right_operand=AttributeReference[attribute_name=o_orderkey] |
| | | +-Less |
| | | +-left_operand=AttributeReference[attribute_name=l_commitdate] |
| | | +-right_operand=AttributeReference[attribute_name=l_receiptdate] |
| | +-from_clause= |
| | +-TableReference[table=lineitem] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=o_orderpriority] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | +-AttributeReference[attribute_name=o_orderpriority] |
| +-from_clause= |
| +-TableReference[table=orders] |
| == |
| |
| # 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 = 'AMERICA' |
| AND o_orderdate >= DATE '1997-01-01' |
| AND o_orderdate < DATE '1997-01-01' + INTERVAL '1 year' |
| GROUP BY |
| n_name |
| ORDER BY |
| revenue DESC |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=n_name] |
| | +-SelectListItem[alias=revenue] |
| | +-FunctionCall[name=SUM] |
| | +-Multiply |
| | +-left_operand=AttributeReference[attribute_name=l_extendedprice] |
| | +-right_operand=Subtract |
| | +-left_operand=Literal |
| | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | +-right_operand=AttributeReference[attribute_name=l_discount] |
| +-where_clause=And |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=c_custkey] |
| | | +-right_operand=AttributeReference[attribute_name=o_custkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=l_orderkey] |
| | | +-right_operand=AttributeReference[attribute_name=o_orderkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=l_suppkey] |
| | | +-right_operand=AttributeReference[attribute_name=s_suppkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=c_nationkey] |
| | | +-right_operand=AttributeReference[attribute_name=s_nationkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=s_nationkey] |
| | | +-right_operand=AttributeReference[attribute_name=n_nationkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=n_regionkey] |
| | | +-right_operand=AttributeReference[attribute_name=r_regionkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=r_name] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=AMERICA] |
| | +-GreaterOrEqual |
| | | +-left_operand=AttributeReference[attribute_name=o_orderdate] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=1997-01-01,explicit_type=Datetime] |
| | +-Less |
| | +-left_operand=AttributeReference[attribute_name=o_orderdate] |
| | +-right_operand=Add |
| | +-left_operand=Literal |
| | | +-StringLiteral[value=1997-01-01,explicit_type=Datetime] |
| | +-right_operand=Literal |
| | +-StringLiteral[value=1 year,explicit_type=YearMonthInterval] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=n_name] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=false,nulls_first=true] |
| | +-AttributeReference[attribute_name=revenue] |
| +-from_clause= |
| +-TableReference[table=customer] |
| +-TableReference[table=orders] |
| +-TableReference[table=lineitem] |
| +-TableReference[table=supplier] |
| +-TableReference[table=nation] |
| +-TableReference[table=region] |
| == |
| |
| # Query 6 |
| SELECT |
| SUM(l_extendedprice * l_discount) AS revenue |
| FROM |
| lineitem |
| WHERE |
| l_shipdate >= DATE '1997-01-01' |
| AND l_shipdate < DATE '1997-01-01' + INTERVAL '1 year' |
| AND l_discount BETWEEN 0.03 - 0.01 AND 0.03 + 0.01 |
| AND l_quantity < 25 |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem[alias=revenue] |
| | +-FunctionCall[name=SUM] |
| | +-Multiply |
| | +-left_operand=AttributeReference[attribute_name=l_extendedprice] |
| | +-right_operand=AttributeReference[attribute_name=l_discount] |
| +-where_clause=And |
| | +-GreaterOrEqual |
| | | +-left_operand=AttributeReference[attribute_name=l_shipdate] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=1997-01-01,explicit_type=Datetime] |
| | +-Less |
| | | +-left_operand=AttributeReference[attribute_name=l_shipdate] |
| | | +-right_operand=Add |
| | | +-left_operand=Literal |
| | | | +-StringLiteral[value=1997-01-01,explicit_type=Datetime] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=1 year,explicit_type=YearMonthInterval] |
| | +-Between |
| | | +-check_operand=AttributeReference[attribute_name=l_discount] |
| | | +-lower_bound_operand=Subtract |
| | | | +-left_operand=Literal |
| | | | | +-NumericLiteral[numeric_string=0.03,float_like=true] |
| | | | +-right_operand=Literal |
| | | | +-NumericLiteral[numeric_string=0.01,float_like=true] |
| | | +-upper_bound_operand=Add |
| | | +-left_operand=Literal |
| | | | +-NumericLiteral[numeric_string=0.03,float_like=true] |
| | | +-right_operand=Literal |
| | | +-NumericLiteral[numeric_string=0.01,float_like=true] |
| | +-Less |
| | +-left_operand=AttributeReference[attribute_name=l_quantity] |
| | +-right_operand=Literal |
| | +-NumericLiteral[numeric_string=25,float_like=false] |
| +-from_clause= |
| +-TableReference[table=lineitem] |
| == |
| |
| # 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 = 'ETHIOPIA' AND n2.n_name = 'UNITED STATES') |
| OR (n1.n_name = 'UNITED STATES' 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 |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=supp_nation] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=cust_nation] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=l_year] |
| | +-SelectListItem[alias=revenue] |
| | +-FunctionCall[name=SUM] |
| | +-AttributeReference[attribute_name=volume] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=supp_nation] |
| | +-AttributeReference[attribute_name=cust_nation] |
| | +-AttributeReference[attribute_name=l_year] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | | +-AttributeReference[attribute_name=supp_nation] |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | | +-AttributeReference[attribute_name=cust_nation] |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | +-AttributeReference[attribute_name=l_year] |
| +-from_clause= |
| +-SubqueryTable |
| +-table_signature=TableSignature[table_alias=shipping] |
| +-SubqueryExpression |
| +-Select |
| +-select_clause=SelectList |
| | +-SelectListItem[alias=supp_nation] |
| | | +-AttributeReference[attribute_name=n_name,relation_name=n1] |
| | +-SelectListItem[alias=cust_nation] |
| | | +-AttributeReference[attribute_name=n_name,relation_name=n2] |
| | +-SelectListItem[alias=l_year] |
| | | +-Extract[unit=year] |
| | | +-date_expression=AttributeReference[attribute_name=l_shipdate] |
| | +-SelectListItem[alias=volume] |
| | +-Multiply |
| | +-left_operand=AttributeReference[attribute_name=l_extendedprice] |
| | +-right_operand=Subtract |
| | +-left_operand=Literal |
| | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | +-right_operand=AttributeReference[attribute_name=l_discount] |
| +-where_clause=And |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=s_suppkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_suppkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=o_orderkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_orderkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=c_custkey] |
| | | +-right_operand=AttributeReference[attribute_name=o_custkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=s_nationkey] |
| | | +-right_operand=AttributeReference[attribute_name=n_nationkey, |
| | | relation_name=n1] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=c_nationkey] |
| | | +-right_operand=AttributeReference[attribute_name=n_nationkey, |
| | | relation_name=n2] |
| | +-Or |
| | | +-And |
| | | | +-Equal |
| | | | | +-left_operand=AttributeReference[attribute_name=n_name, |
| | | | | | relation_name=n1] |
| | | | | +-right_operand=Literal |
| | | | | +-StringLiteral[value=ETHIOPIA] |
| | | | +-Equal |
| | | | +-left_operand=AttributeReference[attribute_name=n_name, |
| | | | | relation_name=n2] |
| | | | +-right_operand=Literal |
| | | | +-StringLiteral[value=UNITED STATES] |
| | | +-And |
| | | +-Equal |
| | | | +-left_operand=AttributeReference[attribute_name=n_name, |
| | | | | relation_name=n1] |
| | | | +-right_operand=Literal |
| | | | +-StringLiteral[value=UNITED STATES] |
| | | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=n_name, |
| | | | relation_name=n2] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=ETHIOPIA] |
| | +-Between |
| | +-check_operand=AttributeReference[attribute_name=l_shipdate] |
| | +-lower_bound_operand=Literal |
| | | +-StringLiteral[value=1995-01-01,explicit_type=Datetime] |
| | +-upper_bound_operand=Literal |
| | +-StringLiteral[value=1996-12-31,explicit_type=Datetime] |
| +-from_clause= |
| +-TableReference[table=supplier] |
| +-TableReference[table=lineitem] |
| +-TableReference[table=orders] |
| +-TableReference[table=customer] |
| +-TableReference[table=nation] |
| | +-table_signature=TableSignature[table_alias=n1] |
| +-TableReference[table=nation] |
| +-table_signature=TableSignature[table_alias=n2] |
| == |
| |
| # Query 8 |
| SELECT |
| o_year, |
| SUM(CASE |
| WHEN nation = 'UNITED STATES' 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 = 'MEDIUM ANODIZED NICKEL' |
| ) AS all_nations |
| GROUP BY |
| o_year |
| ORDER BY |
| o_year |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=o_year] |
| | +-SelectListItem[alias=mkt_share] |
| | +-Divide |
| | +-left_operand=FunctionCall[name=SUM] |
| | | +-SearchedCaseExpression |
| | | +-else_result_expression=Literal |
| | | | +-NumericLiteral[numeric_string=0,float_like=false] |
| | | +-when_clauses= |
| | | +-SearchedWhenClause |
| | | +-condition_predicate=Equal |
| | | | +-left_operand=AttributeReference[attribute_name=nation] |
| | | | +-right_operand=Literal |
| | | | +-StringLiteral[value=UNITED STATES] |
| | | +-result_expression=AttributeReference[attribute_name=volume] |
| | +-right_operand=FunctionCall[name=SUM] |
| | +-AttributeReference[attribute_name=volume] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=o_year] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | +-AttributeReference[attribute_name=o_year] |
| +-from_clause= |
| +-SubqueryTable |
| +-table_signature=TableSignature[table_alias=all_nations] |
| +-SubqueryExpression |
| +-Select |
| +-select_clause=SelectList |
| | +-SelectListItem[alias=o_year] |
| | | +-Extract[unit=year] |
| | | +-date_expression=AttributeReference[attribute_name=o_orderdate] |
| | +-SelectListItem[alias=volume] |
| | | +-Multiply |
| | | +-left_operand=AttributeReference[attribute_name=l_extendedprice] |
| | | +-right_operand=Subtract |
| | | +-left_operand=Literal |
| | | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | | +-right_operand=AttributeReference[attribute_name=l_discount] |
| | +-SelectListItem[alias=nation] |
| | +-AttributeReference[attribute_name=n_name,relation_name=n2] |
| +-where_clause=And |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=p_partkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_partkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=s_suppkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_suppkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=l_orderkey] |
| | | +-right_operand=AttributeReference[attribute_name=o_orderkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=o_custkey] |
| | | +-right_operand=AttributeReference[attribute_name=c_custkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=c_nationkey] |
| | | +-right_operand=AttributeReference[attribute_name=n_nationkey, |
| | | relation_name=n1] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=n_regionkey, |
| | | | relation_name=n1] |
| | | +-right_operand=AttributeReference[attribute_name=r_regionkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=r_name] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=AMERICA] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=s_nationkey] |
| | | +-right_operand=AttributeReference[attribute_name=n_nationkey, |
| | | relation_name=n2] |
| | +-Between |
| | | +-check_operand=AttributeReference[attribute_name=o_orderdate] |
| | | +-lower_bound_operand=Literal |
| | | | +-StringLiteral[value=1995-01-01,explicit_type=Datetime] |
| | | +-upper_bound_operand=Literal |
| | | +-StringLiteral[value=1996-12-31,explicit_type=Datetime] |
| | +-Equal |
| | +-left_operand=AttributeReference[attribute_name=p_type] |
| | +-right_operand=Literal |
| | +-StringLiteral[value=MEDIUM ANODIZED NICKEL] |
| +-from_clause= |
| +-TableReference[table=part] |
| +-TableReference[table=supplier] |
| +-TableReference[table=lineitem] |
| +-TableReference[table=orders] |
| +-TableReference[table=customer] |
| +-TableReference[table=nation] |
| | +-table_signature=TableSignature[table_alias=n1] |
| +-TableReference[table=nation] |
| | +-table_signature=TableSignature[table_alias=n2] |
| +-TableReference[table=region] |
| == |
| |
| # Query 9 |
| SELECT |
| nation, |
| o_year, |
| SUM(amount) AS sum_profit |
| FROM |
| ( |
| SELECT |
| n_name AS nation, |
| EXTRACT(year FROM 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 '%ghost%' |
| ) AS profit |
| GROUP BY |
| nation, |
| o_year |
| ORDER BY |
| nation, |
| o_year DESC |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=nation] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=o_year] |
| | +-SelectListItem[alias=sum_profit] |
| | +-FunctionCall[name=SUM] |
| | +-AttributeReference[attribute_name=amount] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=nation] |
| | +-AttributeReference[attribute_name=o_year] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | | +-AttributeReference[attribute_name=nation] |
| | +-OrderByItem[is_asc=false,nulls_first=true] |
| | +-AttributeReference[attribute_name=o_year] |
| +-from_clause= |
| +-SubqueryTable |
| +-table_signature=TableSignature[table_alias=profit] |
| +-SubqueryExpression |
| +-Select |
| +-select_clause=SelectList |
| | +-SelectListItem[alias=nation] |
| | | +-AttributeReference[attribute_name=n_name] |
| | +-SelectListItem[alias=o_year] |
| | | +-Extract[unit=year] |
| | | +-date_expression=AttributeReference[attribute_name=o_orderdate] |
| | +-SelectListItem[alias=amount] |
| | +-Subtract |
| | +-left_operand=Multiply |
| | | +-left_operand=AttributeReference[ |
| | | | attribute_name=l_extendedprice] |
| | | +-right_operand=Subtract |
| | | +-left_operand=Literal |
| | | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | | +-right_operand=AttributeReference[attribute_name=l_discount] |
| | +-right_operand=Multiply |
| | +-left_operand=AttributeReference[attribute_name=ps_supplycost] |
| | +-right_operand=AttributeReference[attribute_name=l_quantity] |
| +-where_clause=And |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=s_suppkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_suppkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=ps_suppkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_suppkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=ps_partkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_partkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=p_partkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_partkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=o_orderkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_orderkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=s_nationkey] |
| | | +-right_operand=AttributeReference[attribute_name=n_nationkey] |
| | +-Like |
| | +-left_operand=AttributeReference[attribute_name=p_name] |
| | +-right_operand=Literal |
| | +-StringLiteral[value=%ghost%] |
| +-from_clause= |
| +-TableReference[table=part] |
| +-TableReference[table=supplier] |
| +-TableReference[table=lineitem] |
| +-TableReference[table=partsupp] |
| +-TableReference[table=orders] |
| +-TableReference[table=nation] |
| == |
| |
| # Query 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 >= DATE '1994-03-01' |
| AND o_orderdate < DATE '1994-03-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 |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=c_custkey] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=c_name] |
| | +-SelectListItem[alias=revenue] |
| | | +-FunctionCall[name=SUM] |
| | | +-Multiply |
| | | +-left_operand=AttributeReference[attribute_name=l_extendedprice] |
| | | +-right_operand=Subtract |
| | | +-left_operand=Literal |
| | | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | | +-right_operand=AttributeReference[attribute_name=l_discount] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=c_acctbal] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=n_name] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=c_address] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=c_phone] |
| | +-SelectListItem |
| | +-AttributeReference[attribute_name=c_comment] |
| +-where_clause=And |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=c_custkey] |
| | | +-right_operand=AttributeReference[attribute_name=o_custkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=l_orderkey] |
| | | +-right_operand=AttributeReference[attribute_name=o_orderkey] |
| | +-GreaterOrEqual |
| | | +-left_operand=AttributeReference[attribute_name=o_orderdate] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=1994-03-01,explicit_type=Datetime] |
| | +-Less |
| | | +-left_operand=AttributeReference[attribute_name=o_orderdate] |
| | | +-right_operand=Add |
| | | +-left_operand=Literal |
| | | | +-StringLiteral[value=1994-03-01,explicit_type=Datetime] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=3 month,explicit_type=YearMonthInterval] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=l_returnflag] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=R] |
| | +-Equal |
| | +-left_operand=AttributeReference[attribute_name=c_nationkey] |
| | +-right_operand=AttributeReference[attribute_name=n_nationkey] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=c_custkey] |
| | +-AttributeReference[attribute_name=c_name] |
| | +-AttributeReference[attribute_name=c_acctbal] |
| | +-AttributeReference[attribute_name=c_phone] |
| | +-AttributeReference[attribute_name=n_name] |
| | +-AttributeReference[attribute_name=c_address] |
| | +-AttributeReference[attribute_name=c_comment] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=false,nulls_first=true] |
| | +-AttributeReference[attribute_name=revenue] |
| +-limit=LIMIT |
| | +-NumericLiteral[numeric_string=20,float_like=false] |
| +-from_clause= |
| +-TableReference[table=customer] |
| +-TableReference[table=orders] |
| +-TableReference[table=lineitem] |
| +-TableReference[table=nation] |
| == |
| |
| # Query 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 = 'INDONESIA' |
| GROUP BY |
| ps_partkey HAVING |
| SUM(ps_supplycost * ps_availqty) > ( |
| SELECT |
| SUM(ps_supplycost * ps_availqty) * 0.0000010000 |
| FROM |
| partsupp, |
| supplier, |
| nation |
| WHERE |
| ps_suppkey = s_suppkey |
| AND s_nationkey = n_nationkey |
| AND n_name = 'INDONESIA' |
| ) |
| ORDER BY |
| value DESC |
| -- |
| ERROR: syntax error (15 : 7) |
| SELECT |
| ^ |
| == |
| |
| # Query 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 >= DATE '1997-01-01' |
| AND l_receiptdate < DATE '1997-01-01' + INTERVAL '1 year' |
| GROUP BY |
| l_shipmode |
| ORDER BY |
| l_shipmode |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=l_shipmode] |
| | +-SelectListItem[alias=high_line_count] |
| | | +-FunctionCall[name=SUM] |
| | | +-SearchedCaseExpression |
| | | +-else_result_expression=Literal |
| | | | +-NumericLiteral[numeric_string=0,float_like=false] |
| | | +-when_clauses= |
| | | +-SearchedWhenClause |
| | | +-condition_predicate=Or |
| | | | +-Equal |
| | | | | +-left_operand=AttributeReference[ |
| | | | | | attribute_name=o_orderpriority] |
| | | | | +-right_operand=Literal |
| | | | | +-StringLiteral[value=1-URGENT] |
| | | | +-Equal |
| | | | +-left_operand=AttributeReference[ |
| | | | | attribute_name=o_orderpriority] |
| | | | +-right_operand=Literal |
| | | | +-StringLiteral[value=2-HIGH] |
| | | +-result_expression=Literal |
| | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | +-SelectListItem[alias=low_line_count] |
| | +-FunctionCall[name=SUM] |
| | +-SearchedCaseExpression |
| | +-else_result_expression=Literal |
| | | +-NumericLiteral[numeric_string=0,float_like=false] |
| | +-when_clauses= |
| | +-SearchedWhenClause |
| | +-condition_predicate=And |
| | | +-NotEqual |
| | | | +-left_operand=AttributeReference[ |
| | | | | attribute_name=o_orderpriority] |
| | | | +-right_operand=Literal |
| | | | +-StringLiteral[value=1-URGENT] |
| | | +-NotEqual |
| | | +-left_operand=AttributeReference[ |
| | | | attribute_name=o_orderpriority] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=2-HIGH] |
| | +-result_expression=Literal |
| | +-NumericLiteral[numeric_string=1,float_like=false] |
| +-where_clause=And |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=o_orderkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_orderkey] |
| | +-InValueList |
| | | +-test_expression=AttributeReference[attribute_name=l_shipmode] |
| | | +-value_list= |
| | | +-Literal |
| | | | +-StringLiteral[value=REG AIR] |
| | | +-Literal |
| | | +-StringLiteral[value=RAIL] |
| | +-Less |
| | | +-left_operand=AttributeReference[attribute_name=l_commitdate] |
| | | +-right_operand=AttributeReference[attribute_name=l_receiptdate] |
| | +-Less |
| | | +-left_operand=AttributeReference[attribute_name=l_shipdate] |
| | | +-right_operand=AttributeReference[attribute_name=l_commitdate] |
| | +-GreaterOrEqual |
| | | +-left_operand=AttributeReference[attribute_name=l_receiptdate] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=1997-01-01,explicit_type=Datetime] |
| | +-Less |
| | +-left_operand=AttributeReference[attribute_name=l_receiptdate] |
| | +-right_operand=Add |
| | +-left_operand=Literal |
| | | +-StringLiteral[value=1997-01-01,explicit_type=Datetime] |
| | +-right_operand=Literal |
| | +-StringLiteral[value=1 year,explicit_type=YearMonthInterval] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=l_shipmode] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | +-AttributeReference[attribute_name=l_shipmode] |
| +-from_clause= |
| +-TableReference[table=orders] |
| +-TableReference[table=lineitem] |
| == |
| |
| # Query 13 |
| SELECT |
| c_count, |
| COUNT(*) AS custdist |
| FROM |
| ( |
| SELECT |
| c_custkey, |
| COUNT(o_orderkey) |
| 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 (c_custkey, c_count) |
| GROUP BY |
| c_count |
| ORDER BY |
| custdist desc, |
| c_count DESC |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=c_count] |
| | +-SelectListItem[alias=custdist] |
| | +-FunctionCall[name=COUNT,is_star=true] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=c_count] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=false,nulls_first=true] |
| | | +-AttributeReference[attribute_name=custdist] |
| | +-OrderByItem[is_asc=false,nulls_first=true] |
| | +-AttributeReference[attribute_name=c_count] |
| +-from_clause= |
| +-SubqueryTable |
| +-table_signature=TableSignature[table_alias=c_orders, |
| | columns=(c_custkey, c_count)] |
| +-SubqueryExpression |
| +-Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=c_custkey] |
| | +-SelectListItem |
| | +-FunctionCall[name=COUNT] |
| | +-AttributeReference[attribute_name=o_orderkey] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=c_custkey] |
| +-from_clause= |
| +-JoinedTable[join_type=LeftOuterJoin] |
| +-left_table=TableReference[table=customer] |
| +-right_table=TableReference[table=orders] |
| +-join_predicate=And |
| +-Equal |
| | +-left_operand=AttributeReference[attribute_name=c_custkey] |
| | +-right_operand=AttributeReference[attribute_name=o_custkey] |
| +-NotLike |
| +-left_operand=AttributeReference[attribute_name=o_comment] |
| +-right_operand=Literal |
| +-StringLiteral[value=%special%requests%] |
| == |
| |
| # Query 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 >= DATE '1994-11-01' |
| AND l_shipdate < DATE '1994-11-01' + INTERVAL '1 month' |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem[alias=promo_revenue] |
| | +-Divide |
| | +-left_operand=Multiply |
| | | +-left_operand=Literal |
| | | | +-NumericLiteral[numeric_string=100.00,float_like=true] |
| | | +-right_operand=FunctionCall[name=SUM] |
| | | +-SearchedCaseExpression |
| | | +-else_result_expression=Literal |
| | | | +-NumericLiteral[numeric_string=0,float_like=false] |
| | | +-when_clauses= |
| | | +-SearchedWhenClause |
| | | +-condition_predicate=Like |
| | | | +-left_operand=AttributeReference[attribute_name=p_type] |
| | | | +-right_operand=Literal |
| | | | +-StringLiteral[value=PROMO%] |
| | | +-result_expression=Multiply |
| | | +-left_operand=AttributeReference[ |
| | | | attribute_name=l_extendedprice] |
| | | +-right_operand=Subtract |
| | | +-left_operand=Literal |
| | | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | | +-right_operand=AttributeReference[ |
| | | attribute_name=l_discount] |
| | +-right_operand=FunctionCall[name=SUM] |
| | +-Multiply |
| | +-left_operand=AttributeReference[attribute_name=l_extendedprice] |
| | +-right_operand=Subtract |
| | +-left_operand=Literal |
| | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | +-right_operand=AttributeReference[attribute_name=l_discount] |
| +-where_clause=And |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=l_partkey] |
| | | +-right_operand=AttributeReference[attribute_name=p_partkey] |
| | +-GreaterOrEqual |
| | | +-left_operand=AttributeReference[attribute_name=l_shipdate] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=1994-11-01,explicit_type=Datetime] |
| | +-Less |
| | +-left_operand=AttributeReference[attribute_name=l_shipdate] |
| | +-right_operand=Add |
| | +-left_operand=Literal |
| | | +-StringLiteral[value=1994-11-01,explicit_type=Datetime] |
| | +-right_operand=Literal |
| | +-StringLiteral[value=1 month,explicit_type=YearMonthInterval] |
| +-from_clause= |
| +-TableReference[table=lineitem] |
| +-TableReference[table=part] |
| == |
| |
| # Query 15 |
| WITH revenue (supplier_no, total_revenue) AS ( |
| SELECT |
| l_suppkey, |
| sum(l_extendedprice * (1-l_discount)) |
| FROM |
| lineitem |
| WHERE |
| l_shipdate >= DATE '1996-11-01' |
| AND l_shipdate < DATE '1996-11-01' + INTERVAL '3 month' |
| GROUP BY |
| l_suppkey |
| ) SELECT |
| s_suppkey, |
| s_name, |
| s_address, |
| s_phone, |
| total_revenue |
| FROM |
| supplier, |
| revenue |
| WHERE |
| s_suppkey = supplier_no AND total_revenue = ( |
| SELECT |
| MAX(total_revenue) |
| FROM |
| revenue |
| ) |
| ORDER BY |
| s_suppkey |
| -- |
| ERROR: syntax error (23 : 1) |
| SELECT |
| ^ |
| == |
| |
| # Query 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#22' |
| AND p_type NOT LIKE 'ECONOMY BURNISHED%' |
| AND p_size IN (32, 42, 9, 18, 50, 30, 12, 21) |
| 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 |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=p_brand] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=p_type] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=p_size] |
| | +-SelectListItem[alias=supplier_cnt] |
| | +-FunctionCall[name=COUNT,is_distinct=true] |
| | +-AttributeReference[attribute_name=ps_suppkey] |
| +-where_clause=And |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=p_partkey] |
| | | +-right_operand=AttributeReference[attribute_name=ps_partkey] |
| | +-NotEqual |
| | | +-left_operand=AttributeReference[attribute_name=p_brand] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=Brand#22] |
| | +-NotLike |
| | | +-left_operand=AttributeReference[attribute_name=p_type] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=ECONOMY BURNISHED%] |
| | +-InValueList |
| | | +-test_expression=AttributeReference[attribute_name=p_size] |
| | | +-value_list= |
| | | +-Literal |
| | | | +-NumericLiteral[numeric_string=32,float_like=false] |
| | | +-Literal |
| | | | +-NumericLiteral[numeric_string=42,float_like=false] |
| | | +-Literal |
| | | | +-NumericLiteral[numeric_string=9,float_like=false] |
| | | +-Literal |
| | | | +-NumericLiteral[numeric_string=18,float_like=false] |
| | | +-Literal |
| | | | +-NumericLiteral[numeric_string=50,float_like=false] |
| | | +-Literal |
| | | | +-NumericLiteral[numeric_string=30,float_like=false] |
| | | +-Literal |
| | | | +-NumericLiteral[numeric_string=12,float_like=false] |
| | | +-Literal |
| | | +-NumericLiteral[numeric_string=21,float_like=false] |
| | +-Not |
| | +-InTableQuery |
| | +-test_expression=AttributeReference[attribute_name=ps_suppkey] |
| | +-table_query=SubqueryExpression |
| | +-Select |
| | +-select_clause=SelectList |
| | | +-SelectListItem |
| | | +-AttributeReference[attribute_name=s_suppkey] |
| | +-where_clause=Like |
| | | +-left_operand=AttributeReference[attribute_name=s_comment] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=%Customer%Complaints%] |
| | +-from_clause= |
| | +-TableReference[table=supplier] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=p_brand] |
| | +-AttributeReference[attribute_name=p_type] |
| | +-AttributeReference[attribute_name=p_size] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=false,nulls_first=true] |
| | | +-AttributeReference[attribute_name=supplier_cnt] |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | | +-AttributeReference[attribute_name=p_brand] |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | | +-AttributeReference[attribute_name=p_type] |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | +-AttributeReference[attribute_name=p_size] |
| +-from_clause= |
| +-TableReference[table=partsupp] |
| +-TableReference[table=part] |
| == |
| |
| # Query 17 |
| SELECT |
| SUM(l_extendedprice) / 7.0 AS avg_yearly |
| FROM |
| lineitem, |
| part |
| WHERE |
| p_partkey = l_partkey |
| AND p_brand = 'Brand#24' |
| AND p_container = 'JUMBO BOX' |
| AND l_quantity < ( |
| SELECT |
| 0.2 * AVG(l_quantity) |
| FROM |
| lineitem |
| WHERE |
| l_partkey = p_partkey |
| ) |
| -- |
| ERROR: syntax error (11 : 5) |
| SELECT |
| ^ |
| == |
| |
| # Query 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 |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=c_name] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=c_custkey] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=o_orderkey] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=o_orderdate] |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=o_totalprice] |
| | +-SelectListItem |
| | +-FunctionCall[name=sum] |
| | +-AttributeReference[attribute_name=l_quantity] |
| +-where_clause=And |
| | +-InTableQuery |
| | | +-test_expression=AttributeReference[attribute_name=o_orderkey] |
| | | +-table_query=SubqueryExpression |
| | | +-Select |
| | | +-select_clause=SelectList |
| | | | +-SelectListItem |
| | | | +-AttributeReference[attribute_name=l_orderkey] |
| | | +-group_by=GroupBy |
| | | | +-AttributeReference[attribute_name=l_orderkey] |
| | | +-having=HAVING |
| | | | +-Greater |
| | | | +-left_operand=FunctionCall[name=SUM] |
| | | | | +-AttributeReference[attribute_name=l_quantity] |
| | | | +-right_operand=Literal |
| | | | +-NumericLiteral[numeric_string=314,float_like=false] |
| | | +-from_clause= |
| | | +-TableReference[table=lineitem] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=c_custkey] |
| | | +-right_operand=AttributeReference[attribute_name=o_custkey] |
| | +-Equal |
| | +-left_operand=AttributeReference[attribute_name=o_orderkey] |
| | +-right_operand=AttributeReference[attribute_name=l_orderkey] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=c_name] |
| | +-AttributeReference[attribute_name=c_custkey] |
| | +-AttributeReference[attribute_name=o_orderkey] |
| | +-AttributeReference[attribute_name=o_orderdate] |
| | +-AttributeReference[attribute_name=o_totalprice] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=false,nulls_first=true] |
| | | +-AttributeReference[attribute_name=o_totalprice] |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | +-AttributeReference[attribute_name=o_orderdate] |
| +-limit=LIMIT |
| | +-NumericLiteral[numeric_string=100,float_like=false] |
| +-from_clause= |
| +-TableReference[table=customer] |
| +-TableReference[table=orders] |
| +-TableReference[table=lineitem] |
| == |
| |
| # Query 19 |
| SELECT |
| SUM(l_extendedprice* (1 - l_discount)) AS revenue |
| FROM |
| lineitem, |
| part |
| WHERE |
| ( |
| p_partkey = l_partkey |
| AND p_brand = 'Brand#45' |
| AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') |
| AND l_quantity >= 2 AND l_quantity <= 2 + 10 |
| AND p_size BETWEEN 1 AND 5 |
| AND l_shipmode IN ('AIR', 'AIR REG') |
| AND l_shipinstruct = 'DELIVER IN PERSON' |
| ) |
| OR |
| ( |
| p_partkey = l_partkey |
| AND p_brand = 'Brand#12' |
| AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') |
| AND l_quantity >= 13 AND l_quantity <= 13 + 10 |
| AND p_size BETWEEN 1 AND 10 |
| AND l_shipmode IN ('AIR', 'AIR REG') |
| AND l_shipinstruct = 'DELIVER IN PERSON' |
| ) |
| OR |
| ( |
| p_partkey = l_partkey |
| AND p_brand = 'Brand#53' |
| AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') |
| AND l_quantity >= 24 AND l_quantity <= 24 + 10 |
| AND p_size BETWEEN 1 AND 15 |
| AND l_shipmode IN ('AIR', 'AIR REG') |
| AND l_shipinstruct = 'DELIVER IN PERSON' |
| ) |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem[alias=revenue] |
| | +-FunctionCall[name=SUM] |
| | +-Multiply |
| | +-left_operand=AttributeReference[attribute_name=l_extendedprice] |
| | +-right_operand=Subtract |
| | +-left_operand=Literal |
| | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | +-right_operand=AttributeReference[attribute_name=l_discount] |
| +-where_clause=Or |
| | +-And |
| | | +-Equal |
| | | | +-left_operand=AttributeReference[attribute_name=p_partkey] |
| | | | +-right_operand=AttributeReference[attribute_name=l_partkey] |
| | | +-Equal |
| | | | +-left_operand=AttributeReference[attribute_name=p_brand] |
| | | | +-right_operand=Literal |
| | | | +-StringLiteral[value=Brand#45] |
| | | +-InValueList |
| | | | +-test_expression=AttributeReference[attribute_name=p_container] |
| | | | +-value_list= |
| | | | +-Literal |
| | | | | +-StringLiteral[value=SM CASE] |
| | | | +-Literal |
| | | | | +-StringLiteral[value=SM BOX] |
| | | | +-Literal |
| | | | | +-StringLiteral[value=SM PACK] |
| | | | +-Literal |
| | | | +-StringLiteral[value=SM PKG] |
| | | +-GreaterOrEqual |
| | | | +-left_operand=AttributeReference[attribute_name=l_quantity] |
| | | | +-right_operand=Literal |
| | | | +-NumericLiteral[numeric_string=2,float_like=false] |
| | | +-LessOrEqual |
| | | | +-left_operand=AttributeReference[attribute_name=l_quantity] |
| | | | +-right_operand=Add |
| | | | +-left_operand=Literal |
| | | | | +-NumericLiteral[numeric_string=2,float_like=false] |
| | | | +-right_operand=Literal |
| | | | +-NumericLiteral[numeric_string=10,float_like=false] |
| | | +-Between |
| | | | +-check_operand=AttributeReference[attribute_name=p_size] |
| | | | +-lower_bound_operand=Literal |
| | | | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | | | +-upper_bound_operand=Literal |
| | | | +-NumericLiteral[numeric_string=5,float_like=false] |
| | | +-InValueList |
| | | | +-test_expression=AttributeReference[attribute_name=l_shipmode] |
| | | | +-value_list= |
| | | | +-Literal |
| | | | | +-StringLiteral[value=AIR] |
| | | | +-Literal |
| | | | +-StringLiteral[value=AIR REG] |
| | | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=l_shipinstruct] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=DELIVER IN PERSON] |
| | +-And |
| | | +-Equal |
| | | | +-left_operand=AttributeReference[attribute_name=p_partkey] |
| | | | +-right_operand=AttributeReference[attribute_name=l_partkey] |
| | | +-Equal |
| | | | +-left_operand=AttributeReference[attribute_name=p_brand] |
| | | | +-right_operand=Literal |
| | | | +-StringLiteral[value=Brand#12] |
| | | +-InValueList |
| | | | +-test_expression=AttributeReference[attribute_name=p_container] |
| | | | +-value_list= |
| | | | +-Literal |
| | | | | +-StringLiteral[value=MED BAG] |
| | | | +-Literal |
| | | | | +-StringLiteral[value=MED BOX] |
| | | | +-Literal |
| | | | | +-StringLiteral[value=MED PKG] |
| | | | +-Literal |
| | | | +-StringLiteral[value=MED PACK] |
| | | +-GreaterOrEqual |
| | | | +-left_operand=AttributeReference[attribute_name=l_quantity] |
| | | | +-right_operand=Literal |
| | | | +-NumericLiteral[numeric_string=13,float_like=false] |
| | | +-LessOrEqual |
| | | | +-left_operand=AttributeReference[attribute_name=l_quantity] |
| | | | +-right_operand=Add |
| | | | +-left_operand=Literal |
| | | | | +-NumericLiteral[numeric_string=13,float_like=false] |
| | | | +-right_operand=Literal |
| | | | +-NumericLiteral[numeric_string=10,float_like=false] |
| | | +-Between |
| | | | +-check_operand=AttributeReference[attribute_name=p_size] |
| | | | +-lower_bound_operand=Literal |
| | | | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | | | +-upper_bound_operand=Literal |
| | | | +-NumericLiteral[numeric_string=10,float_like=false] |
| | | +-InValueList |
| | | | +-test_expression=AttributeReference[attribute_name=l_shipmode] |
| | | | +-value_list= |
| | | | +-Literal |
| | | | | +-StringLiteral[value=AIR] |
| | | | +-Literal |
| | | | +-StringLiteral[value=AIR REG] |
| | | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=l_shipinstruct] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=DELIVER IN PERSON] |
| | +-And |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=p_partkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_partkey] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=p_brand] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=Brand#53] |
| | +-InValueList |
| | | +-test_expression=AttributeReference[attribute_name=p_container] |
| | | +-value_list= |
| | | +-Literal |
| | | | +-StringLiteral[value=LG CASE] |
| | | +-Literal |
| | | | +-StringLiteral[value=LG BOX] |
| | | +-Literal |
| | | | +-StringLiteral[value=LG PACK] |
| | | +-Literal |
| | | +-StringLiteral[value=LG PKG] |
| | +-GreaterOrEqual |
| | | +-left_operand=AttributeReference[attribute_name=l_quantity] |
| | | +-right_operand=Literal |
| | | +-NumericLiteral[numeric_string=24,float_like=false] |
| | +-LessOrEqual |
| | | +-left_operand=AttributeReference[attribute_name=l_quantity] |
| | | +-right_operand=Add |
| | | +-left_operand=Literal |
| | | | +-NumericLiteral[numeric_string=24,float_like=false] |
| | | +-right_operand=Literal |
| | | +-NumericLiteral[numeric_string=10,float_like=false] |
| | +-Between |
| | | +-check_operand=AttributeReference[attribute_name=p_size] |
| | | +-lower_bound_operand=Literal |
| | | | +-NumericLiteral[numeric_string=1,float_like=false] |
| | | +-upper_bound_operand=Literal |
| | | +-NumericLiteral[numeric_string=15,float_like=false] |
| | +-InValueList |
| | | +-test_expression=AttributeReference[attribute_name=l_shipmode] |
| | | +-value_list= |
| | | +-Literal |
| | | | +-StringLiteral[value=AIR] |
| | | +-Literal |
| | | +-StringLiteral[value=AIR REG] |
| | +-Equal |
| | +-left_operand=AttributeReference[attribute_name=l_shipinstruct] |
| | +-right_operand=Literal |
| | +-StringLiteral[value=DELIVER IN PERSON] |
| +-from_clause= |
| +-TableReference[table=lineitem] |
| +-TableReference[table=part] |
| == |
| |
| # Query 20 |
| SELECT |
| s_name, |
| s_address |
| FROM |
| supplier, |
| nation |
| WHERE |
| s_suppkey IN ( |
| SELECT |
| ps_suppkey |
| FROM |
| partsupp |
| WHERE |
| ps_partkey IN ( |
| SELECT |
| p_partkey |
| FROM |
| part |
| WHERE |
| p_name LIKE 'sandy%' |
| ) |
| AND ps_availqty > ( |
| SELECT |
| 0.5 * SUM(l_quantity) |
| FROM |
| lineitem |
| WHERE |
| l_partkey = ps_partkey |
| AND l_suppkey = ps_suppkey |
| AND l_shipdate >= DATE '1993-01-01' |
| AND l_shipdate < DATE '1993-01-01' + INTERVAL '1 year' |
| ) |
| ) |
| AND s_nationkey = n_nationkey |
| AND n_name = 'GERMANY' |
| ORDER BY |
| s_name |
| -- |
| ERROR: syntax error (23 : 9) |
| SELECT |
| ^ |
| == |
| |
| # Query 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 = 'CANADA' |
| GROUP BY |
| s_name |
| ORDER BY |
| numwait desc, |
| s_name |
| LIMIT 100 |
| -- |
| SelectStatement |
| +-select_query=Select |
| +-select_clause=SelectList |
| | +-SelectListItem |
| | | +-AttributeReference[attribute_name=s_name] |
| | +-SelectListItem[alias=numwait] |
| | +-FunctionCall[name=count,is_star=true] |
| +-where_clause=And |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=s_suppkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_suppkey, |
| | | relation_name=l1] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=o_orderkey] |
| | | +-right_operand=AttributeReference[attribute_name=l_orderkey, |
| | | relation_name=l1] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=o_orderstatus] |
| | | +-right_operand=Literal |
| | | +-StringLiteral[value=F] |
| | +-Greater |
| | | +-left_operand=AttributeReference[attribute_name=l_receiptdate, |
| | | | relation_name=l1] |
| | | +-right_operand=AttributeReference[attribute_name=l_commitdate, |
| | | relation_name=l1] |
| | +-Exists |
| | | +-subquery=SubqueryExpression |
| | | +-Select |
| | | +-select_clause=SelectStar |
| | | +-where_clause=And |
| | | | +-Equal |
| | | | | +-left_operand=AttributeReference[attribute_name=l_orderkey, |
| | | | | | relation_name=l2] |
| | | | | +-right_operand=AttributeReference[attribute_name=l_orderkey, |
| | | | | relation_name=l1] |
| | | | +-NotEqual |
| | | | +-left_operand=AttributeReference[attribute_name=l_suppkey, |
| | | | | relation_name=l2] |
| | | | +-right_operand=AttributeReference[attribute_name=l_suppkey, |
| | | | relation_name=l1] |
| | | +-from_clause= |
| | | +-TableReference[table=lineitem] |
| | | +-table_signature=TableSignature[table_alias=l2] |
| | +-Not |
| | | +-Exists |
| | | +-subquery=SubqueryExpression |
| | | +-Select |
| | | +-select_clause=SelectStar |
| | | +-where_clause=And |
| | | | +-Equal |
| | | | | +-left_operand=AttributeReference[attribute_name=l_orderkey, |
| | | | | | relation_name=l3] |
| | | | | +-right_operand=AttributeReference[attribute_name=l_orderkey, |
| | | | | relation_name=l1] |
| | | | +-NotEqual |
| | | | | +-left_operand=AttributeReference[attribute_name=l_suppkey, |
| | | | | | relation_name=l3] |
| | | | | +-right_operand=AttributeReference[attribute_name=l_suppkey, |
| | | | | relation_name=l1] |
| | | | +-Greater |
| | | | +-left_operand=AttributeReference[attribute_name=l_receiptdate, |
| | | | | relation_name=l3] |
| | | | +-right_operand=AttributeReference[attribute_name=l_commitdate, |
| | | | relation_name=l3] |
| | | +-from_clause= |
| | | +-TableReference[table=lineitem] |
| | | +-table_signature=TableSignature[table_alias=l3] |
| | +-Equal |
| | | +-left_operand=AttributeReference[attribute_name=s_nationkey] |
| | | +-right_operand=AttributeReference[attribute_name=n_nationkey] |
| | +-Equal |
| | +-left_operand=AttributeReference[attribute_name=n_name] |
| | +-right_operand=Literal |
| | +-StringLiteral[value=CANADA] |
| +-group_by=GroupBy |
| | +-AttributeReference[attribute_name=s_name] |
| +-order_by=OrderBy |
| | +-OrderByItem[is_asc=false,nulls_first=true] |
| | | +-AttributeReference[attribute_name=numwait] |
| | +-OrderByItem[is_asc=true,nulls_first=false] |
| | +-AttributeReference[attribute_name=s_name] |
| +-limit=LIMIT |
| | +-NumericLiteral[numeric_string=100,float_like=false] |
| +-from_clause= |
| +-TableReference[table=supplier] |
| +-TableReference[table=lineitem] |
| | +-table_signature=TableSignature[table_alias=l1] |
| +-TableReference[table=orders] |
| +-TableReference[table=nation] |
| == |
| |
| # Query 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 |
| ('27', '44', '34', '25', '30', '33', '23') |
| AND c_acctbal > ( |
| SELECT |
| AVG(c_acctbal) |
| FROM |
| customer |
| WHERE |
| c_acctbal > 0.00 |
| AND SUBSTR(c_phone, 1, 2) IN |
| ('27', '44', '34', '25', '30', '33', '23') |
| ) |
| AND NOT EXISTS ( |
| SELECT * |
| FROM |
| orders |
| WHERE |
| o_custkey = c_custkey |
| ) |
| ) AS custsale |
| GROUP BY |
| cntrycode |
| ORDER BY |
| cntrycode |
| -- |
| ERROR: syntax error (16 : 9) |
| SELECT |
| ^ |
