Advance Table

SQL> CREATE TABLE SampDept

2 AS

3 SELECT * FROM Dept

4 /

Table created.

SQL> DESC Dept

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> DESC SampDept

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> SELECT * FROM SampDept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> CREATE TABLE SampDept1(

2 DeptID,

3 DeptName,

4 Place

5 )

6 AS

7 SELECT * FROM Dept

8 /

Table created.

SQL> DESC SampDept1

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTID NOT NULL NUMBER(2)

DEPTNAME VARCHAR2(14)

PLACE VARCHAR2(13)

SQL> SELECT * FROM SampDept1;

DEPTID DEPTNAME PLACE

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> CREATE TABLE SampDept2

2 AS

3 SELECT Deptno, Dname

4 FROM Dept

5 /

Table created.

SQL> DESC SampDept2

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

SQL> CREATE TABLE SampDept3

2 AS

3 SELECT * FROM Dept

4 WHERE 1 = 2

5 /

Table created.

SQL> DESC SampDept3

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> SELECT * FROM SampDept3;

no rows selected

SQL> CREATE TABLE SampleTablePart

2 (

3 SampID NUMBER(4)

4 CONSTRAINT SampIDPK PRIMARY KEY,

5 SampName VARCHAR2(20)

6 CONSTRAINT SampNameNN NOT NULL

7 CONSTRAINT SampNameCHK

8 CHECK(SampName = UPPER(SampName)),

9 SampDate DATE DEFAULT SYSDATE

10 CONSTRAINT SampdateNN NOT NULL,

11 SampDesc VARCHAR2(4000)

12 DEFAULT 'NOT PROVIDED'

13 )

14 PARTITION BY RANGE(SampID)

15 (

16 PARTITION SampIDPart1

17 VALUES LESS THAN (500),

18 PARTITION SampIDPart2

19 VALUES LESS THAN (1000),

20 PARTITION SampIDPart3

21 VALUES LESS THAN (2000),

22 PARTITION SampIDPart4

23 VALUES LESS THAN (3000),

24 PARTITION SampIDPart5

25 VALUES LESS THAN (4000),

26 PARTITION SampIDPart6

27 VALUES LESS THAN (MAXVALUE)

28 );

Table created.

SQL> cl scr

CREATE TABLE EmpTableHash

(

EmpNo NUMBER(6)

CONSTRAINT EmpNOPK PRIMARY KEY,

EName VARCHAR2(20)

DEFAULT 'NOT PROVIDED'

CONSTRAINT ENameNN NOT NULL

CONSTRAINT ENameCHK CHECK(EName = UPPER(EName)),

Job VARCHAR2(30)

DEFAULT 'NOT MENTIONED'

CONSTRAINT SampdateNN NOT NULL

CONSTRAINT JobCHKUpper CHECK(Job = UPPER(Job))

CONSTRAINT JobCHKList CHECK(Job IN('PRESIDENT','ANALYST','MANAGER','SALESMAN','CLERK')),

DeptNo NUMBER(2)

CONSTRAINT DeptnoCHKRange

CHECK(DeptNo BETWEEN 10 AND 99)

CONSTRAINT DeptnoCHKList

CHECK(DeptNo IN(10, 20, 30, 40, 50, 60, 70, 80, 90)),

MGRID NUMBER(6)

CONSTRAINT MGRIDFKSelf

REFERENCES EmpTableHash(EmpNo)

ON DELETE SET NULL,

HireDate DATE

DEFAULT SYSDATE

CONSTRAINT HireDateNN NOT NULL,

Sal NUMBER(8,2)

DEFAULT 10000

CONSTRAINT SalNN NOT NULL

CONSTRAINT SalChkRange CHECK(Sal BETWEEN 10000 AND 150000),

Comm NUMBER(7,2)

DEFAULT NULL

CONSTRAINT CommNN NOT NULL

CONSTRAINT CommChkRange CHECK(Comm BETWEEN 0 AND 25000),

CONSTRAINT DeptNOFKHASH

FOREIGN KEY(DeptNo)

REFERENCES Dept(Deptno)

ON DELETE CASCADE,

CONSTRAINT TotalSalaryCHK

CHECK(Sal + Comm <= 175000)

)

PARTITION BY HASH(DeptNO)

PARTITIONS 9

CREATE TABLE EmpSampleList1

(

EmpNo NUMBER(6)

CONSTRAINT EmpNOPK1 PRIMARY KEY,

EName VARCHAR2(20)

DEFAULT 'NOT PROVIDED'

CONSTRAINT ENameNN1 NOT NULL

CONSTRAINT ENameCHK1

CHECK(EName = UPPER(EName)),

Job VARCHAR2(30)

DEFAULT 'NOT MENTIONED'

CONSTRAINT SampdateNN1 NOT NULL

CONSTRAINT JobCHKUpper1

CHECK(Job = UPPER(Job))

CONSTRAINT JobCHKList1 CHECK

CONSTRAINT JobCHKList CHECK(Job IN('PRESIDENT','ANALYST','MANAGER','SALESMAN','CLERK')),

DeptNo NUMBER(2)

CONSTRAINT DeptnoCHKRange1

CHECK(DeptNo BETWEEN 10 AND 99)

CONSTRAINT DeptnoCHKList1

CHECK(DeptNo IN(10,20,30,40,50,60,70,80,90)),

MGRID NUMBER(6)

CONSTRAINT MGRIDFKSelf1

REFERENCES EmpSampleList(EmpNo)

ON DELETE SET NULL,

HireDate DATE

DEFAULT SYSDATE

CONSTRAINT HireDateNN1 NOT NULL,

Sal NUMBER(8,2)

DEFAULT 10000

CONSTRAINT SalNN1 NOT NULL

CONSTRAINT SalChkRange1

CHECK(Sal BETWEEN 10000 AND 150000),

Comm NUMBER(7,2)

DEFAULT NULL

CONSTRAINT CommNN1 NOT NULL

CONSTRAINT CommChkRange1

CHECK(Comm BETWEEN 0 AND 25000),

CONSTRAINT DeptNOFKHASH1

FOREIGN KEY(DeptNo)

REFERENCES Dept(Deptno)

ON DELETE CASCADE,

CONSTRAINT TotalSalaryCHK1

CHECK(Sal + Comm <= 175000)

)

PARTITION BY LIST(Job)

(

PARTITION JPresident

VALUES('PRESIDENT'),

PARTITION JAnalyst

VALUES('ANALYST'),

PARTITION JManager

VALUES('MANAGER'),

PARTITION JSalesman

VALUES('SALESMAN'),

PARTITION JClerk

VALUES('CLERK')

);

CREATE TABLE EmpSampleList

(

EmpNo NUMBER(6)

CONSTRAINT EmpNOPK PRIMARY KEY,

EName VARCHAR2(20)

DEFAULT 'NOT PROVIDED'

CONSTRAINT ENameNN NOT NULL

CONSTRAINT ENameCHK

CHECK(EName = UPPER(EName)),

Job VARCHAR2(30)

DEFAULT 'NOT MENTIONED'

CONSTRAINT SampdateNN NOT NULL

CONSTRAINT JobCHKUpper

CHECK(Job = UPPER(Job))

CONSTRAINT JobCHKList CHECK

CONSTRAINT JobCHKList CHECK(Job IN('PRESIDENT','ANALYST','MANAGER','SALESMAN','CLERK')),

DeptNo NUMBER(2)

CONSTRAINT DeptnoCHKRange

CHECK(DeptNo BETWEEN 10 AND 99)

CONSTRAINT DeptnoCHKList

CHECK(DeptNo IN(10,20,30,40,50,60,70,80,90)),

MGRID NUMBER(6)

CONSTRAINT MGRIDFKSelf

REFERENCES EmpSampleList(EmpNo)

ON DELETE SET NULL,

HireDate DATE

DEFAULT SYSDATE

CONSTRAINT HireDateNN NOT NULL,

Sal NUMBER(8, 2)

DEFAULT 10000

CONSTRAINT SalNN NOT NULL

CONSTRAINT SalChkRange

CHECK(Sal BETWEEN 10000 AND 150000),

Comm NUMBER(7, 2)

DEFAULT NULL

CONSTRAINT CommNN NOT NULL

CONSTRAINT CommChkRange

CHECK(Comm BETWEEN 0 AND 25000),

CONSTRAINT DeptNOFKHASH

FOREIGN KEY(DeptNo)

REFERENCES Dept(Deptno)

ON DELETE CASCADE,

CONSTRAINT TotalSalaryCHK

CHECK(Sal + Comm <= 175000)

)

PARTITION BY LIST(Job)

(

PARTITION JPartList1

VALUES(

'PRESIDENT',

'ANALYST'

),

PARTITION JPartList2

VALUES(

'MANAGER',

'SALESMAN',

'CLERK'

)

);

CREATE TABLE EmpSampleSubPart

(

EmpNo NUMBER(6)

CONSTRAINT EmpNOPK2 PRIMARY KEY,

EName VARCHAR2(20)

DEFAULT 'NOT PROVIDED'

CONSTRAINT ENameNN2 NOT NULL

CONSTRAINT ENameCHK2

CHECK(EName = UPPER(EName)),

Job VARCHAR2(30)

DEFAULT 'NOT MENTIONED'

CONSTRAINT SampdateNN2 NOT NULL

CONSTRAINT JobCHKUpper2

CHECK(Job = UPPER(Job))

CONSTRAINT JobCHKList2

CONSTRAINT JobCHKList CHECK(Job IN('PRESIDENT','ANALYST','MANAGER','SALESMAN','CLERK')),

DeptNo NUMBER(2) CONSTRAINT

DeptnoCHKRange2 CHECK(DeptNo BETWEEN 10 AND 99)

CONSTRAINT DeptnoCHKList2

CHECK(DeptNo IN(10,20,30,40,50,60,70,80,90)),

MGRID NUMBER(6)

CONSTRAINT MGRIDFKSelf2

REFERENCES EmpSampleSubPart(EmpNo)

ON DELETE SET NULL,

HireDate DATE

DEFAULT SYSDATE

CONSTRAINT HireDateNN2 NOT NULL,

Sal NUMBER(8, 2)

DEFAULT 10000

CONSTRAINT SalNN2 NOT NULL

CONSTRAINT SalChkRange2

CHECK(Sal BETWEEN 10000 AND 150000),

Comm NUMBER(7, 2)

DEFAULT NULL

CONSTRAINT CommNN2 NOT NULL

CONSTRAINT CommChkRange2

CHECK(Comm BETWEEN 0 AND 25000),

CONSTRAINT DeptNOFKSubPart2

FOREIGN KEY(DeptNo)

REFERENCES Dept(Deptno)

ON DELETE CASCADE,

CONSTRAINT TotalSalaryCHK2

CHECK(Sal + Comm <= 175000)

)

PARTITION BY RANGE(Ename)

SUBPARTITION BY HASH(Job)

SUBPARTITIONS 5

(

PARTITION NameP1

VALUES LESS THAN('G'),

PARTITION NAMEP2

VALUES LESS THAN('N'),

PARTITION NAMEP3

VALUES LESS THAN('T'),

PARTITION NAMEP4

VALUES LESS THAN(MAXVALUE)

)

/

ALTER TABLE SampleTablePart

SPLIT PARTITION SampIDPart6

AT(5000)

INTO(

PARTITION SampIDPart6,

PARTITION SampIDPart7

)

/

ALTER TABLE EmpSampleList

SPLIT PARTITION JPartList2

VALUES(

'MANAGER',

'SALESMAN'

)

INTO(

PARTITION MANAGER,

PARTITION SALESMAN

)

/

ALTER TABLE EmpSampleList

MERGE

PARTITIONS

MANAGER,

SALESMAN

INTO

PARTITION

JPARTLIST2

/

ALTER TABLE SampleTablePart

MERGE

PARTITIONS

SampIDPart6,

SampIDPart7

INTO

PARTITION

SampIDPart7

/