ddl.sql

-- a short file to demo basic ddl loading functions in Splice Machine

--create schema
create schema DDL;

--set schema
set schema DDL;

--check current schema
values(current schema);

--create CUSTOMERS table
create table CUSTOMERS (
	id integer not null,
	first_name varchar(100),
		active char(1),
	create_dt date DEFAULT CURRENT_DATE,
		primary key(id)
	);

--describe the table (see schma of table)
describe customers;

--show primary keys
show primarykeys from customers;

--add a missing column
alter table CUSTOMERS add column last_name varchar(100);

insert into CUSTOMERS(id, first_name,active,last_name) values (1, 'Mary', 'A','Smith');

-- prepared statements
prepare customers as insert into CUSTOMERS(id, first_name,active,last_name) values (?,?,?,?);

--executing a prepared statement
execute customers using 'values (3, ''John'', ''A'', ''Jones'')';	

--prepared and executing prepare statements both fail!

--creating new table (using 'as identity' function)
create table ORDERHEADER (
	id integer NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 5, INCREMENT BY 5) CONSTRAINT Order_PK PRIMARY KEY,
	customer_id integer not null,
	total DECIMAL(19,2),
	create_dt date DEFAULT CURRENT_DATE 
	);

--insert a record into that table
insert into ORDERHEADER (customer_id, total)  values (1,150.00);

--create ORDERLINE table
create table ORDERLINE (
	id BIGINT NOT NULL PRIMARY KEY,
	order_id integer,
	product_id INTEGER NOT NULL,
	quantity INTEGER NOT NULL,
	total DECIMAL (19,2),
	create_dt DATE
	);

-- let's create an index on the order line table for order_id
create index IDX_OR_LINE_ORD_ID on ORDERLINE(order_id);

--create customer_phone table with a composite primary key
create table CUSTOMER_PHONE (
	customer_id integer not null,
	phone varchar(20),
	status char(1) DEFAULT 'A',
	create_dt DATE DEFAULT CURRENT_DATE,
	primary key (customer_id, phone)
	);

-- create a table by copying another with the schema we want
--neither primary key nor data is copied over
create table CUSTOMER_ORIG_1 as select * from CUSTOMERS with no data;

--create a table by copying another with the schema we want, data is copied
create table customer_orig_2 as select * from customers with data;

--insert data into CUSTOMER_PHONE table
insert into CUSTOMER_PHONE (customer_id, phone, status) values (1, '555-555-5555', 'A');

-- create a view, join customer data with the phone table
create view FULL_CUSTOMER as select c.first_name, c.last_name, p.phone from CUSTOMERS c
	left outer join CUSTOMER_PHONE p on c.id = p.customer_id;

-- delete data from a table
delete from customer_phone;

-- delete data from a table according to specific parameter (not specific to this example)
delete from customer_phone where phone = '555-555-5555';

-- delete all data from a table
truncate table customer_phone;

-- drop - remove a table entirely
drop table CUSTOMER_ORIG_1

-- drop view
drop view FULL_CUSTOMER;

-- drop index
drop index IDX_OR_LINE_ORD_ID;