SAP HANA SQL
-- Alter Table:
---------------
CREATE COLUMN TABLE "QBEXSQL"."EMPtab4ALTER"
(
"EMPID" INT ,
"EMPNAME" VARCHAR(20),
"DEPTNO" VARCHAR(5)
);
-- Add New Column:
------------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
ADD ("DEPTNAME" VARCHAR(20));
-- Drop Column
---------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
DROP ( "DEPTNAME" );
-- Alter Column Definition
--------------------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
ALTER ( "DEPTNO" VARCHAR(10));
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
ALTER ( "EMPID" BIGINT);
-- Add Constraint
-----------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
ADD CONSTRAINT PK PRIMARY KEY ("EMPID");
-- Drop Primary key
--------------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
DROP PRIMARY KEY;
-- Preload Clause
-----------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
PRELOAD ("EMPNAME", "DEPTNO");
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
PRELOAD ALL;
-- Alter Table Type
--------------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
ALTER TYPE ROW;
-- Add Partition
-----------------
ALTER TABLE "QBEXSQL"."RANGEPARTMONTH"
ADD PARTITION VALUE = '2015-08' ;
-- Drop Partition:
-------------------
ALTER TABLE "QBEXSQL"."RANGEPARTMONTH"
DROP PARTITION VALUE = '2015-08' ;
-- Auto Merge
-------------
ALTER TABLE "QBEXSQL"."RANGEPARTMONTH"
DISABLE AUTOMERGE ;
ALTER TABLE "QBEXSQL"."RANGEPARTMONTH"
ENABLE AUTOMERGE ;
-- Unload Priority
------------------
ALTER TABLE "QBEXSQL"."RANGEPARTMONTH"
UNLOAD PRIORITY 6 ;
-- Auto Merge
-- Merge Dog
CREATE COLUMN TABLE "QBEXSQL"."EMPMERGE"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
) NO AUTO MERGE;
-- Create DB Table:
-------------------
--1. Create Simple COLUMN Table
CREATE COLUMN TABLE "QBEXSQL"."EMP"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
--2. Create Simple ROW Table
CREATE ROW TABLE "QBEXSQL"."EMPROW"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
-- Comments:
------------
CREATE COLUMN TABLE "QBEXSQL"."EMPCOMMENTS"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
COMMENT ON TABLE "QBEXSQL"."EMPCOMMENTS" IS 'Employee Details Table';
COMMENT ON COLUMN "QBEXSQL"."EMPCOMMENTS"."EMPID" IS 'Employee Number';
COMMENT ON COLUMN "QBEXSQL"."EMPCOMMENTS"."EMPNAME" IS 'Employee Name';
<NotepadPlus>
<Session activeView="0">
<mainView activeIndex="10">
<File firstVisibleLine="0" xOffset="0" scrollWidth="460" startPos="47" endPos="0" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Schema.txt" backupFilePath="C:\Users\Pratap\AppData\Roaming\Notepad++\backup\Schema.txt@2015-07-06_113507" originalFileLastModifTimestamp="1436162532" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="610" startPos="179" endPos="77" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Basic Column and Row Store Tables.txt" backupFilePath="" originalFileLastModifTimestamp="1436163085" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="610" startPos="249" endPos="191" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Global Temporary Column Table.txt" backupFilePath="" originalFileLastModifTimestamp="1436163102" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="660" startPos="513" endPos="513" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Local Temporary Table.txt" backupFilePath="" originalFileLastModifTimestamp="1436163603" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="2081" startPos="178" endPos="178" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Logging.txt" backupFilePath="" originalFileLastModifTimestamp="1436164982" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="400" startPos="31" endPos="152" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Auto Merge.txt" backupFilePath="C:\Users\Pratap\AppData\Roaming\Notepad++\backup\Auto Merge.txt@2015-07-06_121440" originalFileLastModifTimestamp="1436165042" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="410" startPos="180" endPos="180" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Unload Pripority.txt" backupFilePath="" originalFileLastModifTimestamp="1436165193" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="430" startPos="453" endPos="453" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Hash Partitioning.txt" backupFilePath="" originalFileLastModifTimestamp="1436165517" />
<File firstVisibleLine="33" xOffset="0" scrollWidth="500" startPos="1112" endPos="1564" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Range Partitioning.txt" backupFilePath="" originalFileLastModifTimestamp="1436167539" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="460" startPos="34" endPos="34" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\RoundRobin Partitioning.txt" backupFilePath="" originalFileLastModifTimestamp="1436167928" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="870" startPos="0" endPos="0" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Copy DB Table.txt" backupFilePath="" originalFileLastModifTimestamp="1436169048" />
</mainView>
<subView activeIndex="0" />
</Session>
</NotepadPlus>
-- Create Table with Like or Subquery:
---------------------------------------
-- Base Table:
----------------
CREATE COLUMN TABLE "QBEXSQL"."EMPBASE"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
insert into "QBEXSQL"."EMPBASE" values (1,'Pratap');
insert into "QBEXSQL"."EMPBASE" values (2,'Pratap');
insert into "QBEXSQL"."EMPBASE" values (3,'Pratap');
CREATE COLUMN TABLE "QBEXSQL"."EMPWITHDATA" LIKE "QBEXSQL"."EMPBASE" WITH DATA;
CREATE COLUMN TABLE "QBEXSQL"."EMPWITHNODATA" LIKE "QBEXSQL"."EMPBASE" WITH NO DATA;
CREATE COLUMN TABLE "QBEXSQL"."EMPWITHDATASUB" AS
(
SELECT * FROM "QBEXSQL"."EMPBASE"
);
CREATE COLUMN TABLE "QBEXSQL"."EMPWITHNODATASUB" AS
(
SELECT * FROM "QBEXSQL"."EMPBASE"
) WITH NO DATA;
-- Triggers [max 1024 on one DB table]
--------------------------------------
CREATE COLUMN TABLE "QBEXSQL"."AUDIT_TABLE"
(
"USERNAME" VARCHAR(20) NOT NULL,
"TIMING" TIMESTAMP NOT NULL,
PRIMARY KEY ("USERNAME","TIMING")
);
CREATE COLUMN TABLE "QBEXSQL"."EMPTRIGGER"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
CREATE TRIGGER "QBEXSQL"."InsertLogs"
AFTER INSERT ON "QBEXSQL"."EMPTRIGGER" FOR EACH ROW
BEGIN
INSERT INTO "QBEXSQL"."AUDIT_TABLE" VALUES (CURRENT_USER,CURRENT_TIMESTAMP);
END;
INSERT INTO "QBEXSQL"."EMPTRIGGER" VALUES (1,'Pratap');
INSERT INTO "QBEXSQL"."EMPTRIGGER" VALUES (2,'Pratap');
INSERT INTO "QBEXSQL"."EMPTRIGGER" VALUES (3,'Pratap');
INSERT INTO "QBEXSQL"."EMPTRIGGER" VALUES (4,'Pratap');
SELECT * FROM "QBEXSQL"."AUDIT_TABLE";
-- Create & Drop Extended Storage:
----------------------------------
The CREATE EXTENDED STORAGE statement defines an external Sybase IQ database connection for the SAP HANA database.
The Sybase IQ database will automatically be used to transparently archive and query data stored in extended tables.
Only database users having the system privilege CREATE SOURCE or DATA ADMIN are allowed to create extended storage.
--3. Create Global temporary Table
-----------------------------------
CREATE GLOBAL TEMPORARY COLUMN TABLE "QBEXSQL"."EMPTMPCOLUMN"
(
"EMPID" INT NOT NULL,
"EMPNAME" VARCHAR(20)
);
INSERT INTO "QBEXSQL"."EMPTMPCOLUMN"
VALUES (1,'Pratap');
INSERT INTO "QBEXSQL"."EMPTMPCOLUMN"
VALUES (2,'Pratap');
INSERT INTO "QBEXSQL"."EMPTMPCOLUMN"
VALUES (3,'Pratap');
INSERT INTO "QBEXSQL"."EMPTMPCOLUMN"
VALUES (4,'Pratap');
select * from "QBEXSQL"."EMPTMPCOLUMN";
-- Table Partitioning
----------------------
-- Hash, Range, Round Robin
-- Hash
CREATE COLUMN TABLE "QBEXSQL"."EMPHASH"
(
"EMPID" INT PRIMARY KEY,
"EMPNAME" VARCHAR(20)
) PARTITION BY HASH ("EMPID") PARTITIONS 4;
INSERT INTO "QBEXSQL"."EMPHASH"
VALUES (1,'Pratap');
INSERT INTO "QBEXSQL"."EMPHASH"
VALUES (2,'Pratap');
INSERT INTO "QBEXSQL"."EMPHASH"
VALUES (3,'Pratap');
INSERT INTO "QBEXSQL"."EMPHASH"
VALUES (4,'Pratap');
-- Create Index
----------------
-- The CREATE INDEX statement creates an index on a table with the selected column(s).
-- When column data types are character string types, binary string types, decimal types,
-- or when the constraint is a composite key, or a non-unique constraint, the default index type is CPBTREE.
-- In other cases BTREE will be used. If neither BTREE nor CPBTREE keyword is specified,
-- then the SAP HANA database chooses the appropriate index type.
---------------------------------------------------------------------------------------------------------------------------------
--INDEXES : Indexes currently defined on tables.
--INDEX_COLUMNS : Index column information.
CREATE COLUMN TABLE "QBEXSQL"."EMPSIMPLEIND"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
CREATE INDEX "MYSIMPLENDEX" ON "QBEXSQL"."EMPSIMPLEIND" ("EMPNAME");
CREATE COLUMN TABLE "QBEXSQL"."EMPIND"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
CREATE UNIQUE CPBTREE INDEX "MYFIRSTINDEX" ON "QBEXSQL"."EMPIND" ("EMPNAME");
CREATE COLUMN TABLE "QBEXSQL"."EMPINDEX"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
CREATE UNIQUE INDEX "MYSECONDINDEX" ON "QBEXSQL"."EMPINDEX" ("EMPNAME");
--3. Create local temporary Table
-----------------------------------
CREATE LOCAL TEMPORARY COLUMN TABLE "QBEXSQL"."#EMPTMPLOCALCOLUMN"
(
"EMPID" INT,
"EMPNAME" VARCHAR(20)
);
INSERT INTO "QBEXSQL"."#EMPTMPLOCALCOLUMN"
VALUES (1,'Pratap');
INSERT INTO "QBEXSQL"."#EMPTMPLOCALCOLUMN"
VALUES (2,'Pratap');
INSERT INTO "QBEXSQL"."#EMPTMPLOCALCOLUMN"
VALUES (3,'Pratap');
INSERT INTO "QBEXSQL"."#EMPTMPLOCALCOLUMN"
VALUES (4,'Pratap');
select * from "QBEXSQL"."#EMPTMPLOCALCOLUMN";
-- Logging and NO Logging
-------------------------
-- Default Logging
-- no logging
--Specifies the retention time in seconds of the column table created by NO LOGGING.
--After the specified retention period has elapsed, the table will be dropped if used physical memory of the host is above 80%.
CREATE COLUMN TABLE "QBEXSQL"."EMPNOLOGGINGRET"
(
"EMPID" INT,
"EMPNAME" VARCHAR(20)
) NO LOGGING RETENTION 300;
INSERT INTO "QBEXSQL"."EMPNOLOGGINGRET"
VALUES (1,'Pratap');
INSERT INTO "QBEXSQL"."EMPNOLOGGINGRET"
VALUES (2,'Pratap');
INSERT INTO "QBEXSQL"."EMPNOLOGGINGRET"
VALUES (3,'Pratap');
INSERT INTO "QBEXSQL"."EMPNOLOGGINGRET"
VALUES (4,'Pratap');
select * from "QBEXSQL"."EMPNOLOGGINGRET";
-- Primary Key & Foreign Key:
-----------------------------
CREATE COLUMN TABLE "QBEXSQL"."DEPARTMENT"
(
"DEPTNO" INT NOT NULL PRIMARY KEY,
"DEPTNAME" VARCHAR(20)
);
CREATE COLUMN TABLE "QBEXSQL"."EMPLOYEES"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20),
"DEPTNO" INT,
FOREIGN KEY ("DEPTNO") REFERENCES "QBEXSQL"."DEPARTMENT"("DEPTNO") ON DELETE CASCADE
);
CREATE COLUMN TABLE "QBEXSQL"."EMPLOYEES"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20),
"DEPTNO" INT,
FOREIGN KEY ("DEPTNO") REFERENCES "QBEXSQL"."DEPARTMENT"("DEPTNO") ON DELETE CASCADE
);
-- Range Partitioning
---------------------
CREATE COLUMN TABLE "QBEXSQL"."RANGEPART"
(
"ORDERID" INT NOT NULL,
"ORDERDATE" DATE NOT NULL,
"NETSALES" DECIMAL(10,2),
PRIMARY KEY ("ORDERID","ORDERDATE")
) PARTITION BY RANGE ("ORDERDATE")
(
PARTITION '2015-01-01' <= VALUES < '2015-06-01',
PARTITION VALUE = '2015-07-15', PARTITION OTHERS
);
INSERT INTO "QBEXSQL"."RANGEPART"
VALUES (1,'2015-01-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPART"
VALUES (2,'2015-03-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPART"
VALUES (3,'2015-07-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPART"
VALUES (4,'2016-07-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPART"
VALUES (5,'2016-07-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPART"
VALUES (6,'2016-07-15',15000.00);
-- Month
CREATE COLUMN TABLE "QBEXSQL"."RANGEPARTMONTH"
(
"ORDERID" INT NOT NULL,
"ORDERDATE" DATE NOT NULL,
"NETSALES" DECIMAL(10,2),
PRIMARY KEY ("ORDERID","ORDERDATE")
) PARTITION BY RANGE (MONTH("ORDERDATE"))
(
PARTITION '2015-01' <= VALUES < '2015-06',
PARTITION VALUE = '2015-07', PARTITION OTHERS
);
INSERT INTO "QBEXSQL"."RANGEPARTMONTH"
VALUES (1,'2015-01-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPARTMONTH"
VALUES (2,'2015-03-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPARTMONTH"
VALUES (3,'2015-07-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPARTMONTH"
VALUES (4,'2016-07-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPARTMONTH"
VALUES (5,'2016-07-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPARTMONTH"
VALUES (6,'2016-07-15',15000.00);
-- Others:
-----------
1. Rename Column:
-----------------
rename COLUMN "QBEXSQL"."EMPtab4ALTER"."EMPID" TO "EMPNO";
2. Rename Table:
-----------------
RENAME TABLE "QBEXSQL"."EMPtab4ALTER1" TO "FORALTERS";
3. Rename Index:
-----------------
RENAME INDEX <OLDNAME> TO <NEWNAME>;
-- Round Robin:
---------------
-- No Primary key
CREATE COLUMN TABLE "QBEXSQL"."ROUNDROBIN"
(
"ORDERID" INT ,
"ORDERDATE" DATE,
"NETSALES" DECIMAL(10,2)
) PARTITION BY ROUNDROBIN PARTITIONS 4;
INSERT INTO "QBEXSQL"."ROUNDROBIN"
VALUES (1,'2015-01-15',15000.00);
INSERT INTO "QBEXSQL"."ROUNDROBIN"
VALUES (2,'2015-03-15',15000.00);
INSERT INTO "QBEXSQL"."ROUNDROBIN"
VALUES (3,'2015-07-15',15000.00);
INSERT INTO "QBEXSQL"."ROUNDROBIN"
VALUES (4,'2016-07-15',15000.00);
INSERT INTO "QBEXSQL"."ROUNDROBIN"
VALUES (5,'2016-07-15',15000.00);
INSERT INTO "QBEXSQL"."ROUNDROBIN"
VALUES (6,'2016-07-15',15000.00);
-- Schema
----------
Create Schema "QBEXSQL";
Create Schema "QBEXSQL" OWNED BY "<USERNAME>"
-- Create Sequence
------------------
CREATE SEQUENCE "QBEXSQL"."BillingDocNumbers"
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE
CACHE 5
RESET BY
( SELECT MAX("EMPID") FROM "QBEXSQL"."EMPSIMPLEIND");
CREATE TABLE "QBEXSQL"."EMPNAMES"
(
"EMPANMES" VARCHAR(20)
);
INSERT INTO "QBEXSQL"."EMPNAMES" VALUES ('Pratap');
INSERT INTO "QBEXSQL"."EMPNAMES" VALUES ('Qbex Pratap');
INSERT INTO "QBEXSQL"."EMPNAMES" VALUES ('Chand');
INSERT INTO "QBEXSQL"."EMPNAMES" VALUES ('Pavan');
INSERT INTO "QBEXSQL"."EMPNAMES" VALUES ('Ganga');
INSERT INTO "QBEXSQL"."EMPNAMES" VALUES ('Raja');
CREATE COLUMN TABLE "QBEXSQL"."EMP_IDS"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
INSERT INTO "QBEXSQL"."EMP_IDS"
( SELECT "QBEXSQL"."BillingDocNumbers".NEXTVAL, "EMPANMES" FROM "QBEXSQL"."EMPNAMES");
-- Unload Priority
-------------------
-- 0 --- 9
CREATE COLUMN TABLE "QBEXSQL"."EMPUNLOAD"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
) uNload priority 0;
---------------
CREATE COLUMN TABLE "QBEXSQL"."EMPtab4ALTER"
(
"EMPID" INT ,
"EMPNAME" VARCHAR(20),
"DEPTNO" VARCHAR(5)
);
-- Add New Column:
------------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
ADD ("DEPTNAME" VARCHAR(20));
-- Drop Column
---------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
DROP ( "DEPTNAME" );
-- Alter Column Definition
--------------------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
ALTER ( "DEPTNO" VARCHAR(10));
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
ALTER ( "EMPID" BIGINT);
-- Add Constraint
-----------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
ADD CONSTRAINT PK PRIMARY KEY ("EMPID");
-- Drop Primary key
--------------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
DROP PRIMARY KEY;
-- Preload Clause
-----------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
PRELOAD ("EMPNAME", "DEPTNO");
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
PRELOAD ALL;
-- Alter Table Type
--------------------
ALTER TABLE "QBEXSQL"."EMPtab4ALTER"
ALTER TYPE ROW;
-- Add Partition
-----------------
ALTER TABLE "QBEXSQL"."RANGEPARTMONTH"
ADD PARTITION VALUE = '2015-08' ;
-- Drop Partition:
-------------------
ALTER TABLE "QBEXSQL"."RANGEPARTMONTH"
DROP PARTITION VALUE = '2015-08' ;
-- Auto Merge
-------------
ALTER TABLE "QBEXSQL"."RANGEPARTMONTH"
DISABLE AUTOMERGE ;
ALTER TABLE "QBEXSQL"."RANGEPARTMONTH"
ENABLE AUTOMERGE ;
-- Unload Priority
------------------
ALTER TABLE "QBEXSQL"."RANGEPARTMONTH"
UNLOAD PRIORITY 6 ;
-- Auto Merge
-- Merge Dog
CREATE COLUMN TABLE "QBEXSQL"."EMPMERGE"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
) NO AUTO MERGE;
-- Create DB Table:
-------------------
--1. Create Simple COLUMN Table
CREATE COLUMN TABLE "QBEXSQL"."EMP"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
--2. Create Simple ROW Table
CREATE ROW TABLE "QBEXSQL"."EMPROW"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
-- Comments:
------------
CREATE COLUMN TABLE "QBEXSQL"."EMPCOMMENTS"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
COMMENT ON TABLE "QBEXSQL"."EMPCOMMENTS" IS 'Employee Details Table';
COMMENT ON COLUMN "QBEXSQL"."EMPCOMMENTS"."EMPID" IS 'Employee Number';
COMMENT ON COLUMN "QBEXSQL"."EMPCOMMENTS"."EMPNAME" IS 'Employee Name';
<NotepadPlus>
<Session activeView="0">
<mainView activeIndex="10">
<File firstVisibleLine="0" xOffset="0" scrollWidth="460" startPos="47" endPos="0" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Schema.txt" backupFilePath="C:\Users\Pratap\AppData\Roaming\Notepad++\backup\Schema.txt@2015-07-06_113507" originalFileLastModifTimestamp="1436162532" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="610" startPos="179" endPos="77" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Basic Column and Row Store Tables.txt" backupFilePath="" originalFileLastModifTimestamp="1436163085" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="610" startPos="249" endPos="191" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Global Temporary Column Table.txt" backupFilePath="" originalFileLastModifTimestamp="1436163102" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="660" startPos="513" endPos="513" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Local Temporary Table.txt" backupFilePath="" originalFileLastModifTimestamp="1436163603" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="2081" startPos="178" endPos="178" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Logging.txt" backupFilePath="" originalFileLastModifTimestamp="1436164982" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="400" startPos="31" endPos="152" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Auto Merge.txt" backupFilePath="C:\Users\Pratap\AppData\Roaming\Notepad++\backup\Auto Merge.txt@2015-07-06_121440" originalFileLastModifTimestamp="1436165042" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="410" startPos="180" endPos="180" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Unload Pripority.txt" backupFilePath="" originalFileLastModifTimestamp="1436165193" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="430" startPos="453" endPos="453" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Hash Partitioning.txt" backupFilePath="" originalFileLastModifTimestamp="1436165517" />
<File firstVisibleLine="33" xOffset="0" scrollWidth="500" startPos="1112" endPos="1564" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Range Partitioning.txt" backupFilePath="" originalFileLastModifTimestamp="1436167539" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="460" startPos="34" endPos="34" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\RoundRobin Partitioning.txt" backupFilePath="" originalFileLastModifTimestamp="1436167928" />
<File firstVisibleLine="0" xOffset="0" scrollWidth="870" startPos="0" endPos="0" selMode="0" lang="Normal Text" encoding="-1" filename="C:\Pratap Walmart\Open SAP\Hana Class Decks\Practice Files\Hana Class Files\SQL Notepad Files\Copy DB Table.txt" backupFilePath="" originalFileLastModifTimestamp="1436169048" />
</mainView>
<subView activeIndex="0" />
</Session>
</NotepadPlus>
-- Create Table with Like or Subquery:
---------------------------------------
-- Base Table:
----------------
CREATE COLUMN TABLE "QBEXSQL"."EMPBASE"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
insert into "QBEXSQL"."EMPBASE" values (1,'Pratap');
insert into "QBEXSQL"."EMPBASE" values (2,'Pratap');
insert into "QBEXSQL"."EMPBASE" values (3,'Pratap');
CREATE COLUMN TABLE "QBEXSQL"."EMPWITHDATA" LIKE "QBEXSQL"."EMPBASE" WITH DATA;
CREATE COLUMN TABLE "QBEXSQL"."EMPWITHNODATA" LIKE "QBEXSQL"."EMPBASE" WITH NO DATA;
CREATE COLUMN TABLE "QBEXSQL"."EMPWITHDATASUB" AS
(
SELECT * FROM "QBEXSQL"."EMPBASE"
);
CREATE COLUMN TABLE "QBEXSQL"."EMPWITHNODATASUB" AS
(
SELECT * FROM "QBEXSQL"."EMPBASE"
) WITH NO DATA;
-- Triggers [max 1024 on one DB table]
--------------------------------------
CREATE COLUMN TABLE "QBEXSQL"."AUDIT_TABLE"
(
"USERNAME" VARCHAR(20) NOT NULL,
"TIMING" TIMESTAMP NOT NULL,
PRIMARY KEY ("USERNAME","TIMING")
);
CREATE COLUMN TABLE "QBEXSQL"."EMPTRIGGER"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
CREATE TRIGGER "QBEXSQL"."InsertLogs"
AFTER INSERT ON "QBEXSQL"."EMPTRIGGER" FOR EACH ROW
BEGIN
INSERT INTO "QBEXSQL"."AUDIT_TABLE" VALUES (CURRENT_USER,CURRENT_TIMESTAMP);
END;
INSERT INTO "QBEXSQL"."EMPTRIGGER" VALUES (1,'Pratap');
INSERT INTO "QBEXSQL"."EMPTRIGGER" VALUES (2,'Pratap');
INSERT INTO "QBEXSQL"."EMPTRIGGER" VALUES (3,'Pratap');
INSERT INTO "QBEXSQL"."EMPTRIGGER" VALUES (4,'Pratap');
SELECT * FROM "QBEXSQL"."AUDIT_TABLE";
-- Create & Drop Extended Storage:
----------------------------------
The CREATE EXTENDED STORAGE statement defines an external Sybase IQ database connection for the SAP HANA database.
The Sybase IQ database will automatically be used to transparently archive and query data stored in extended tables.
Only database users having the system privilege CREATE SOURCE or DATA ADMIN are allowed to create extended storage.
--3. Create Global temporary Table
-----------------------------------
CREATE GLOBAL TEMPORARY COLUMN TABLE "QBEXSQL"."EMPTMPCOLUMN"
(
"EMPID" INT NOT NULL,
"EMPNAME" VARCHAR(20)
);
INSERT INTO "QBEXSQL"."EMPTMPCOLUMN"
VALUES (1,'Pratap');
INSERT INTO "QBEXSQL"."EMPTMPCOLUMN"
VALUES (2,'Pratap');
INSERT INTO "QBEXSQL"."EMPTMPCOLUMN"
VALUES (3,'Pratap');
INSERT INTO "QBEXSQL"."EMPTMPCOLUMN"
VALUES (4,'Pratap');
select * from "QBEXSQL"."EMPTMPCOLUMN";
-- Table Partitioning
----------------------
-- Hash, Range, Round Robin
-- Hash
CREATE COLUMN TABLE "QBEXSQL"."EMPHASH"
(
"EMPID" INT PRIMARY KEY,
"EMPNAME" VARCHAR(20)
) PARTITION BY HASH ("EMPID") PARTITIONS 4;
INSERT INTO "QBEXSQL"."EMPHASH"
VALUES (1,'Pratap');
INSERT INTO "QBEXSQL"."EMPHASH"
VALUES (2,'Pratap');
INSERT INTO "QBEXSQL"."EMPHASH"
VALUES (3,'Pratap');
INSERT INTO "QBEXSQL"."EMPHASH"
VALUES (4,'Pratap');
-- Create Index
----------------
-- The CREATE INDEX statement creates an index on a table with the selected column(s).
-- When column data types are character string types, binary string types, decimal types,
-- or when the constraint is a composite key, or a non-unique constraint, the default index type is CPBTREE.
-- In other cases BTREE will be used. If neither BTREE nor CPBTREE keyword is specified,
-- then the SAP HANA database chooses the appropriate index type.
---------------------------------------------------------------------------------------------------------------------------------
--INDEXES : Indexes currently defined on tables.
--INDEX_COLUMNS : Index column information.
CREATE COLUMN TABLE "QBEXSQL"."EMPSIMPLEIND"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
CREATE INDEX "MYSIMPLENDEX" ON "QBEXSQL"."EMPSIMPLEIND" ("EMPNAME");
CREATE COLUMN TABLE "QBEXSQL"."EMPIND"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
CREATE UNIQUE CPBTREE INDEX "MYFIRSTINDEX" ON "QBEXSQL"."EMPIND" ("EMPNAME");
CREATE COLUMN TABLE "QBEXSQL"."EMPINDEX"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
CREATE UNIQUE INDEX "MYSECONDINDEX" ON "QBEXSQL"."EMPINDEX" ("EMPNAME");
--3. Create local temporary Table
-----------------------------------
CREATE LOCAL TEMPORARY COLUMN TABLE "QBEXSQL"."#EMPTMPLOCALCOLUMN"
(
"EMPID" INT,
"EMPNAME" VARCHAR(20)
);
INSERT INTO "QBEXSQL"."#EMPTMPLOCALCOLUMN"
VALUES (1,'Pratap');
INSERT INTO "QBEXSQL"."#EMPTMPLOCALCOLUMN"
VALUES (2,'Pratap');
INSERT INTO "QBEXSQL"."#EMPTMPLOCALCOLUMN"
VALUES (3,'Pratap');
INSERT INTO "QBEXSQL"."#EMPTMPLOCALCOLUMN"
VALUES (4,'Pratap');
select * from "QBEXSQL"."#EMPTMPLOCALCOLUMN";
-- Logging and NO Logging
-------------------------
-- Default Logging
-- no logging
--Specifies the retention time in seconds of the column table created by NO LOGGING.
--After the specified retention period has elapsed, the table will be dropped if used physical memory of the host is above 80%.
CREATE COLUMN TABLE "QBEXSQL"."EMPNOLOGGINGRET"
(
"EMPID" INT,
"EMPNAME" VARCHAR(20)
) NO LOGGING RETENTION 300;
INSERT INTO "QBEXSQL"."EMPNOLOGGINGRET"
VALUES (1,'Pratap');
INSERT INTO "QBEXSQL"."EMPNOLOGGINGRET"
VALUES (2,'Pratap');
INSERT INTO "QBEXSQL"."EMPNOLOGGINGRET"
VALUES (3,'Pratap');
INSERT INTO "QBEXSQL"."EMPNOLOGGINGRET"
VALUES (4,'Pratap');
select * from "QBEXSQL"."EMPNOLOGGINGRET";
-- Primary Key & Foreign Key:
-----------------------------
CREATE COLUMN TABLE "QBEXSQL"."DEPARTMENT"
(
"DEPTNO" INT NOT NULL PRIMARY KEY,
"DEPTNAME" VARCHAR(20)
);
CREATE COLUMN TABLE "QBEXSQL"."EMPLOYEES"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20),
"DEPTNO" INT,
FOREIGN KEY ("DEPTNO") REFERENCES "QBEXSQL"."DEPARTMENT"("DEPTNO") ON DELETE CASCADE
);
CREATE COLUMN TABLE "QBEXSQL"."EMPLOYEES"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20),
"DEPTNO" INT,
FOREIGN KEY ("DEPTNO") REFERENCES "QBEXSQL"."DEPARTMENT"("DEPTNO") ON DELETE CASCADE
);
-- Range Partitioning
---------------------
CREATE COLUMN TABLE "QBEXSQL"."RANGEPART"
(
"ORDERID" INT NOT NULL,
"ORDERDATE" DATE NOT NULL,
"NETSALES" DECIMAL(10,2),
PRIMARY KEY ("ORDERID","ORDERDATE")
) PARTITION BY RANGE ("ORDERDATE")
(
PARTITION '2015-01-01' <= VALUES < '2015-06-01',
PARTITION VALUE = '2015-07-15', PARTITION OTHERS
);
INSERT INTO "QBEXSQL"."RANGEPART"
VALUES (1,'2015-01-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPART"
VALUES (2,'2015-03-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPART"
VALUES (3,'2015-07-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPART"
VALUES (4,'2016-07-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPART"
VALUES (5,'2016-07-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPART"
VALUES (6,'2016-07-15',15000.00);
-- Month
CREATE COLUMN TABLE "QBEXSQL"."RANGEPARTMONTH"
(
"ORDERID" INT NOT NULL,
"ORDERDATE" DATE NOT NULL,
"NETSALES" DECIMAL(10,2),
PRIMARY KEY ("ORDERID","ORDERDATE")
) PARTITION BY RANGE (MONTH("ORDERDATE"))
(
PARTITION '2015-01' <= VALUES < '2015-06',
PARTITION VALUE = '2015-07', PARTITION OTHERS
);
INSERT INTO "QBEXSQL"."RANGEPARTMONTH"
VALUES (1,'2015-01-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPARTMONTH"
VALUES (2,'2015-03-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPARTMONTH"
VALUES (3,'2015-07-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPARTMONTH"
VALUES (4,'2016-07-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPARTMONTH"
VALUES (5,'2016-07-15',15000.00);
INSERT INTO "QBEXSQL"."RANGEPARTMONTH"
VALUES (6,'2016-07-15',15000.00);
-- Others:
-----------
1. Rename Column:
-----------------
rename COLUMN "QBEXSQL"."EMPtab4ALTER"."EMPID" TO "EMPNO";
2. Rename Table:
-----------------
RENAME TABLE "QBEXSQL"."EMPtab4ALTER1" TO "FORALTERS";
3. Rename Index:
-----------------
RENAME INDEX <OLDNAME> TO <NEWNAME>;
-- Round Robin:
---------------
-- No Primary key
CREATE COLUMN TABLE "QBEXSQL"."ROUNDROBIN"
(
"ORDERID" INT ,
"ORDERDATE" DATE,
"NETSALES" DECIMAL(10,2)
) PARTITION BY ROUNDROBIN PARTITIONS 4;
INSERT INTO "QBEXSQL"."ROUNDROBIN"
VALUES (1,'2015-01-15',15000.00);
INSERT INTO "QBEXSQL"."ROUNDROBIN"
VALUES (2,'2015-03-15',15000.00);
INSERT INTO "QBEXSQL"."ROUNDROBIN"
VALUES (3,'2015-07-15',15000.00);
INSERT INTO "QBEXSQL"."ROUNDROBIN"
VALUES (4,'2016-07-15',15000.00);
INSERT INTO "QBEXSQL"."ROUNDROBIN"
VALUES (5,'2016-07-15',15000.00);
INSERT INTO "QBEXSQL"."ROUNDROBIN"
VALUES (6,'2016-07-15',15000.00);
-- Schema
----------
Create Schema "QBEXSQL";
Create Schema "QBEXSQL" OWNED BY "<USERNAME>"
-- Create Sequence
------------------
CREATE SEQUENCE "QBEXSQL"."BillingDocNumbers"
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE
CACHE 5
RESET BY
( SELECT MAX("EMPID") FROM "QBEXSQL"."EMPSIMPLEIND");
CREATE TABLE "QBEXSQL"."EMPNAMES"
(
"EMPANMES" VARCHAR(20)
);
INSERT INTO "QBEXSQL"."EMPNAMES" VALUES ('Pratap');
INSERT INTO "QBEXSQL"."EMPNAMES" VALUES ('Qbex Pratap');
INSERT INTO "QBEXSQL"."EMPNAMES" VALUES ('Chand');
INSERT INTO "QBEXSQL"."EMPNAMES" VALUES ('Pavan');
INSERT INTO "QBEXSQL"."EMPNAMES" VALUES ('Ganga');
INSERT INTO "QBEXSQL"."EMPNAMES" VALUES ('Raja');
CREATE COLUMN TABLE "QBEXSQL"."EMP_IDS"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
);
INSERT INTO "QBEXSQL"."EMP_IDS"
( SELECT "QBEXSQL"."BillingDocNumbers".NEXTVAL, "EMPANMES" FROM "QBEXSQL"."EMPNAMES");
-- Unload Priority
-------------------
-- 0 --- 9
CREATE COLUMN TABLE "QBEXSQL"."EMPUNLOAD"
(
"EMPID" INT NOT NULL PRIMARY KEY,
"EMPNAME" VARCHAR(20)
) uNload priority 0;
Comments
Post a Comment