PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

Object Oriented Database Management System

Advance Tables:

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 
/ 

Go Back