本文以一个简单的示例,为您介绍使用Hologres查询外部表和内部表中数据的基本步骤,帮助您直观体验Hologres查询数据性能。

前提条件

背景信息

Hologres在查询数据方面具有极速响应的优势。本示例说明如何创建Hologres的数据库、外部表、内部表以及如何导入数据至内部表中,使用Hologres分别查询内部表和外部表中的数据,帮助您快速体验Hologres查询数据的性能。

操作流程

  1. 步骤一:创建数据库
    通过Hologres快速创建数据库,用于后续存放实例数据进行查询使用。
  2. 步骤二:创建表
    在已创建的数据库中,创建表用于存储示例数据。表分为外部表和内部表:
    • 外部表指在Hologres中不存储数据,只进行字段映射的表。
    • 内部表指数据存储在Hologres中的表。
  3. 步骤三:导入示例数据
    本示例为您提供TPC-H标准数据集数据,您可以通过Hologres将数据导入表中,用于后续查询使用。
  4. 步骤四:查询表中数据
    本示例为您提供了22条标准的TPC-H测试查询SQL语句,帮助您快速体验在Hologres中查询数据的时效性和稳定性。

步骤一:创建数据库

  1. 登录Hologres管理控制台,单击左侧实例列表
  2. 实例列表页面,单击对应实例名称。
  3. 实例详情页左侧导航栏,单击Database管理
  4. DB授权页面,单击右上角新增数据库
    新增数据库
  5. 新增数据库对话框,配置如下参数。
    新增数据库
    参数 说明
    实例名 选择在哪个Hologres实例上创建数据库。默认展示当前已登录实例的名称,您也可以在下拉框中选择其他Hologres实例。
    数据库名称 本示例数据库名称为tpch_10g。
    说明 配置的数据库名称必须唯一。
    简单权限策略 您可以为创建的数据库选择一种权限策略。更多关于权限策略的说明,请参见:
    • SPM:简单权限模型,该权限模型授权是以DB为粒度,划分admin(管理员)、developer(开发者)、writer(读写者)以及viewer(分析师)四种角色,您可以通过少量的权限管理函数,即可对DB中的对象进行方便且安全的权限管理。
    • SLPM:基于Schema级别的简单权限模型,该权限模型以Schema为粒度,划分 <db>.admin(DB管理员)、<db>.<schema>.developer(开发者)、<db>.<schema>.writer(读写者)以及 <db>.<schema>.viewer(分析师),相比于简单权限模型更为细粒度。
    • 专家权限模型:Hologres兼容PostgreSQL,使用与Postgres完全一致的权限系统。

步骤二:创建表

数据库创建成功后,您需在数据库中创建对应的表。创建表时,根据您数据存放的位置不同,分为创建外部表和内部表。

  • 创建外部表
    1. 登录数据库
      1. HoloWeb控制台DB授权页面,单击元数据管理
      2. 元数据管理页面,双击左侧目录树中已创建成功的数据库名称,单击确认登录数据库
    2. 新建外部表
      1. SQL编辑器页面,单击左上角的新建SQL窗口
      2. 在新增的临时Query查询页面,选择已创建的实例名数据库后,请您在SQL查询的编辑框输入如下语句,单击运行

        以下SQL语句用来创建一个映射到MaxCompute公共空间MAXCOMPUTE_PUBLIC_DATA中odps_customer_10g、odps_lineitem_10g等表的外部表,用于后续查询。

        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');
  • 创建内部表
    1. 登录数据库
      1. HoloWeb控制台DB授权页面,单击元数据管理
      2. 元数据管理页面,双击左侧目录树中已创建成功的数据库名称,单击确认登录数据库
    2. 新建内部表
      1. SQL编辑器页面,单击左上角的新建SQL窗口
      2. 在新增的临时Query查询页面,选择已创建的实例名数据库后,请您在SQL查询的编辑框输入如下语句,单击运行

        以下SQL语句用来创建名称分别为LINEITEM、ORDERS、PARTSUPP、PART、CUSTOMER、SUPPLIER、NATION和REGION的表,用于后续存储数据。

        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;

步骤三:导入示例数据

内部表创建成功后,可以通过以下步骤将数据导入Hologres内部表中。

说明 外部表在Hologres中不存储数据,只进行字段映射。通过外部表您可以使用Hologres直接调用存储于MaxCompute公共空间MAXCOMPUTE_PUBLIC_DATA的数据。
  1. SQL编辑器页面,单击左上角的新建SQL窗口
  2. 在新增的临时Query查询页面,选择已创建的实例名数据库后,请您在SQL查询的编辑框输入如下语句,单击运行
    以下SQL语句将MaxCompute公共空间MAXCOMPUTE_PUBLIC_DATA中public.odps_customer_10g、public.odps_lineitem_10g等表中数据导入到对应名称的内部表中,用于后续查询。
    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);

步骤四:查询表中数据

  1. SQL编辑器页面,单击左上角的新建SQL窗口SQL编辑器页面,单击左上角的新建SQL窗口
  2. 在新增的临时Query查询页面,选择已创建的实例名数据库后,请您在SQL查询的编辑框输入如下语句,单击运行
    说明 下述SQL代码均为查询内部表数据使用,如需查询外部表,请将对应代码查询的表名更换为外部表名。
    基于TPC-H演化的22条查询语句如下所示,您可以单击表格中的链接进行查看。
    名称 查询语句
    TPC-H 22条查询语句 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;