CREATE DATABASE [ IF NOT EXISTS ] database_name

CREATE DATABASE ocient;

DROP DATABASE [ IF EXISTS ] database_name [, ...]

DROP DATABASE ocient;

ALTER DATABASE old_database_name RENAME TO new_database_name

ALTER DATABASE oracle RENAME TO ocient;

ALTER DATABASE database
    ALTER SSO INTEGRATION sso_name

ALTER DATABASE example_database
    SET SSO INTEGRATION sso_test; 

ALTER DATABASE database
    ALTER SSO INTEGRATION sso_name

ALTER DATABASE example_database
    ALTER SSO INTEGRATION sso_test; 

ALTER DATABASE database REMOVE SSO INTEGRATION

ALTER DATABASE example_database REMOVE SSO INTEGRATION;

ALTER DATABASE database ALTER SECURITY <security_setting> [=] <value>

ALTER DATABASE example_db 
    ALTER SECURITY password_lifetime_days = 20;

CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] table_name 
    [ ( <column_definition> [, ...] | <clustering_definition> ) ]  
    [ [ WITH ] <create_option> [, ... ] ] 
    [ AS (query) ]

<column_definition> ::=
   column_name [ data_type ] [ <timekey_definition> 
   | <column_constraint> [, ...] ]

<timekey_definition> ::=
  TIME KEY BUCKET (bucket_granularity, bucket_value) [ <column_constraint> ]

<column_constraint> ::=
   TIME KEY BUCKET( bucket_granularity, bucket_value )
   | [ NOT ] NULL
   | DEFAULT literal
   | COMMENT comment
   | COMPRESSION GDC [ (compression_value), EXISTING schema_name ]
   | COMPRESSION [ compression_scheme ]

<clustering_definition> ::=
    CLUSTERING KEY key_name (ck_col1, ck_col2 [, ...])
        [, INDEX index_name (idx_col1, idx_col2 [, ...])
        [, INDEX ... ] ]
           
 <create_option> ::= 
   STORAGESPACE storage_space_name
   | SEGMENTSIZE segment_value
   | REDUNDANCY segment_part (redundancy_scheme)
   | STREAMLOADER_PROPERTIES streamloader_json
   | COMMENT '<string>'

CLUSTERING KEY key_name (ck_col1, ck_col2 [, ...])
    [, INDEX index_name (idx_col1, idx_col2 [, ...])
    [, INDEX ... ] ]

column_name [ data_type ] TIME KEY BUCKET (bucket_granularity, bucket_value [, NOINDEX] )

 CREATE TABLE trades (
    id UUID NOT NULL,
    ticker_symbol VARCHAR(255) NOT NULL COMPRESSION GDC(2),
    t_type VARCHAR(255) NOT NULL COMPRESSION GDC(1),
    raw_ticker_data VARCHAR(255),
    created_at TIMESTAMP NOT NULL TIME KEY BUCKET(1, HOUR),
    array_of_tuples tuple<<varchar(2040), byte, bigint, double, timestamp, date, time, decimal(3,2), st_point, boolean, binary(6), hash(8), ip, uuid>>[],
    CLUSTERING INDEX idx_ticker_symbol_type (ticker_symbol, t_type),
    INDEX idx_type (t_type)
 )
    STORAGESPACE ocient_storage,
    REDUNDANCY DATA (PARITY),
    REDUNDANCY MANIFEST (COPY),
    STREAMLOADER_PROPERTIES '{
    "pageQueryExclusionDuration" : "2700s"
}';

CREATE TABLE original_table (
 	col_int INT NOT NULL DEFAULT 123456789,
 	col_bigint BIGINT NOT NULL, 
 	col_id INT NOT NULL,
	col_point POINT NOT NULL DEFAULT 'POINT(0 0)',
	col_timestamp TIMESTAMP TIME KEY BUCKET (1,DAY) NOT NULL,
	col_varchar VARCHAR(255) COMPRESSION ZSTD,
	CLUSTERING KEY ck (col_bigint, col_id)
)
WITH 
    INDEX idx_01 (col_varchar) USING HASH, 
    INDEX idx_02 (col_point) USING SPATIAL;

CREATE TABLE basic_ctas AS (
	SELECT * FROM original_table ); 

EXPORT TABLE basic_ctas; 

CREATE TABLE basic_ctas (
    "col_int" INT NOT NULL,
    "col_bigint" BIGINT NOT NULL,
    "col_id" INT NOT NULL,
    "col_point" POINT NOT NULL,
    "col_timestamp" TIMESTAMP NOT NULL,
    "col_varchar" VARCHAR(536870912) COMPRESSION DYNAMIC NULL
)
REDUNDANCY cde (PARITY),
REDUNDANCY column_metadata (COPY),
REDUNDANCY data (PARITY),
REDUNDANCY index (PARITY),
REDUNDANCY pdf (PARITY),
REDUNDANCY skip_lists (COPY),
REDUNDANCY stats (PARITY),
REDUNDANCY summary_stats (PARITY),
REDUNDANCY table_of_contents (COPY),
STORAGESPACE "ss0",
SEGMENTSIZE 4;

CREATE TABLE complex_ctas (
 	col_amt INT NOT NULL,
 	col_phone BIGINT NOT NULL COMPRESSION DYNAMIC, 
 	col_id INT NOT NULL,
	col_point POINT NOT NULL DEFAULT 'POINT(0 0)',
	col_timestamp TIMESTAMP TIME KEY BUCKET (1,HOUR) NOT NULL,
	col_varchar VARCHAR(255) COMPRESSION DYNAMIC,
	CLUSTERING KEY ck (col_amt, col_phone, col_id)
)
WITH 
    INDEX idx_01 (col_varchar) USING NGRAM(3), 
    INDEX idx_02 (col_point) USING SPATIAL
AS (
	SELECT * FROM original_table ); 

CREATE TABLE subset (
 	col_amt INT NOT NULL,
 	col_phone BIGINT NOT NULL, 
 	col_id INT NOT NULL
)
WITH 
    REDUNDANCY cde (PARITY), 
    SEGMENTSIZE 3
AS (
    SELECT 
        col_int, 
        col_bigint,
        col_id
FROM 
    original_table ); 

CREATE TABLE calc_table (
 	col_int_multiply INT NOT NULL,
 	col_month_add TIMESTAMP NOT NULL, 
 	col_year INT NOT NULL, 
 	col_substring VARCHAR(255)
)
AS (
    SELECT
        col_int * 10, 
        ADD_MONTHS(col_timestamp, 3),
        YEAR(col_timestamp), 
        SUBSTRING(col_varchar, 1, 3)
FROM 
    original_table ); 

CREATE TABLE my_schema.my_ctas_table_2 (
   CLUSTERING INDEX idx (int_col)
 )
 USING LOADERS "stream-loader1"
 AS (SELECT int_col FROM my_schema.my_table);

CREATE TABLE my_schema.my_ctas_table_2 (
   CLUSTERING INDEX idx (int_col)
 )
 USING LOADERS "stream-loader2","stream-loader3"
 AS (SELECT int_col FROM my_schema.my_table);

DROP TABLE [ IF EXISTS ] table_name [, ...]

DROP TABLE employees;

DROP TABLE employees, departments;

ALTER TABLE [ IF EXISTS ] old_table_name RENAME TO new_table_name

ALTER TABLE us.employees RENAME TO mid_west_employees;

ALTER TABLE us.employees RENAME TO north_america.employees;

ALTER TABLE [ IF EXISTS ] table_name RENAME COLUMN old_column_name TO new_column_name

ALTER TABLE employees RENAME COLUMN name TO first_name;

ALTER TABLE [ IF EXISTS ] table_name ADD COLUMN <column_definition>;

<column_definition> ::=
   column_name [ data_type ] [ <column_constraint> [, ...] ]

<column_constraint> ::=
   TIME KEY BUCKET(bucket_granularity [, bucket_value ] )
   | [ NOT ] NULL
   | DEFAULT literal
   | COMMENT comment
   | COMPRESSION GDC [ (compression_value), EXISTING schema_name ]
   | COMPRESSION [ compression_scheme ]

ALTER TABLE employees ADD COLUMN 
    new_column BIGINT 
        NOT NULL 
        DEFAULT 0;

ALTER TABLE employees ADD COLUMN 
    new_column BIGINT
    NULL;

ALTER TABLE [ IF EXISTS ] table_name ALTER COLUMN column_name 
    SET COMPRESSION [ compression_scheme ];

ALTER TABLE employees ALTER COLUMN employee_name 
    SET COMPRESSION LZ4;

ALTER TABLE employees ALTER COLUMN employee_name 
    SET COMPRESSION ZSTD 
        compression_level=5, 
        dictionary_size=32768;

ALTER TABLE [ IF EXISTS ] table_name ALTER REDUNDANCY segment_part (redundancy_scheme)

ALTER TABLE employees ALTER REDUNDANCY STATS (COPY);

ALTER TABLE [ IF EXISTS ] table_name DROP COLUMN column_name [ IF EXISTS ]

ALTER TABLE employees DROP COLUMN address;

ALTER TABLE [ IF EXISTS ] table_name STREAMLOADER_PROPERTIES streamloader_json

ALTER TABLE employees
    STREAMLOADER_PROPERTIES '{
        "pageQueryExclusionDuration" : "30s"
        }';

ALTER TABLE [ IF EXISTS ] table_name DISABLE INDEX 
    { index_name_or_uuid | IN (index_name_or_uuid [,...]) }

ALTER TABLE employees DISABLE INDEX current_idx;

ALTER TABLE employees DISABLE INDEX '5c15d8de-36fa-4055-9bdc-3f1750aaeea0';

ALTER TABLE employees DISABLE INDEX IN (current_idx, other_idx);

ALTER TABLE [ IF EXISTS ] table_name ENABLE INDEX index_name_or_uuid

ALTER TABLE employees ENABLE INDEX current_idx;

ALTER TABLE employees ENABLE INDEX '5c15d8de-36fa-4055-9bdc-3f1750aaeea0';

ALTER TABLE employees ENABLE INDEX IN (current_idx, other_idx);

DELETE FROM table_name [ WITH cte ] [ WHERE <filter_clause> ]

DELETE FROM movies WHERE budget < 10000; 

DELETE FROM transactions
    WITH old_transactions AS (
        SELECT 
            transaction_id
        FROM 
        	transactions
    	WHERE transaction_date < '2022-01-01'
        	AND amount < 100
	)
	WHERE transaction_id IN (
		SELECT transaction_id 
		FROM old_transactions );

EXPORT TABLE table_name

EXPORT TABLE trades;

CREATE TABLE "admin@system"."trade_test" (
    "id" UUID NOT NULL,
    "ticker_symbol" VARCHAR(1048576) COMPRESSION GDC(2) NOT NULL,
    "t_type" VARCHAR(1048576) COMPRESSION GDC(1) NOT NULL,
    "raw_ticker_data" VARCHAR(1048576) COMPRESSION DYNAMIC NULL,
    "created_at" TIMESTAMP TIME KEY BUCKET(1, HOUR) NOT NULL,
    "array_of_tuples" TUPLE<<VARCHAR(1048576) COMPRESSION DYNAMIC,TINYINT,BIGINT,DOUBLE PRECISION,TIMESTAMP,DATE,TIME,DECIMAL(3,2),POINT,BOOLEAN,BINARY(6),BINARY(8),IP,UUID>>[] NULL,
    CLUSTERING INDEX "idx_ticker_symbol_type" ("ticker_symbol", "t_type"),
    INDEX "idx_type" ("t_type")
)
REDUNDANCY cde (PARITY),
REDUNDANCY manifest (COPY),
REDUNDANCY pdf (PARITY),
REDUNDANCY stats (PARITY),
REDUNDANCY column_metadata (COPY),
REDUNDANCY index (PARITY),
REDUNDANCY summary_stats (PARITY),
REDUNDANCY skip_lists (COPY),
REDUNDANCY data (PARITY),
STORAGESPACE "storage",
SEGMENTSIZE 4,
STREAMLOADER_PROPERTIES '{"pageQueryExclusionDuration" : "30s"}';
CREATE INDEX "new_idx" ON "admin@system"."trade_test" ("raw_ticker_data") USING HASH;

INSERT INTO table_name [ ( col1, col2 [, ...] ) ] 
    [ WITH cte ] { query | VALUES [ <rows_to_insert> ] } 
    
<rows_to_insert> ::=     
    ( row1_col1, row1_col2 [, ...] ), 
    ( row2_col1, row2_col2 [, ...] )
    [, ...] 

INSERT INTO system.table_a SELECT * FROM system.table_b;

INSERT INTO system.table_a (id_col_a, int_col_a) 
    SELECT id_col_b, int_col_b FROM system.table_b;

CREATE TABLE sales (
    product INT NOT NULL,
    quantity INT NOT NULL,
    sale_date DATE NOT NULL
);

INSERT INTO sales (product, quantity, sale_date)
    VALUES 
        (1, 10, '2023-01-15'),
        (2, 5, '2023-01-20'),
        (1, 8, '2023-02-05');

CREATE TABLE monthly_sales_summary (
    "product_id" INT NOT NULL,
    "month" DATE NOT NULL,
    "total_quantity" INT NOT NULL
);

INSERT INTO monthly_sales_summary (product_id, month, total_quantity)
WITH monthly_totals AS (
    SELECT 
        product,
        DATE_TRUNC('month', sale_date) AS month,
        SUM(quantity_sold) AS total_quantity
    FROM 
        sales
    GROUP BY 
        product, 
        DATE_TRUNC('month', sale_date)
)
SELECT 
    product, 
    month, 
    total_quantity
FROM 
    monthly_totals;

INSERT INTO TABLE table_name USING LOADERS streamloader [, ... ]
     query

INSERT INTO system.table_a (id_col_a, int_col_a) 
    USING LOADERS "stream-loader1"
    SELECT id_col_b, int_col_b FROM system.table_b;

INSERT INTO system.table_a (id_col_a, int_col_a) 
    USING LOADERS "stream-loader2","stream-loader3"
    SELECT id_col_b, int_col_b FROM system.table_b;

TRUNCATE TABLE table_name

TRUNCATE TABLE table_name WHERE segment_group_id = <ID>

TRUNCATE TABLE table_name WHERE segment_group_id in (<ID>, ...)

TRUNCATE TABLE students;

TRUNCATE TABLE us.students;

TRUNCATE TABLE students WHERE segment_group_id = 123456789;

TRUNCATE TABLE us.students WHERE segment_group_id IN (1,2,3,4,5);

CREATE [ OR REPLACE ] VIEW [ IF NOT EXISTS ] view_name AS query

CREATE VIEW option_trades 
    AS SELECT * FROM trades WHERE type = 'OPTIONS';

DROP VIEW [ IF EXISTS ] view_name [, ...]

DROP VIEW star_employees;

DROP VIEW star_employees, bad_employees;

ALTER VIEW [ IF EXISTS ] old_view_name RENAME TO new_view_name

ALTER VIEW star_employees RENAME TO star_mid_west_employees;

ALTER VIEW us.star_employees RENAME TO us.star_mid_west_employees;

ALTER VIEW [ IF EXISTS ] view_name AS query

ALTER VIEW star_employees AS SELECT * FROM sys.tables;

EXPORT VIEW view_name

EXPORT VIEW students;

CREATE INDEX [ IF NOT EXISTS ] index_name ON table (column_name) [ USING <index_type> ]

<index_type> ::=
   INVERTED | HASH | NGRAM [ (n_value) ] | SPATIAL  

CREATE INDEX new_idx ON employees (address);

CREATE INDEX ngram_address_idx ON employees (address) USING NGRAM;

CREATE INDEX tuple_index ON employees (tuple_col[1]);

CREATE INDEX spatial_index ON employees (point_col)

DROP INDEX [ IF EXISTS ] index_name ON table_name

DROP INDEX new_idx ON employees;