mariadb-columnstore-engine/dbcon/doc/100GB_stream0/stream0.sql

 ------------------------------------------------------------------------------------------------//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 '1994-01-01' AND
	L_SHIPDATE < date '1994-01-01' + interval '1' month;

--------------------------------------------------------------------------------------------------//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 = 40 AND
	P_TYPE LIKE '%%NICKEL' AND
	S_NATIONKEY = N_NATIONKEY AND
	N_REGIONKEY = R_REGIONKEY AND
	R_NAME = 'AFRICA' 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 = 'AFRICA'
				)
ORDER BY 
	S_ACCTBAL DESC,
	N_NAME,
	S_NAME,
	P_PARTKEY;

-----------------------------------------------------------------------------------------//Q9
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 '%%orchid%%'
	) PROFIT
GROUP BY 
	NATION,
	O_YEAR
ORDER BY 
	NATION,
	O_YEAR DESC;

------------------------------------------------------------------------------------------------//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 'orchid%%'
			) 
	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 '1995-01-01' AND
			L_SHIPDATE < date '1995-01-01' + interval '1' year
		)
	) 
	AND S_NATIONKEY = N_NATIONKEY AND
	N_NAME = 'KENYA'
ORDER BY 
	S_NAME;

-------------------------------------------------------------------------------------------//Q6
SELECT 
	SUM(L_EXTENDEDPRICE*L_DISCOUNT) AS REVENUE
FROM 
	LINEITEM
WHERE 
	L_SHIPDATE >= date '1997-01-01' AND
	L_SHIPDATE < date '1997-01-01' + interval '1' year AND
	L_DISCOUNT BETWEEN 0.03 - 0.01 AND 0.03 + 0.01 AND
	L_QUANTITY < 25;

------------------------------------------------------------------------------------------------//Q17
SELECT 
	SUM(L_EXTENDEDPRICE)/7.0 AS AVG_YEARLY
FROM 
	LINEITEM,
	PART
WHERE 
	P_PARTKEY = L_PARTKEY AND
	P_BRAND = 'Brand#22' AND
	P_CONTAINER = 'JUMBO 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) > 318
	) 
	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;

-------------------------------------------------------------------------------------------//Q8
SELECT 
	O_YEAR,
	SUM (CASE 
			WHEN NATION = 'ROMANIA'
			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 = 'EUROPE' AND
			S_NATIONKEY =	N2.N_NATIONKEY AND
			O_ORDERDATE BETWEEN '1995-	01-01' AND '1996-12-31' AND
			P_TYPE = 'LARGE BRUSHED BRASS'
	) ALL_NATIONS
GROUP BY 
	O_YEAR
ORDER BY 
	O_YEAR;


------------------------------------------------------------------------------------------------//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 = 'IRAQ'
GROUP BY 
	S_NAME
ORDER BY 
	NUMWAIT DESC,
	S_NAME;

------------------------------------------------------------------------------------------------//Q13
SELECT 
	C_COUNT,
	COUNT(*) AS CUSTDIST
FROM 
	( 
		SELECT C_CUSTKEY,
		COUNT(O_ORDERKEY) C_COUNT
		FROM 
			CUSTOMER left outer join ORDERS on
				C_CUSTKEY = O_CUSTKEY AND
				O_COMMENT not like '%%express%%requests%%'
		GROUP BY 
			C_CUSTKEY
	) C_ORDERS
GROUP BY 
	C_COUNT
ORDER BY 
	CUSTDIST DESC,
	C_COUNT DESC;

----------------------------------------------------------------------------------------------//Q3
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-03' AND
	L_SHIPDATE > date '1995-03-03'
GROUP BY 
	L_ORDERKEY,
	O_ORDERDATE,
	O_SHIPPRIORITY
ORDER BY 
	REVENUE DESC,
	O_ORDERDATE;

------------------------------------------------------------------------------------------------//Q22
SELECT 
	CNTRYCODE,
	COUNT(*) NUMCUST,
	SUM(C_ACCTBAL) TOTACCTBAL
FROM ( 
	SELECT 
		SUBSTR(C_PHONE,1,2) CNTRYCODE,
		C_ACCTBAL
	FROM 
		CUSTOMER
	WHERE 
		SUBSTR(C_PHONE,1,2) IN 
			('24', '28', '30', '18', '21', '23', '14') 
		AND C_ACCTBAL > ( 
			SELECT 
				AVG(C_ACCTBAL)
			FROM 
				CUSTOMER
			WHERE 
				C_ACCTBAL > 0.00 
				AND SUBSTR(C_PHONE,1,2) IN
					('24', '28', '30', '18', '21', '23', '14')
		) 
		AND NOT EXISTS ( 
			SELECT *
			FROM
				ORDERS
			WHERE
				O_CUSTKEY = C_CUSTKEY
		)
) CUSTSALE
GROUP BY 
	CNTRYCODE
ORDER BY 
	CNTRYCODE;

------------------------------------------------------------------------------------------------//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#12' AND
	P_TYPE NOT LIKE 'SMALL BRUSHED%%' AND
	P_SIZE IN (36, 3, 24, 40, 37, 41, 15, 13) 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;

-------------------------------------------------------------------------------------------//Q4
SELECT 
	O_ORDERPRIORITY,
	COUNT(*) AS ORDER_COUNT
FROM 
	ORDERS
WHERE 
	O_ORDERDATE >= date '1993-05-01' AND
	O_ORDERDATE < date '1993-05-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;

--------------------------------------------------------------------------------------------------//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 = 'RUSSIA'
GROUP BY 
	PS_PARTKEY HAVING 
		SUM(PS_SUPPLYCOST*PS_AVAILQTY) > ( 
			SELECT
				SUM(PS_SUPPLYCOST * PS_AVAILQTY) * 0.0000010000
			FROM 
				PARTSUPP,
				SUPPLIER,
				NATION
			WHERE 
				PS_SUPPKEY = S_SUPPKEY AND
			S_NATIONKEY =	N_NATIONKEY AND
			N_NAME = 'RUSSIA'
		)
ORDER BY 
	VALUE DESC;

------------------------------------------------------------------------------------------------//Q15
CREATE VIEW REVENUE0 (SUPPLIER_NO, TOTAL_REVENUE) AS
	SELECT 
		L_SUPPKEY,
		SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))
FROM 
	LINEITEM
WHERE 
	L_SHIPDATE >= date '1993-01-01' AND
	L_SHIPDATE < date '1993-01-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;

DROP VIEW REVENUE0;

---------------------------------------------------------------------------------------------------//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 '73' day
GROUP BY 
	L_RETURNFLAG,
	L_LINESTATUS
ORDER BY 
	L_RETURNFLAG,
	L_LINESTATUS;

--------------------------------------------------------------------------------------------------//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 '1994-08-01' AND
	O_ORDERDATE < date '1997-08-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;

------------------------------------------------------------------------------------------------//Q19
SELECT 
	SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE
FROM 
	LINEITEM,
	PART
WHERE 
	(
		P_PARTKEY = L_PARTKEY AND
		P_BRAND = 'Brand#41' 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#33' AND
		P_CONTAINER IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND
		L_QUANTITY >= 14 AND L_QUANTITY <= 14 + 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 >= 25 AND L_QUANTITY <= 25 + 10 AND
		P_SIZE BETWEEN 1 AND 15 AND
		L_SHIPMODE IN ('AIR', 'AIR REG') AND
		L_SHIPINSTRUCT = 'DELIVER IN PERSON'
	);

-----------------------------------------------------------------------------------------//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 = 'AFRICA' AND
	O_ORDERDATE >= date '1997-01-01' AND
	O_ORDERDATE < date '1997-01-01' + interval '1' year
GROUP BY 
	N_NAME
ORDER BY 
	REVENUE DESC;

--------------------------------------------------------------------------------------------//Q7
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 = 'ROMANIA')
				OR	(N1.N_NAME = 'ROMANIA' AND N2.N_NAME = 'CANADA')
				) 
				AND L_SHIPDATE BETWEEN '1995-01-01' AND '1996-12-31'
		) SHIPPING
GROUP BY 
	SUPP_NATION,
	CUST_NATION,
	L_YEAR
ORDER BY 
	SUPP_NATION,
	CUST_NATION,
	L_YEAR;

---------------------------------------------------------------------------------------------------//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','SHIP') 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;