tpch_to_mariadb_with_view_lowercase.sql

CREATE DATABASE tpch character set utf8mb4;
USE tpch;

CREATE TABLE nation  ( 
	N_NATIONKEY INTEGER primary key,
    N_NAME       CHAR(25) NOT NULL,
    N_REGIONKEY  INTEGER NOT NULL,
    N_COMMENT    VARCHAR(152));
							
CREATE TABLE region  ( 
	R_REGIONKEY INTEGER primary key,
    R_NAME       CHAR(25) NOT NULL,
    R_COMMENT    VARCHAR(152));
							
CREATE TABLE part  ( 
	P_PARTKEY INTEGER primary key,
    P_NAME        VARCHAR(55) NOT NULL,
    P_MFGR        CHAR(25) NOT NULL,
    P_BRAND       CHAR(10) NOT NULL,
    P_TYPE        VARCHAR(25) NOT NULL,
    P_SIZE        INTEGER NOT NULL,
    P_CONTAINER   CHAR(10) NOT NULL,
    P_RETAILPRICE DECIMAL(15,2) NOT NULL,
    P_COMMENT     VARCHAR(23) NOT NULL );
						  
CREATE TABLE supplier  ( 
	S_SUPPKEY INTEGER primary key,
    S_NAME        CHAR(25) NOT NULL,
    S_ADDRESS     VARCHAR(40) NOT NULL,
    S_NATIONKEY   INTEGER NOT NULL,
    S_PHONE       CHAR(15) NOT NULL,
    S_ACCTBAL     DECIMAL(15,2) NOT NULL,
    S_COMMENT     VARCHAR(101) NOT NULL);
							 
CREATE TABLE partsupp  ( 
	PS_PARTKEY INTEGER NOT NULL,
    PS_SUPPKEY     INTEGER NOT NULL,
    PS_AVAILQTY    INTEGER NOT NULL,
    PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
    PS_COMMENT     VARCHAR(199) NOT NULL, primary key (PS_PARTKEY, PS_SUPPKEY) );
						
CREATE TABLE customer  ( 
	C_CUSTKEY INTEGER primary key,
    C_NAME        VARCHAR(25) NOT NULL,
    C_ADDRESS     VARCHAR(40) NOT NULL,
    C_NATIONKEY   INTEGER NOT NULL,
    C_PHONE       CHAR(15) NOT NULL,
    C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
    C_MKTSEGMENT  CHAR(10) NOT NULL,
    C_COMMENT     VARCHAR(117) NOT NULL);
							 
CREATE TABLE orders  ( 
	O_ORDERKEY INTEGER primary key,
    O_CUSTKEY        INTEGER NOT NULL,
    O_ORDERSTATUS    CHAR(1) NOT NULL,
    O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
    O_ORDERDATE      DATE NOT NULL,
    O_ORDERPRIORITY  CHAR(15) NOT NULL,
    O_CLERK          CHAR(15) NOT NULL,
    O_SHIPPRIORITY   INTEGER NOT NULL,
    O_COMMENT        VARCHAR(79) NOT NULL);
						   
CREATE TABLE lineitem ( 
	L_ORDERKEY INTEGER NOT NULL,
    L_PARTKEY     INTEGER NOT NULL,
    L_SUPPKEY     INTEGER NOT NULL,
    L_LINENUMBER  INTEGER 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  CHAR(1) NOT NULL,
    L_LINESTATUS  CHAR(1) NOT NULL,
    L_SHIPDATE    DATE NOT NULL,
    L_COMMITDATE  DATE NOT NULL,
    L_RECEIPTDATE DATE NOT NULL,
    L_SHIPINSTRUCT CHAR(25) NOT NULL,
    L_SHIPMODE     CHAR(10) NOT NULL,
    L_COMMENT      VARCHAR(44) NOT NULL,
    primary key(L_ORDERKEY,L_LINENUMBER));
	
	
	

SET GLOBAL local_infile = 1;

LOAD DATA LOCAL INFILE 'PATH/nation.tbl' INTO TABLE `nation` FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA LOCAL INFILE 'PATH/region.tbl' INTO TABLE `region` FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA LOCAL INFILE 'PATH/part.tbl' INTO TABLE `part` FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA LOCAL INFILE 'PATH/supplier.tbl' INTO TABLE `supplier` FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n'; 
LOAD DATA LOCAL INFILE 'PATH/partsupp.tbl' INTO TABLE `partsupp` FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';	
LOAD DATA LOCAL INFILE 'PATH/customer.tbl' INTO TABLE `customer` FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA LOCAL INFILE 'PATH/orders.tbl' INTO TABLE `orders` FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n'; 
LOAD DATA LOCAL INFILE 'PATH/lineitem.tbl' INTO TABLE `lineitem` FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n'; 


ALTER TABLE region ADD PRIMARY KEY IF NOT EXISTS(R_REGIONKEY);
ALTER TABLE nation ADD PRIMARY KEY IF NOT EXISTS (N_NATIONKEY);
ALTER TABLE nation ADD FOREIGN KEY IF NOT EXISTS nation_FK1 (N_REGIONKEY) references region(R_REGIONKEY);
ALTER TABLE part   ADD PRIMARY KEY IF NOT EXISTS(P_PARTKEY);
ALTER TABLE supplier ADD PRIMARY KEY IF NOT EXISTS (S_SUPPKEY);
ALTER TABLE supplier ADD FOREIGN KEY IF NOT EXISTS supplier_FK1 (S_NATIONKEY) references nation(N_NATIONKEY);
ALTER TABLE partsupp ADD PRIMARY KEY IF NOT EXISTS (PS_PARTKEY,PS_SUPPKEY);
ALTER TABLE customer ADD PRIMARY KEY IF NOT EXISTS (C_CUSTKEY);
ALTER TABLE customer ADD FOREIGN KEY IF NOT EXISTS customer_FK1 (C_NATIONKEY) references nation(N_NATIONKEY);
ALTER TABLE lineitem ADD PRIMARY KEY IF NOT EXISTS (L_ORDERKEY,L_LINENUMBER);
ALTER TABLE partsupp ADD FOREIGN KEY IF NOT EXISTS partsupp_FK1 (PS_SUPPKEY) references supplier(S_SUPPKEY);
ALTER TABLE partsupp ADD FOREIGN KEY IF NOT EXISTS partsupp_FK2 (PS_PARTKEY) references part(P_PARTKEY);
ALTER TABLE orders   ADD FOREIGN KEY IF NOT EXISTS orders_FK1 (O_CUSTKEY) references customer(C_CUSTKEY);
ALTER TABLE lineitem ADD FOREIGN KEY IF NOT EXISTS lineitem_FK1 (L_ORDERKEY)  references orders(O_ORDERKEY);
ALTER TABLE lineitem ADD FOREIGN KEY IF NOT EXISTS lineitem_FK2 (L_PARTKEY,L_SUPPKEY) references partsupp(PS_PARTKEY, PS_SUPPKEY);

create view revenue15 (supplier_no, total_revenue) as 
select SQL_NO_CACHE l_suppkey, sum(l_extendedprice * (1 - l_discount)) 
from lineitem where l_shipdate >= date '1996-01-01' 
and l_shipdate < date '1996-01-01' + interval '3' month group by l_suppkey;