\! gs_ktool -d all
\! gs_ktool -g

CREATE CLIENT MASTER KEY cmk1 WITH ( KEY_STORE = gs_ktool , KEY_PATH = "gs_ktool/1" , ALGORITHM = AES_256_CBC);
CREATE COLUMN ENCRYPTION KEY cek1 WITH VALUES (CLIENT_MASTER_KEY = cmk1, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);
CREATE COLUMN ENCRYPTION KEY cek2 WITH VALUES (CLIENT_MASTER_KEY = cmk1, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);
CREATE TABLE IF NOT EXISTS sales2007q1(name varchar(50), amount NUMERIC ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC));
CREATE TABLE IF NOT EXISTS sales2007q2 (name varchar(50), amount NUMERIC ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC));
CREATE TABLE IF NOT EXISTS sales2007q3(name varchar(50), amount NUMERIC ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek2, ENCRYPTION_TYPE = RANDOMIZED));
CREATE TABLE IF NOT EXISTS sales2007q4 (name varchar(50), amount NUMERIC ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek2, ENCRYPTION_TYPE = RANDOMIZED));
CREATE TABLE IF NOT EXISTS sales2007q5(name varchar(50) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC), amount NUMERIC);
CREATE TABLE IF NOT EXISTS sales2007q6 (name varchar(50) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC), amount NUMERIC);

insert into sales2007q1 values ('Mike', 150000.25), ('Jon', 132000.75), ('Mary', 10000);
insert into sales2007q2 values ('Mike', 120000.25), ('Jon', 142000.75), ('Mary', 10000);
insert into sales2007q3 values ('Mike', 150000.25), ('Jon', 132000.75), ('Mary', 10000);
insert into sales2007q4 values ('Mike', 120000.25), ('Jon', 142000.75), ('Mary', 10000);
insert into sales2007q5 values ('Mike', 150000.25), ('Jon', 132000.75), ('Mary', 10000);
insert into sales2007q6 values ('Mike', 120000.25), ('Jon', 142000.75), ('Mary', 10000);
select name from sales2007q1 ORDER BY name ASC;
select name from sales2007q2 ORDER BY name ASC;
SELECT name FROM sales2007q1 UNION SELECT name FROM sales2007q2 ORDER BY name ASC;
SELECT name FROM sales2007q1 INTERSECT SELECT name FROM sales2007q2 ORDER BY name ASC;
SELECT name FROM sales2007q1 EXCEPT SELECT name FROM sales2007q2 ORDER BY name ASC;
SELECT * FROM sales2007q3 UNION SELECT * FROM sales2007q2 ORDER BY name ASC;
SELECT name FROM sales2007q3 UNION SELECT name FROM sales2007q4 ORDER BY name ASC;
SELECT name FROM sales2007q3 UNION SELECT name FROM sales2007q4 ORDER BY name ASC;
SELECT name FROM sales2007q3 INTERSECT SELECT name FROM sales2007q4 ORDER BY name ASC;
SELECT name FROM sales2007q3 INTERSECT SELECT name FROM sales2007q4 ORDER BY name ASC;
SELECT name FROM  sales2007q3 EXCEPT SELECT name FROM  sales2007q4 ORDER BY name ASC;
SELECT name FROM sales2007q1 UNION ALL SELECT name FROM sales2007q2 ORDER BY name ASC;
SELECT name FROM sales2007q3 UNION ALL SELECT name FROM sales2007q4 ORDER BY name ASC;
SELECT amount FROM sales2007q5 UNION ALL SELECT amount FROM sales2007q6 ORDER BY amount ASC;

CREATE TABLE t_setop (c1 int, c2 int ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC), 
    c3 numeric, c4 numeric ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC));
insert into t_setop values
    (50,50,5.5,5.5),
    (60,60,6.6,6.6),
    (10,10,1.2,1.2),
    (30,30,1.2,1.2),
    (40,40,4.4,4.4);
SELECT c2 FROM t_setop UNION SELECT c4 FROM t_setop;

drop table t_setop;
drop table sales2007q1;
drop table sales2007q2;
drop table sales2007q3;
drop table sales2007q5;
drop table sales2007q4;
drop table sales2007q6;

DROP CLIENT MASTER KEY cmk1 CASCADE;

\! gs_ktool -d all