drop database if exists tpch300s;
go
create database tpch300s;
go
use tpch300s;

create table part(
	p_partkey		bigint			not null,
	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			bigint			not null,
	p_container		char(10)		not null,
	p_retailprice	decimal(15,2)	not null,
	p_comment		varchar(23)		null
);
create clustered columnstore index idx_part on part;

create table supplier(
	s_suppkey		bigint			not null,
	s_name			char(25)		not null,
	s_address		varchar(40)		not null,
	s_nationkey		bigint			not null,
	s_phone			char(15)		not null,
	s_acctbal		decimal(15,2)	not null,
	s_comment		varchar(101)	null
);
create clustered columnstore index idx_supplier on supplier;

create table partsupp(
	ps_partkey		bigint			not null,
	ps_suppkey		bigint			not null,
	ps_availqty		bigint			not null,
	ps_supplycost	decimal(15,2)	not null,
	ps_comment		varchar(199)	null
);
create clustered columnstore index idx_partsupp on partsupp;

create table region(
	r_regionkey		bigint			not null,
	r_name			char(25)		not null,
	r_comment		varchar(152)	null
);
create clustered columnstore index idx_region on region;

create table nation(
	n_nationkey		bigint			not null,
	n_name			char(25)		not null,
	n_regionkey		bigint			not null,
	n_comment		varchar(152)	null
);
create clustered columnstore index idx_nation on nation;

create table customer(
	c_custkey		bigint			not null,
	c_name			varchar(25)		not null,
	c_address		varchar(40)		not null,
	c_nationkey		bigint			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)	null
);
create clustered columnstore index idx_customer on customer;

create table orders(
	o_orderkey		bigint			not null,
	o_custkey		bigint			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	bigint			not null,
	o_comment		varchar(79)		null
);
create clustered columnstore index idx_orders on orders;

create table lineitem(
	l_orderkey		bigint			not null,
	l_partkey		bigint			not null,
	l_suppkey		bigint			not null,
	l_linenumber	bigint			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)		null
);
create clustered columnstore index idx_lineitem on lineitem;