Social Icons

Creating Tables








Creation Type SQL
Creating a Table with a Unique Primary Index CREATE TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
UNIQUE PRIMARY INDEX (EMP_NO);
Creating a Table with a Non-Unique Primary Index CREATE TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
PRIMARY INDEX(EMP_NO);
Creating a Table without entering a Primary Index CREATE TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
Creating a Table with NO Primary Index CREATE TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
NO PRIMARY INDEX;
Creating a Set Table CREATE SET TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
PRIMARY INDEX(EMP_NO);
Creating a Multiset Table CREATE MULTISET TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
PRIMARY INDEX(EMP_NO);
Creating a Set Table with a Unique Primary Index CREATE SET TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
UNIQUE PRIMARY INDEX(EMP_NO);
Creating a Set Table with a Unique Secondary Index CREATE SET TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
PRIMARY INDEX(EMP_NO)
UNIQUE INDEX(DEPT_NO);
Creating a Table with an UPI and USI CREATE SET TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
UNIQUE PRIMARY INDEX(EMP_NO)
UNIQUE INDEX(DEPT_NO);
Creating a Table with a Multicolumn Primary Index CREATE TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
PRIMARY INDEX(EMP_NO,DEPT_NO,SALARY);
Creating a Unique Secondary Index (USI) after a table is created CREATE UNIQUE INDEX IDXNAME(EMP_NO) ON EMP_TABLE ;
Creating a Value-Ordered NUSI CREATE INDEX(DEPT_NO) ORDER BY VALUES ON EMP_TABLE ;
Making an exact copy a Table with data CREATE TABLE EMP_TABLE_BACKUP AS EMP_TABLE WITH DATA;
Making an exact copy a Table with out data CREATE TABLE EMP_TABLE_BACKUP AS EMP_TABLE WITH NO DATA;
Copying a Table CREATE TABLE EMP_TABLE_BACKUP AS (SELECT * FROM EMP_TABLE ) WITH DATA;
Copying only specific columns of a table CREATE TABLE EMP_TABLE_BACKUP AS
(SELECT
EMP_NO
,DEPT_NO
,SALARY
FROM EMP_TABLE )
WITH DATA;
Copying aTable and Keeping the Statistics CREATE TABLE EMP_TABLE_BACKUP AS EMP_TABLE WITH DATA AND STATS;
Copying a Table without data but with Statistics CREATE TABLE EMP_TABLE_BACKUP AS EMP_TABLE WITH NO DATA AND STATS ;
Creating a Table with Fallback CREATE TABLE EMP_TABLE, FALLBACK
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with No Fallback CREATE TABLE EMP_TABLE, NO FALLBACK
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with a Before Journal CREATE TABLE EMP_TABLE
,NO FALLBACK
,BEFORE JOURNAL
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with a Dual Before Journal CREATE TABLE EMP_TABLE
,NO FALLBACK
,DUAL JOURNAL
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with an After Journal CREATE TABLE EMP_TABLE
,NO FALLBACK
,AFTER JOURNAL
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with a Dual After Journal CREATE TABLE EMP_TABLE
,NO FALLBACK
,DUAL AFTER JOURNAL
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with a Journal Keyword Alone CREATE TABLE EMP_TABLE
,NO FALLBACK
,JOURNAL
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with Customization of the Data Block Size CREATE TABLE EMP_TABLE
,FALLBACK
,DATABLOCKSIZE= 21248 BYTES
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with Customization with FREESPACE Percent CREATE TABLE EMP_TABLE
,FALLBACK
,DATABLOCKSIZE= 21248 BYTES
,FREESPACE = 20 PERCENT
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Columnar Table CREATE TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
NO PRIMARY INDEX
PARTITION BY COLUMN;
Creating a Columnar Table with Multi-Column Containers CREATE TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
NO PRIMARY INDEX
PARTITION BY COLUMN (
EMP_NO,
DEPT_NO,
(FIRST_NAME,LAST_NAMESALARY));
Creating a Columnar Table with a Row Hybrid CREATE TABLE EMP_TABLE
(
EMP_NO INTEGER
,DEPT_NO INTEGER
,FIRST_NAME VARCHAR(20)
,LAST_NAME CHAR(20)
,SALARY DECIMAL(10,2)
,ADDRESS VARCHAR(100)
)
NO PRIMARY INDEX
PARTITION BY COLUMN (EMP_NO NO AUTO COMPRESS,DEPT_NO
,ROW (FIRST_NAME,LAST_NAMESALARY)NO AUTO COMPRESS);
Creating a Columnar Table with both Row and Column Partitions CREATE TABLE ORDER_TABLE
(
ORDER_NO INTEGER
,CUSTMER_NO INTEGER
,ORDER_DATE DATE
,ORDER_TOTAL DECIMAL(10,2)
)
NO PRIMARY INDEX
PARTITION BY (COLUMN,
RANGE_N (ORDER_DATE BETWEEN DATE '2015-01-01' AND '2015-06-30' EACH INTERVAL '1' MONTH)) ;



Labels: ,