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:
Configure an IP address whitelist for the cluster.
Create a database account for the cluster.
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
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.
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)
You can perform the following operations only for Enterprise Edition, Basic Edition, and Data Lakehouse Edition clusters.
Create an external database.
CREATE EXTERNAL DATABASE IF NOT EXISTS external_tpch;
Create eight external tables.
NoteAnalyticDB 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.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/';
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;
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;
NoteThe 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
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.NoteOne command can generate only one chunk.
For more information about how to use DBGEN, visit GitHub.
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';
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;
NoteThe 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.
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;