All Products
Search
Document Center

AnalyticDB:TPC-H tests

Last Updated:Jun 30, 2025

The TPC-H benchmark can be used to evaluate the business analysis capabilities of databases, focusing on complex queries and high-concurrency decision support performance. This topic describes how to perform TPC-H tests in AnalyticDB for MySQL.

Preparations

Before you execute and test TPC-H queries in AnalyticDB for MySQL, you must complete the following preparations:

  1. Create an AnalyticDB for MySQL cluster.

  2. Configure an IP address whitelist for the cluster.

  3. Create a database account for the cluster.

  4. To connect to the cluster over the Internet, apply for a public endpoint.

Create test tables

You must create eight tables and one view in AnalyticDB for MySQL for performance tests.

(Optional) Enable the Native compute engine and the XUANWU_V2 engine

Important

The Native compute engine is in invitational preview. Only AnalyticDB for MySQL clusters of V3.2.2.1 or later allow you to execute the SET statement to enable the Native compute engine and the XUANWU_V2 engine. For AnalyticDB for MySQL clusters earlier than V3.2.2.1, submit a ticket to request a minor version update, and then execute the SET statement to enable the Native compute engine and the XUANWU_V2 engine.

When the minor version of an AnalyticDB for MySQL cluster is being updated, the cluster is restarted and may encounter transient connections. We recommend that you perform the update during off-peak hours. Make sure that your application is configured to automatically reconnect to the AnalyticDB for MySQL cluster.

If you want to test the performance of the native compute engine and the XUANWU engine of AnalyticDB for MySQL, skip this step and directly create test tables. If you want to test the performance of the Native compute engine and the XUANWU_V2 engine of AnalyticDB for MySQL, execute the following statements to enable the Native compute engine and the XUANWU_V2 engine before you create test tables:

SET ADB_CONFIG native_engine_opt_enabled = true;
SET ADB_CONFIG rc_ddl_engine_rewrite_xuanwuv2 = true;

Create test tables

  • nation table

    CREATE TABLE nation (
      n_nationkey INT NOT NULL COMMENT '',
      n_name VARCHAR NOT NULL COMMENT '',
      n_regionkey INT NOT NULL COMMENT '',
      n_comment VARCHAR COMMENT '',
      dummy VARCHAR,
      PRIMARY KEY (n_nationkey)
    ) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';
  • region table

    CREATE TABLE region (
      r_regionkey INT NOT NULL COMMENT '',
      r_name VARCHAR NOT NULL COMMENT '',
      r_comment VARCHAR COMMENT '',
      dummy VARCHAR,
      PRIMARY KEY (r_regionkey)
    ) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';
  • part table

    CREATE TABLE part (
      p_partkey INT NOT NULL COMMENT '',
      p_name VARCHAR NOT NULL COMMENT '',
      p_mfgr VARCHAR NOT NULL COMMENT '',
      p_brand VARCHAR(10) NOT NULL COMMENT '',
      p_type VARCHAR NOT NULL COMMENT '',
      p_size INT NOT NULL COMMENT '',
      p_container VARCHAR(10) NOT NULL COMMENT '',
      p_retailprice DOUBLE NOT NULL COMMENT '',
      p_comment VARCHAR NOT NULL COMMENT '',
      dummy VARCHAR,
      PRIMARY KEY (p_partkey)
    ) DISTRIBUTED BY HASH (p_partkey) INDEX_ALL = 'Y' compression='lz4hc';
  • supplier table

    CREATE TABLE supplier (
      s_suppkey INT NOT NULL COMMENT '',
      s_name VARCHAR NOT NULL COMMENT '',
      s_address VARCHAR NOT NULL COMMENT '',
      s_nationkey INT NOT NULL COMMENT '',
      s_phone VARCHAR(15) NOT NULL COMMENT '',
      s_acctbal DOUBLE NOT NULL COMMENT '',
      s_comment VARCHAR NOT NULL COMMENT '',
      dummy VARCHAR,
      PRIMARY KEY (s_suppkey)
    ) DISTRIBUTED BY HASH (s_suppkey) INDEX_ALL = 'Y' compression='lz4hc';
  • partsupp table

    CREATE TABLE partsupp (
      ps_partkey INT NOT NULL COMMENT '',
      ps_suppkey INT NOT NULL COMMENT '',
      ps_availqty INT NOT NULL COMMENT '',
      ps_supplycost DOUBLE NOT NULL COMMENT '',
      ps_comment VARCHAR NOT NULL COMMENT '',
      dummy VARCHAR,
      PRIMARY KEY (ps_partkey, ps_suppkey)
    ) DISTRIBUTED BY HASH (ps_partkey) INDEX_ALL = 'Y' compression='lz4hc';
  • customer table

    CREATE TABLE customer (
      c_custkey INT NOT NULL COMMENT '',
      c_name VARCHAR NOT NULL COMMENT '',
      c_address VARCHAR NOT NULL COMMENT '',
      c_nationkey INT NOT NULL COMMENT '',
      c_phone VARCHAR(15) NOT NULL COMMENT '',
      c_acctbal DOUBLE NOT NULL COMMENT '',
      c_mktsegment VARCHAR(10) NOT NULL COMMENT '',
      c_comment VARCHAR NOT NULL COMMENT '',
      dummy VARCHAR,
      PRIMARY KEY (c_custkey)
    ) DISTRIBUTED BY HASH (c_custkey) INDEX_ALL = 'Y' compression='lz4hc';
  • orders table

    CREATE TABLE orders (
      o_orderkey BIGINT NOT NULL COMMENT '',
      o_custkey INT NOT NULL COMMENT '',
      o_orderstatus VARCHAR(1) NOT NULL COMMENT '',
      o_totalprice DOUBLE NOT NULL COMMENT '',
      o_orderdate DATE NOT NULL COMMENT '',
      o_orderpriority VARCHAR(15) NOT NULL COMMENT '',
      o_clerk VARCHAR(15) NOT NULL COMMENT '',
      o_shippriority INT NOT NULL COMMENT '',
      o_comment VARCHAR NOT NULL COMMENT '',
      dummy VARCHAR,
      KEY idx_o_custkey (o_custkey),
      KEY idx_o_orderdate (o_orderdate),
      KEY idx_o_orderkey (o_orderkey),
      KEY idx_o_orderstatus (o_orderstatus)
    ) DISTRIBUTED BY HASH (o_orderkey) PARTITION BY VALUE(date_format(o_orderdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL = 'N' compression='lz4hc';
  • lineitem table

    CREATE TABLE lineitem (
      l_orderkey BIGINT NOT NULL COMMENT '',
      l_partkey INT NOT NULL COMMENT '',
      l_suppkey INT NOT NULL COMMENT '',
      l_linenumber INT NOT NULL COMMENT '',
      l_quantity DOUBLE NOT NULL COMMENT '',
      l_extendedprice DOUBLE NOT NULL COMMENT '',
      l_discount DOUBLE NOT NULL COMMENT '',
      l_tax DOUBLE NOT NULL COMMENT '',
      l_returnflag VARCHAR(1) NOT NULL COMMENT '',
      l_linestatus VARCHAR(1) NOT NULL COMMENT '',
      l_shipdate DATE NOT NULL COMMENT '',
      l_commitdate DATE NOT NULL COMMENT '',
      l_receiptdate DATE NOT NULL COMMENT '',
      l_shipinstruct VARCHAR(25) NOT NULL COMMENT '',
      l_shipmode VARCHAR(10) NOT NULL COMMENT '',
      l_comment VARCHAR NOT NULL COMMENT '',
      dummy VARCHAR,
      KEY idx_l_orderkey (l_orderkey),
      KEY idx_l_partkey (l_partkey),
      KEY idx_l_receiptdate (l_receiptdate),
      KEY idx_l_returnflag (l_returnflag),
      KEY idx_l_shipdate (l_shipdate),
      KEY idx_l_shipinstruct (l_shipinstruct),
      KEY idx_l_shipmode (l_shipmode),
      KEY idx_l_suppkey (l_suppkey)
    ) DISTRIBUTED BY HASH (l_orderkey) PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL = 'N' compression='lz4hc';
  • revenue0 view

    CREATE VIEW `revenue0` AS
    SELECT
      `l_suppkey` supplier_no,
      `sum`(
        (
          `l_extendedprice` * (1 - `l_discount`)
        )
      ) total_revenue
    FROM
      lineitem
    WHERE
      (
        (`l_shipdate` >= DATE '1996-01-01')
        AND (
          `l_shipdate` < (
            DATE '1996-01-01' + INTERVAL '3' MONTH
          )
        )
      )
    GROUP BY
      `l_suppkey`;

Import data

Build a TPC-H test dataset and import 1,000 GB of TPC-H test data to AnalyticDB for MySQL.

Note

The TPC-H performance tests described in this topic are implemented based on the TPC-H benchmark test but do not meet all requirements of the TPC-H benchmark test. Therefore, the test results described in this topic cannot be compared with the published results of the TPC-H benchmark test.

The following table lists the number of rows in different tables of the TPC-H test dataset.

Table name

Number of rows

customer

0.15 billion

lineitem

6 billion

nation

25

orders

1.5 billion

part

0.2 billion

partsupp

0.8 billion

region

5

supplier

10 million

Use OSS external tables to import data (recommended)

Important

You can perform the following operations only for Enterprise Edition, Basic Edition, and Data Lakehouse Edition clusters.

  1. Create an external database.

    CREATE EXTERNAL DATABASE IF NOT EXISTS external_tpch;
  2. Create eight external tables.

    Note

    AnalyticDB for MySQL provides Object Storage Service (OSS) paths that can be used to store TPC-H test data. Replace the OSS path specified by the LOCATION parameter based on the region in which the AnalyticDB for MySQL cluster resides.

    OSS paths for different regions

    Region

    Path

    China (Hangzhou)

    oss://dataset-cn-hangzhou-external/TPC-H/1TB

    China (Zhangjiakou)

    oss://dataset-cn-zhangjiakou-external/TPC-H/1TB

    China (Beijing)

    oss://dataset-cn-beijing-external/TPC-H/1TB

    China (Shanghai)

    oss://dataset-cn-shanghai-external/TPC-H/1TB

    China (Shenzhen)

    oss://dataset-cn-shenzhen-external/TPC-H/1TB

    China (Qingdao)

    oss://dataset-cn-qingdao-external/TPC-H/1TB

    China (Guangzhou)

    oss://dataset-cn-guangzhou-external/TPC-H/1TB

    China (Hong Kong)

    oss://dataset-cn-hongkong-external/TPC-H/1TB

    Singapore

    oss://dataset-ap-southeast-1-external/TPC-H/1TB

    Malaysia (Kuala Lumpur)

    oss://dataset-ap-southeast-3-external/TPC-H/1TB

    Japan (Tokyo)

    oss://dataset-ap-northeast-1-external/TPC-H/1TB

    Indonesia (Jakarta)

    oss://dataset-ap-southeast-5-external/TPC-H/1TB

    Germany (Frankfurt)

    oss://dataset-eu-central-1-external/TPC-H/1TB

    US (Silicon Valley)

    oss://dataset-us-west-1-external/TPC-H/1TB

    UK (London)

    oss://dataset-eu-west-1-external/TPC-H/1TB

    US (Virginia)

    oss://dataset-us-east-1-external/TPC-H/1TB

    CREATE EXTERNAl TABLE external_tpch.nation (
    	n_nationkey int NOT NULL COMMENT '',
    	n_name varchar NOT NULL COMMENT '',
    	n_regionkey int NOT NULL COMMENT '',
    	n_comment varchar COMMENT '',
    	`dummy` varchar
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
    STORED AS TEXTFILE
    LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/nation/';
    
    CREATE EXTERNAl TABLE external_tpch.customer (
    	c_custkey int NOT NULL COMMENT '',
    	c_name varchar NOT NULL COMMENT '',
    	c_address varchar NOT NULL COMMENT '',
    	c_nationkey int NOT NULL COMMENT '',
    	c_phone varchar(15) NOT NULL COMMENT '',
    	c_acctbal double NOT NULL COMMENT '',
    	c_mktsegment varchar(10) NOT NULL COMMENT '',
    	c_comment varchar NOT NULL COMMENT '',
    	`dummy` varchar
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
    STORED AS TEXTFILE
    LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/customer/';
    
    CREATE EXTERNAl TABLE external_tpch.lineitem (
    	l_orderkey bigint NOT NULL COMMENT '',
    	l_partkey int NOT NULL COMMENT '',
    	l_suppkey int NOT NULL COMMENT '',
    	l_linenumber int NOT NULL COMMENT '',
    	l_quantity double NOT NULL COMMENT '',
    	l_extendedprice double NOT NULL COMMENT '',
    	l_discount double NOT NULL COMMENT '',
    	l_tax double NOT NULL COMMENT '',
    	l_returnflag varchar(1) NOT NULL COMMENT '',
    	l_linestatus varchar(1) NOT NULL COMMENT '',
    	l_shipdate date NOT NULL COMMENT '',
    	l_commitdate date NOT NULL COMMENT '',
    	l_receiptdate date NOT NULL COMMENT '',
    	l_shipinstruct varchar(25) NOT NULL COMMENT '',
    	l_shipmode varchar(10) NOT NULL COMMENT '',
    	l_comment varchar NOT NULL COMMENT '',
    	dummy varchar
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
    STORED AS TEXTFILE
    LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/lineitem/';
    
    CREATE EXTERNAl TABLE external_tpch.orders (
    	o_orderkey bigint NOT NULL COMMENT '',
    	o_custkey int NOT NULL COMMENT '',
    	o_orderstatus varchar(1) NOT NULL COMMENT '',
    	o_totalprice double NOT NULL COMMENT '',
    	o_orderdate date NOT NULL COMMENT '',
    	o_orderpriority varchar(15) NOT NULL COMMENT '',
    	o_clerk varchar(15) NOT NULL COMMENT '',
    	o_shippriority int NOT NULL COMMENT '',
    	o_comment varchar NOT NULL COMMENT '',
    	dummy varchar
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
    STORED AS TEXTFILE
    LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/orders/';
    
    CREATE EXTERNAl TABLE external_tpch.part (
    	p_partkey int NOT NULL COMMENT '',
    	p_name varchar NOT NULL COMMENT '',
    	p_mfgr varchar NOT NULL COMMENT '',
    	p_brand varchar(10) NOT NULL COMMENT '',
    	p_type varchar NOT NULL COMMENT '',
    	p_size int NOT NULL COMMENT '',
    	p_container varchar(10) NOT NULL COMMENT '',
    	p_retailprice double NOT NULL COMMENT '',
    	p_comment varchar NOT NULL COMMENT '',
    	dummy varchar
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
    STORED AS TEXTFILE
    LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/part/';
    
    CREATE EXTERNAl TABLE external_tpch.partsupp (
    	ps_partkey int NOT NULL COMMENT '',
    	ps_suppkey int NOT NULL COMMENT '',
    	ps_availqty int NOT NULL COMMENT '',
    	ps_supplycost double NOT NULL COMMENT '',
    	ps_comment varchar NOT NULL COMMENT '',
    	dummy varchar
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
    STORED AS TEXTFILE
    LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/partsupp/';
    
    
    CREATE EXTERNAl TABLE external_tpch.region (
    	r_regionkey int NOT NULL COMMENT '',
    	r_name varchar NOT NULL COMMENT '',
    	r_comment varchar COMMENT '',
    	dummy varchar
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
    STORED AS TEXTFILE
    LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/region/';
    
    CREATE EXTERNAl TABLE external_tpch.supplier (
    	s_suppkey int NOT NULL COMMENT '',
    	s_name varchar NOT NULL COMMENT '',
    	s_address varchar NOT NULL COMMENT '',
    	s_nationkey int NOT NULL COMMENT '',
    	s_phone varchar(15) NOT NULL COMMENT '',
    	s_acctbal double NOT NULL COMMENT '',
    	s_comment varchar NOT NULL COMMENT '',
    	dummy varchar
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
    STORED AS TEXTFILE
    LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/supplier/';
  3. Import data from the external tables to the corresponding AnalyticDB for MySQL internal tables that are described in the "Create test tables" section of this topic.

    INSERT OVERWRITE INTO customer
    SELECT *
    FROM external_tpch.customer;
    
    INSERT OVERWRITE INTO lineitem
    SELECT *
    FROM external_tpch.lineitem;
    
    INSERT  INTO nation
    SELECT *
    FROM external_tpch.nation;
    
    INSERT OVERWRITE INTO orders
    SELECT *
    FROM external_tpch.orders;
    
    INSERT OVERWRITE INTO part
    SELECT *
    FROM external_tpch.part;
    
    INSERT OVERWRITE INTO partsupp
    SELECT *
    FROM external_tpch.partsupp;
    
    INSERT  INTO region
    SELECT *
    FROM external_tpch.region;
    
    INSERT OVERWRITE INTO supplier
    SELECT *
    FROM external_tpch.supplier;
  4. Collect statistics.

    ANALYZE TABLE customer UPDATE HISTOGRAM;
    ANALYZE TABLE lineitem UPDATE HISTOGRAM;
    ANALYZE TABLE nation UPDATE HISTOGRAM;
    ANALYZE TABLE orders UPDATE HISTOGRAM;
    ANALYZE TABLE part UPDATE HISTOGRAM;
    ANALYZE TABLE partsupp UPDATE HISTOGRAM;
    ANALYZE TABLE region UPDATE HISTOGRAM;
    ANALYZE TABLE supplier UPDATE HISTOGRAM;
    Note

    The query optimizer converts queries into execution plans that are executed by the execution engine. The quality of an execution plan affects its query performance. Statistics can be used to help the query optimizer generate high-quality execution plans. After you import data, you must collect histogram statistics on all tables to obtain the optimal performance. For more information about statistics, see Statistics.

Execute the LOAD DATA statement to import data

  1. Download the TPC-H data generation tool DBGEN from the TPC official website and compile the downloaded file to generate a binary executable file named dbgen.

    ./dbgen -s $scale -C $chunks -S $i -f
    • -s: the scale factor (SF), which specifies the database size. A value of 100 specifies 100 GB. A value of 1000 specifies 1 TB.

    • -C: the total number of chunks.

    • -S: the serial number of the chunk that the current command generates.

      Note

      One command can generate only one chunk.

    For more information about how to use DBGEN, visit GitHub.

  2. Execute the LOAD DATA statement to import the files that are generated by DBGEN to AnalyticDB for MySQL.

    In this example, a Windows MySQL client is used to connect to AnalyticDB for MySQL for data import, and line feeds (\r\n) are used.

    LOAD DATA LOCAL INFILE 'customer.tbl' INTO TABLE CUSTOMER 
    FIELDS TERMINATED BY '|'  LINES TERMINATED BY '\r\n';
    LOAD DATA LOCAL INFILE 'orders.tbl'   INTO TABLE ORDERS   
    FIELDS TERMINATED BY '|'  LINES TERMINATED BY '\r\n';
    LOAD DATA LOCAL INFILE 'lineitem.tbl' INTO TABLE LINEITEM 
    FIELDS TERMINATED BY '|'  LINES TERMINATED BY '\r\n';
    LOAD DATA LOCAL INFILE 'nation.tbl'   INTO TABLE NATION   
    FIELDS TERMINATED BY '|'  LINES TERMINATED BY '\r\n';
    LOAD DATA LOCAL INFILE 'partsupp.tbl' INTO TABLE PARTSUPP 
    FIELDS TERMINATED BY '|'  LINES TERMINATED BY '\r\n';
    LOAD DATA LOCAL INFILE 'part.tbl'     INTO TABLE PART     
    FIELDS TERMINATED BY '|'   LINES TERMINATED BY '\r\n';
    LOAD DATA LOCAL INFILE 'region.tbl'   INTO TABLE REGION   
    FIELDS TERMINATED BY '|'  LINES TERMINATED BY '\r\n';
    LOAD DATA LOCAL INFILE 'supplier.tbl' INTO TABLE SUPPLIER 
    FIELDS TERMINATED BY '|'  LINES TERMINATED BY '\r\n';
  3. Collect statistics.

    ANALYZE TABLE customer UPDATE HISTOGRAM;
    ANALYZE TABLE lineitem UPDATE HISTOGRAM;
    ANALYZE TABLE nation UPDATE HISTOGRAM;
    ANALYZE TABLE orders UPDATE HISTOGRAM;
    ANALYZE TABLE part UPDATE HISTOGRAM;
    ANALYZE TABLE partsupp UPDATE HISTOGRAM;
    ANALYZE TABLE region UPDATE HISTOGRAM;
    ANALYZE TABLE supplier UPDATE HISTOGRAM;
    Note

    The query optimizer converts queries into execution plans that are executed by the execution engine. The quality of an execution plan affects its query performance. Statistics about data columns can be used to help the query optimizer generate high-quality execution plans. After you import data, you must collect histogram statistics on all tables to obtain the optimal performance. For more information about statistics, see Statistics.

Appendix: TPC-H test queries

A performance test contains the following 22 SQL queries.

Note

The implementation of TPC-H in this topic is derived from the TPC-H benchmark but does not comply with all the requirements of the TPC-H benchmark.

  • SQL1

    SELECT l_returnflag, l_linestatus, sum(l_quantity) AS sum_qty
    	, sum(l_extendedprice) AS sum_base_price
    	, sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price
    	, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge
    	, avg(l_quantity) AS avg_qty, avg(l_extendedprice) AS avg_price
    	, avg(l_discount) AS avg_disc, count(*) AS count_order
    FROM lineitem
    WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY
    GROUP BY l_returnflag, l_linestatus
    ORDER BY l_returnflag, l_linestatus;
  • SQL2

    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 = 15
    	AND p_type LIKE '%BRASS'
    	AND s_nationkey = n_nationkey
    	AND n_regionkey = r_regionkey
    	AND r_name = 'EUROPE'
    	AND ps_supplycost = (
    		SELECT min(ps_supplycost)
    		FROM partsupp, supplier, nation, region
    		WHERE p_partkey = ps_partkey
    			AND s_suppkey = ps_suppkey
    			AND s_nationkey = n_nationkey
    			AND n_regionkey = r_regionkey
    			AND r_name = 'EUROPE'
    	)
    ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
    LIMIT 100;
  • SQL3

    SELECT l_orderkey, sum(l_extendedprice * (1 - l_discount)) AS revenue
    	, o_orderdate, o_shippriority
    FROM customer, orders, lineitem
    WHERE c_mktsegment = 'BUILDING'
    	AND c_custkey = o_custkey
    	AND l_orderkey = o_orderkey
    	AND o_orderdate < DATE '1995-03-15'
    	AND l_shipdate > DATE '1995-03-15'
    GROUP BY l_orderkey, o_orderdate, o_shippriority
    ORDER BY revenue DESC, o_orderdate
    LIMIT 10;
  • SQL4

    SELECT o_orderpriority, count(*) AS order_count
    FROM orders
    WHERE o_orderdate >= DATE '1993-07-01'
    	AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    	AND EXISTS (
    		SELECT *
    		FROM lineitem
    		WHERE l_orderkey = o_orderkey
    			AND l_commitdate < l_receiptdate
    	)
    GROUP BY o_orderpriority
    ORDER BY o_orderpriority;
  • SQL5

    SELECT n_name, sum(l_extendedprice * (1 - l_discount)) AS revenue
    FROM customer, orders, lineitem, supplier, nation, region
    WHERE c_custkey = o_custkey
    	AND l_orderkey = o_orderkey
    	AND l_suppkey = s_suppkey
    	AND c_nationkey = s_nationkey
    	AND s_nationkey = n_nationkey
    	AND n_regionkey = r_regionkey
    	AND r_name = 'ASIA'
    	AND o_orderdate >= DATE '1994-01-01'
    	AND o_orderdate < DATE '1994-01-01' + INTERVAL '1' YEAR
    GROUP BY n_name
    ORDER BY revenue DESC;
  • SQL6

    SELECT sum(l_extendedprice * l_discount) AS revenue
    FROM lineitem
    WHERE l_shipdate >= DATE '1994-01-01'
    	AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR
    	AND l_discount BETWEEN 0.06 - 0.01 AND 0.06 + 0.01
    	AND l_quantity < 24;
  • SQL7

    SELECT supp_nation, cust_nation, l_year, sum(volume) AS revenue
    FROM (
    	SELECT n1.n_name AS supp_nation, n2.n_name AS cust_nation, EXTRACT(YEAR FROM l_shipdate) AS l_year
    		, l_extendedprice * (1 - l_discount) AS volume
    	FROM supplier, lineitem, orders, customer, nation n1, nation n2
    	WHERE s_suppkey = l_suppkey
    		AND o_orderkey = l_orderkey
    		AND c_custkey = o_custkey
    		AND s_nationkey = n1.n_nationkey
    		AND c_nationkey = n2.n_nationkey
    		AND ((n1.n_name = 'FRANCE'
    				AND n2.n_name = 'GERMANY')
    			OR (n1.n_name = 'GERMANY'
    				AND n2.n_name = 'FRANCE'))
    		AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
    ) shipping
    GROUP BY supp_nation, cust_nation, l_year
    ORDER BY supp_nation, cust_nation, l_year;
  • SQL8

    SELECT o_year
    	, sum(CASE
    		WHEN nation = 'PERU' 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 = 'PROMO BURNISHED TIN'
    ) all_nations
    GROUP BY o_year
    ORDER BY o_year;
  • SQL9

    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 '%green%'
    ) profit
    GROUP BY nation, o_year
    ORDER BY nation, o_year DESC;
  • SQL10

    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-09-01'
    	AND o_orderdate < DATE '1993-09-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
  • SQL11

    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 = 'GERMANY'
    GROUP BY ps_partkey
    HAVING sum(ps_supplycost * ps_availqty) > (
    	SELECT sum(ps_supplycost * ps_availqty) * 0.0000001000
    	FROM partsupp, supplier, nation
    	WHERE ps_suppkey = s_suppkey
    		AND s_nationkey = n_nationkey
    		AND n_name = 'GERMANY'
    )
    ORDER BY value DESC;
  • SQL12

    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 ('TRUCK', 'AIR')
    	AND l_commitdate < l_receiptdate
    	AND l_shipdate < l_commitdate
    	AND l_receiptdate >= DATE '1994-01-01'
    	AND l_receiptdate < DATE '1994-01-01' + INTERVAL '1' YEAR
    GROUP BY l_shipmode
    ORDER BY l_shipmode;
  • SQL13

    SELECT c_count, count(*) AS custdist
    FROM (
    	SELECT c_custkey, count(o_orderkey) AS c_count
    	FROM customer
    		LEFT 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;
  • SQL14

    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 '1995-09-01'
    	AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' MONTH;
  • SQL15

    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;
  • SQL16

    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 'MEDIUM POLISHED%'
    	AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
    	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;
  • SQL17

    SELECT sum(l_extendedprice) / 7.0 AS avg_yearly
    FROM lineitem, part
    WHERE p_partkey = l_partkey
    	AND p_brand = 'Brand#23'
    	AND p_container = 'SM CAN'
    	AND l_quantity < (
    		SELECT 0.2 * avg(l_quantity)
    		FROM lineitem
    		WHERE l_partkey = p_partkey
    	);
  • SQL18

    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) > 300
    	)
    	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;
  • SQL19

    SELECT sum(l_extendedprice * (1 - l_discount)) AS revenue
    FROM lineitem, part
    WHERE (p_partkey = l_partkey
    		AND p_brand = 'Brand#12'
    		AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    		AND l_quantity >= 1
    		AND l_quantity <= 1 + 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#42'
    		AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    		AND l_quantity >= 10
    		AND l_quantity <= 10 + 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#45'
    		AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
    		AND l_quantity >= 20
    		AND l_quantity <= 20 + 10
    		AND p_size BETWEEN 1 AND 15
    		AND l_shipmode IN ('AIR', 'AIR REG')
    		AND l_shipinstruct = 'DELIVER IN PERSON');
  • SQL20

    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 'forest%'
    			)
    			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 '1994-01-01'
    					AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR
    			)
    	)
    	AND s_nationkey = n_nationkey
    	AND n_name = 'CANADA'
    ORDER BY s_name;
  • SQL21

    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 = 'SAUDI ARABIA'
    GROUP BY s_name
    ORDER BY numwait DESC, s_name
    LIMIT 100;
  • SQL22

    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 (
    			'13',
    			'31',
    			'23',
    			'29',
    			'30',
    			'18',
    			'17'
    		)
    		AND c_acctbal > (
    			SELECT avg(c_acctbal)
    			FROM customer
    			WHERE c_acctbal > 0.00
    				AND substring(c_phone FROM 1 FOR 2) IN (
    					'13',
    					'31',
    					'23',
    					'29',
    					'30',
    					'18',
    					'17'
    				)
    		)
    		AND NOT EXISTS (
    			SELECT *
    			FROM orders
    			WHERE o_custkey = c_custkey
    		)
    ) custsale
    GROUP BY cntrycode
    ORDER BY cntrycode;