This topic describes how to query data in foreign tables and internal tables in Hologres. This helps you experience the performance of data queries in Hologres.

Prerequisites

Background information

Hologres can give quick response when you query data. This topic shows you how to create a database, create foreign tables and internal tables, and then import data to the internal tables in Hologres. Then, this topic shows you how to query data in the foreign tables and internal tables in Hologres. This helps you experience data queries in Hologres.

Procedure

  1. Step 1: Create a database
    This step shows you how to create a database in Hologres. You can use the database to store the data of your Hologres instance for later queries.
  2. Step 2: Create tables
    This step shows you how to create tables in the created database. You can use the tables to store sample data. Hologres allows you to create foreign tables and internal tables. These two types of tables have the following characteristics:
    • Foreign tables map fields in external data sources and do not store data in Hologres.
    • Internal tables store data in Hologres.
  3. Step 3: Import sample data
    This step shows you how to import data to internal tables in Hologres for later queries. In this example, the data of the TPC Benchmark™ H (TPC-H) benchmark test is used.
  4. Step 4: Query data in tables
    This step shows you how to query data in tables. In this example, the 22 TPC-H query statements are used to help you experience the timeliness and stability of data queries in Hologres.

Step 1: Create a database

  1. Log on to the Hologres console. In the left-side navigation pane, click Instances.
  2. On the Instances page, click the name of your Hologres instance.
  3. In the left-side pane of the instance details page, click Databases.
  4. On the Database Authorization page, click Create Database in the upper-right corner.
    Create Database
  5. In the Create Database dialog box, set the parameters that are described in the following table.
    Create Database
    Parameter Description
    Instance Name The name of the Hologres instance in which you want to create the database. By default, the name of the connected instance is displayed. You can also select another Hologres instance from the drop-down list.
    Database Name The name of the database. In this example, enter tpch_10g.
    Note The database name must be unique.
    SPM The permission model of the database. Valid values:
    • SPM: the simple permission model (SPM). If you select SPM, permissions are granted at the database level. The following user groups are provided: admin, developer, writer, and viewer. You need to use only a few functions to manage the permissions on the objects in a database in a convenient and secure way.
    • SLPM: the schema-level permission model (SLPM). If you select SLPM, permissions are granted at the schema level. The following user groups are provided: <db>.admin, <db>.<schema>.developer, <db>.<schema>.writer, and <db>.<schema>.viewer. Compared with the SPM, the SLPM manages permissions in a finer-grained manner.
    • Expert: the standard PostgreSQL authorization model. Hologres is compatible with PostgreSQL. If you select Expert, you can use the standard PostgreSQL authorization model.

Step 2: Create tables

After the database is created, you can create tables in the database. You can create foreign tables or internal tables based on where your data is stored.

  • Create foreign tables.
    1. Log on to the database.
      1. On the Database Authorization page of the HoloWeb console, click Metadata Management in the top navigation bar.
      2. On the Metadata Management tab, find the created database in the left-side navigation pane and double-click the database name. In the dialog box that appears, click OK. Log On to Database
    2. Create foreign tables.
      1. Go to the SQL Editor tab. Click the Ad-hoc Query icon in the upper-left corner.
      2. On the Ad-hoc Query tab, select an instance from the Instance drop-down list and a database from the Database drop-down list, enter the following sample statements in the SQL editor, and then click Run.

        The following SQL statements are used to create foreign tables for later queries. The foreign tables map fields in the source tables such as the odps_customer_10g and odps_lineitem_10g tables in the MaxCompute project MAXCOMPUTE_PUBLIC_DATA in the public dataset.

        DROP FOREIGN TABLE IF EXISTS odps_customer_10g;
        DROP FOREIGN TABLE IF EXISTS odps_lineitem_10g;
        DROP FOREIGN TABLE IF EXISTS odps_nation_10g;
        DROP FOREIGN TABLE IF EXISTS odps_orders_10g;
        DROP FOREIGN TABLE IF EXISTS odps_part_10g;
        DROP FOREIGN TABLE IF EXISTS odps_partsupp_10g;
        DROP FOREIGN TABLE IF EXISTS odps_region_10g;
        DROP FOREIGN TABLE IF EXISTS odps_supplier_10g;
        
        
        IMPORT FOREIGN SCHEMA MAXCOMPUTE_PUBLIC_DATA LIMIT to
        (
            odps_customer_10g,
            odps_lineitem_10g,
              odps_nation_10g,
            odps_orders_10g,
              odps_part_10g,
            odps_partsupp_10g,
              odps_region_10g,
            odps_supplier_10g
        ) 
        FROM SERVER odps_server INTO public OPTIONS(if_table_exist 'error',if_unsupported_type 'error');
  • Create internal tables.
    1. Log on to the database.
      1. On the Database Authorization page of the HoloWeb console, click Metadata Management in the top navigation bar.
      2. On the Metadata Management tab, find the created database in the left-side navigation pane and double-click the database name. In the dialog box that appears, click OK. Log On to Database
    2. Create internal tables.
      1. Go to the SQL Editor tab. Click the Ad-hoc Query icon in the upper-left corner.
      2. On the Ad-hoc Query tab, select an instance from the Instance drop-down list and a database from the Database drop-down list, enter the following sample statements in the SQL editor, and then click Run.

        The following SQL statements are used to create internal tables named LINEITEM, ORDERS, PARTSUPP, PART, CUSTOMER, SUPPLIER, NATION, and REGION. The internal tables are used to store data for later queries.

        DROP TABLE IF EXISTS LINEITEM;
        
        BEGIN;
        CREATE TABLE LINEITEM
        (
            L_ORDERKEY      BIGINT      NOT NULL,
            L_PARTKEY       INT         NOT NULL,
            L_SUPPKEY       INT         NOT NULL,
            L_LINENUMBER    INT         NOT NULL,
            L_QUANTITY      DECIMAL(15,2) NOT NULL,
            L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
            L_DISCOUNT      DECIMAL(15,2) NOT NULL,
            L_TAX           DECIMAL(15,2) NOT NULL,
            L_RETURNFLAG    TEXT        NOT NULL,
            L_LINESTATUS    TEXT        NOT NULL,
            L_SHIPDATE      TIMESTAMPTZ NOT NULL,
            L_COMMITDATE    TIMESTAMPTZ NOT NULL,
            L_RECEIPTDATE   TIMESTAMPTZ NOT NULL,
            L_SHIPINSTRUCT  TEXT        NOT NULL,
            L_SHIPMODE      TEXT        NOT NULL,
            L_COMMENT       TEXT        NOT NULL,
            PRIMARY KEY (L_ORDERKEY,L_LINENUMBER)
        );
        CALL set_table_property('LINEITEM', 'clustering_key', 'L_SHIPDATE,L_ORDERKEY');
        CALL set_table_property('LINEITEM', 'segment_key', 'L_SHIPDATE');
        CALL set_table_property('LINEITEM', 'distribution_key', 'L_ORDERKEY');
        CALL set_table_property('LINEITEM', 'bitmap_columns', 'L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT');
        CALL set_table_property('LINEITEM', 'dictionary_encoding_columns', 'L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT');
        CALL set_table_property('LINEITEM', 'time_to_live_in_seconds', '31536000');
        COMMIT;
        
        DROP TABLE IF EXISTS ORDERS;
        
        BEGIN;
        CREATE TABLE ORDERS
        (
            O_ORDERKEY      BIGINT         NOT NULL PRIMARY KEY,
            O_CUSTKEY       INT         NOT NULL,
            O_ORDERSTATUS   TEXT        NOT NULL,
            O_TOTALPRICE    DECIMAL(15,2) NOT NULL,
            O_ORDERDATE     timestamptz NOT NULL,
            O_ORDERPRIORITY TEXT        NOT NULL,
            O_CLERK         TEXT        NOT NULL,
            O_SHIPPRIORITY  INT         NOT NULL,
            O_COMMENT       TEXT        NOT NULL
        );
        CALL set_table_property('ORDERS', 'segment_key', 'O_ORDERDATE');
        CALL set_table_property('ORDERS', 'distribution_key', 'O_ORDERKEY');
        CALL set_table_property('ORDERS', 'colocate_with', 'LINEITEM');
        CALL set_table_property('ORDERS', 'bitmap_columns', 'O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT');
        CALL set_table_property('ORDERS', 'dictionary_encoding_columns', 'O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_COMMENT');
        CALL set_table_property('ORDERS', 'time_to_live_in_seconds', '31536000');
        COMMIT;
        
        DROP TABLE IF EXISTS PARTSUPP;
        
        BEGIN;
        CREATE TABLE PARTSUPP
        (
            PS_PARTKEY    INT    NOT NULL,
            PS_SUPPKEY    INT    NOT NULL,
            PS_AVAILQTY   INT    NOT NULL,
            PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
            PS_COMMENT    TEXT   NOT NULL,
            PRIMARY KEY(PS_PARTKEY,PS_SUPPKEY)
        );
        CALL set_table_property('PARTSUPP', 'distribution_key', 'PS_PARTKEY');
        CALL set_table_property('PARTSUPP', 'colocate_with', 'LINEITEM');
        CALL set_table_property('PARTSUPP', 'bitmap_columns', 'PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY,PS_COMMENT');
        CALL set_table_property('PARTSUPP', 'dictionary_encoding_columns', 'PS_COMMENT');
        CALL set_table_property('PARTSUPP', 'time_to_live_in_seconds', '31536000');
        COMMIT;
        
        DROP TABLE IF EXISTS PART;
        
        BEGIN;
        CREATE TABLE PART
        (
            P_PARTKEY     INT    NOT NULL PRIMARY KEY,
            P_NAME        TEXT   NOT NULL,
            P_MFGR        TEXT   NOT NULL,
            P_BRAND       TEXT   NOT NULL,
            P_TYPE        TEXT   NOT NULL,
            P_SIZE        INT    NOT NULL,
            P_CONTAINER   TEXT   NOT NULL,
            P_RETAILPRICE DECIMAL(15,2) NOT NULL,
            P_COMMENT     TEXT   NOT NULL
        );
        CALL set_table_property('PART', 'distribution_key', 'P_PARTKEY');
        CALL set_table_property('PART', 'colocate_with', 'LINEITEM');
        CALL set_table_property('PART', 'bitmap_columns', 'P_PARTKEY,P_SIZE,P_NAME,P_MFGR,P_BRAND,P_TYPE,P_CONTAINER,P_COMMENT');
        CALL set_table_property('PART', 'dictionary_encoding_columns', 'P_NAME,P_MFGR,P_BRAND,P_TYPE,P_CONTAINER,P_COMMENT');
        CALL set_table_property('PART', 'time_to_live_in_seconds', '31536000');
        COMMIT;
        
        
        
        DROP TABLE IF EXISTS CUSTOMER;
        BEGIN;
        CREATE TABLE CUSTOMER
        (
            C_CUSTKEY    INT    NOT NULL PRIMARY KEY,
            C_NAME       TEXT   NOT NULL,
            C_ADDRESS    TEXT   NOT NULL,
            C_NATIONKEY  INT    NOT NULL,
            C_PHONE      TEXT   NOT NULL,
            C_ACCTBAL    DECIMAL(15,2) NOT NULL,
            C_MKTSEGMENT TEXT   NOT NULL,
            C_COMMENT    TEXT   NOT NULL
        );
        CALL set_table_property('CUSTOMER', 'distribution_key', 'C_CUSTKEY');
        CALL set_table_property('CUSTOMER', 'colocate_with', 'LINEITEM');
        CALL set_table_property('CUSTOMER', 'bitmap_columns', 'C_CUSTKEY,C_NATIONKEY,C_NAME,C_ADDRESS,C_PHONE,C_MKTSEGMENT,C_COMMENT');
        CALL set_table_property('CUSTOMER', 'dictionary_encoding_columns', 'C_NAME,C_ADDRESS,C_PHONE,C_MKTSEGMENT,C_COMMENT');
        CALL set_table_property('CUSTOMER', 'time_to_live_in_seconds', '31536000');
        COMMIT;
        
        DROP TABLE IF EXISTS SUPPLIER;
        
        BEGIN;
        CREATE TABLE SUPPLIER
        (
            S_SUPPKEY   INT    NOT NULL PRIMARY KEY,
            S_NAME      TEXT   NOT NULL,
            S_ADDRESS   TEXT   NOT NULL,
            S_NATIONKEY INT    NOT NULL,
            S_PHONE     TEXT   NOT NULL,
            S_ACCTBAL   DECIMAL(15,2) NOT NULL,
            S_COMMENT   TEXT   NOT NULL
        );
        CALL set_table_property('SUPPLIER', 'distribution_key', 'S_SUPPKEY');
        CALL set_table_property('SUPPLIER', 'colocate_with', 'LINEITEM');
        CALL set_table_property('SUPPLIER', 'bitmap_columns', 'S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_COMMENT');
        CALL set_table_property('SUPPLIER', 'dictionary_encoding_columns', 'S_NAME,S_ADDRESS,S_PHONE,S_COMMENT');
        CALL set_table_property('SUPPLIER', 'time_to_live_in_seconds', '31536000');
        COMMIT;
        
        DROP TABLE IF EXISTS NATION;
        
        BEGIN;
        CREATE TABLE NATION(
          N_NATIONKEY INT NOT NULL PRIMARY KEY,
          N_NAME text NOT NULL,
          N_REGIONKEY INT NOT NULL,
          N_COMMENT text NOT NULL
        );
        CALL set_table_property('NATION', 'distribution_key', 'N_NATIONKEY');
        CALL set_table_property('NATION', 'colocate_with', 'LINEITEM');
        CALL set_table_property('NATION', 'bitmap_columns', 'N_NATIONKEY,N_NAME,N_REGIONKEY,N_COMMENT');
        CALL set_table_property('NATION', 'dictionary_encoding_columns', 'N_NAME,N_COMMENT');
        CALL set_table_property('NATION', 'time_to_live_in_seconds', '31536000');
        COMMIT;
        
        DROP TABLE IF EXISTS REGION;
        
        BEGIN;
        CREATE TABLE REGION
        (
            R_REGIONKEY INT  NOT NULL PRIMARY KEY,
            R_NAME      TEXT NOT NULL,
            R_COMMENT   TEXT
        );
        CALL set_table_property('REGION', 'distribution_key', 'R_REGIONKEY');
        CALL set_table_property('REGION', 'colocate_with', 'LINEITEM');
        CALL set_table_property('REGION', 'bitmap_columns', 'R_REGIONKEY,R_NAME,R_COMMENT');
        CALL set_table_property('REGION', 'dictionary_encoding_columns', 'R_NAME,R_COMMENT');
        CALL set_table_property('REGION', 'time_to_live_in_seconds', '31536000');
        COMMIT;

Step 3: Import sample data

After the internal tables are created, you can perform the following steps to import data to the internal tables in Hologres.

Note Foreign tables map fields in external data sources and do not store data in Hologres. You can use foreign tables in Hologres to query the data that is stored in the MaxCompute project MAXCOMPUTE_PUBLIC_DATA in the public dataset.
  1. Go to the SQL Editor tab. Click the Ad-hoc Query icon in the upper-left corner.
  2. On the Ad-hoc Query tab, select an instance from the Instance drop-down list and a database from the Database drop-down list, enter the following sample statements in the SQL editor, and then click Run.
    The following SQL statements are used to import data for later queries. The data is imported from tables such as the public.odps_customer_10g and public.odps_lineitem_10g tables in the MaxCompute project MAXCOMPUTE_PUBLIC_DATA in the public dataset to the internal tables with corresponding names in Hologres.
    INSERT INTO public.customer SELECT * FROM public.odps_customer_10g ;
    INSERT INTO public.lineitem SELECT * FROM public.odps_lineitem_10g ;
    INSERT INTO public.nation SELECT * FROM public.odps_nation_10g ;
    INSERT INTO public.orders SELECT * FROM public.odps_orders_10g ;
    INSERT INTO public.part SELECT * FROM public.odps_part_10g ;
    INSERT INTO public.partsupp SELECT * FROM public.odps_partsupp_10g ;
    INSERT INTO public.region SELECT * FROM public.odps_region_10g ;
    INSERT INTO public.supplier SELECT * FROM public.odps_supplier_10g ;
    
    vacuum  nation;
    vacuum  region;
    vacuum  supplier;
    vacuum  customer;
    vacuum  part;
    vacuum  partsupp;
    vacuum  orders;
    vacuum  lineitem;
    
    analyze nation;
    analyze region;
    analyze lineitem;
    analyze orders;
    analyze customer;
    analyze part;
    analyze partsupp;
    analyze supplier;
    analyze lineitem (l_orderkey,l_partkey,l_suppkey);
    analyze orders (o_custkey);
    analyze partsupp(ps_partkey,ps_suppkey);

Step 4: Query data in tables

  1. Go to the SQL Editor tab. Click the Ad-hoc Query icon in the upper-left corner.
  2. On the Ad-hoc Query tab, select an instance from the Instance drop-down list and a database from the Database drop-down list, enter the following sample statements in the SQL editor, and then click Run.
    Note The following SQL statements are used to query data in internal tables. To query data in foreign tables, replace the table name to be queried with the name of the foreign table that you want to query.
    The following table provides the links to the 22 TPC-H query statements. To view a specific query statement, you can click the corresponding link in the table.
    Item Query statement
    22 TPC-H query statements Q1 Q2 Q3 Q4
    Q5 Q6 Q7 Q8
    Q9 Q10 Q11 Q12
    Q13 Q14 Q15 Q16
    Q17 Q18 Q19 Q20
    Q21 Q22 - -
    • Q1
      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 '120' day
      group by
              l_returnflag,
              l_linestatus
      order by
              l_returnflag,
              l_linestatus;
    • Q2
      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 '%STEEL'
              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
      limit 100;
    • Q3
      select
              l_orderkey,
              sum(l_extendedprice * (1 - l_discount)) as revenue,
              o_orderdate,
              o_shippriority
      from
              customer,
              orders,
              lineitem
      where
              c_mktsegment = 'MACHINERY'
              and c_custkey = o_custkey
              and l_orderkey = o_orderkey
              and o_orderdate < date '1995-03-23'
              and l_shipdate > date '1995-03-23'
      group by
              l_orderkey,
              o_orderdate,
              o_shippriority
      order by
              revenue desc,
              o_orderdate
      limit 10;
    • Q4
      select
              o_orderpriority,
              count(*) as order_count
      from
              orders
      where
              o_orderdate >= date '1996-07-01'
              and o_orderdate < date '1996-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;
    • Q5
      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 = 'EUROPE'
              and o_orderdate >= date '1996-01-01'
              and o_orderdate < date '1996-01-01' + interval '1' year
      group by
              n_name
      order by
              revenue desc;
    • Q6
      select
              sum(l_extendedprice * l_discount) as revenue
      from
              lineitem
      where
              l_shipdate >= date '1996-01-01'
              and l_shipdate < date '1996-01-01' + interval '1' year
              and l_discount between 0.02 - 0.01 and 0.02 + 0.01
              and l_quantity < 24;
    • Q7
      set hg_experimental_enable_double_equivalent=on;
      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 = 'CANADA' and n2.n_name = 'BRAZIL')
                                      or (n1.n_name = 'BRAZIL' and n2.n_name = 'CANADA')
                              )
                              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;
    • Q8
      set hg_experimental_enable_double_equivalent=on;
      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 = 'LARGE ANODIZED COPPER'
              ) as all_nations
      group by
              o_year
      order by
              o_year;
    • Q9
      set hg_experimental_enable_double_equivalent=on;
      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 '%maroon%'
              ) as profit
      group by
              nation,
              o_year
      order by
              nation,
              o_year desc;
    • Q10
      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 '1993-02-01'
              and o_orderdate < date '1993-02-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;
    • Q11
      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 = 'EGYPT'
      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 = 'EGYPT'
                      )
      order by
              value desc;
    • Q12
      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 ('FOB', 'AIR')
              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;
    • Q13
      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%deposits%'
                      group by
                              c_custkey
              ) c_orders
      group by
              c_count
      order by
              custdist desc,
              c_count desc;
    • Q14
      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 '1997-06-01'
              and l_shipdate < date '1997-06-01' + interval '1' month;
    • Q15
      with revenue0(SUPPLIER_NO, TOTAL_REVENUE)  as
        (
        select
          l_suppkey,
          sum(l_extendedprice * (1 - l_discount))
        from
          lineitem
        where
          l_shipdate >= date '1995-02-01'
          and l_shipdate < date '1995-02-01' + interval '3' month
        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;
    • Q16
      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#45'
              and p_type not like 'SMALL ANODIZED%'
              and p_size in (47, 15, 37, 30, 46, 16, 18, 6)
              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;
    • Q17
      select
              sum(l_extendedprice) / 7.0 as avg_yearly
      from
              lineitem,
              part
      where
              p_partkey = l_partkey
              and p_brand = 'Brand#51'
              and p_container = 'WRAP PACK'
              and l_quantity < (
                      select
                              0.2 * avg(l_quantity)
                      from
                              lineitem
                      where
                              l_partkey = p_partkey
              );
    • Q18
      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) > 312
              )
              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;
    • Q19
      select
              sum(l_extendedprice* (1 - l_discount)) as revenue
      from
              lineitem,
              part
      where
              (
                      p_partkey = l_partkey
                      and p_brand = 'Brand#52'
                      and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
                      and l_quantity >= 3 and l_quantity <= 3 + 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#43'
                      and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
                      and l_quantity >= 12 and l_quantity <= 12 + 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#52'
                      and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
                      and l_quantity >= 21 and l_quantity <= 21 + 10
                      and p_size between 1 and 15
                      and l_shipmode in ('AIR', 'AIR REG')
                      and l_shipinstruct = 'DELIVER IN PERSON'
              );
    • Q20
      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 'drab%'
                              )
                              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 '1996-01-01'
                                              and l_shipdate < date '1996-01-01' + interval '1' year
                              )
              )
              and s_nationkey = n_nationkey
              and n_name = 'KENYA'
      order by
              s_name;
    • Q21
      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 = 'PERU'
      group by
              s_name
      order by
              numwait desc,
              s_name
      limit 100;
    • Q22
      select
              cntrycode,
              count(*) as numcust,
              sum(c_acctbal) as totacctbal
      from
              (
                      select
                              substring(c_phone from 1 for 2) as cntrycode,
                              c_acctbal
                      from
                              customer
                      where
                              substring(c_phone from 1 for 2) in
                                      ('24', '32', '17', '18', '12', '14', '22')
                              and c_acctbal > (
                                      select
                                              avg(c_acctbal)
                                      from
                                              customer
                                      where
                                              c_acctbal > 0.00
                                              and substring(c_phone from 1 for 2) in
                                                      ('24', '32', '17', '18', '12', '14', '22')
                              )
                              and not exists (
                                      select
                                              *
                                      from
                                              orders
                                      where
                                              o_custkey = c_custkey
                              )
              ) as custsale
      group by
              cntrycode
      order by
              cntrycode;