·  SQL – Table

 

- Info:

  - Tables are used to store data.

  - Partitioned Tables are special case of tables.

  - Following SQLs can be used to find out how much MBs table and it's indexes take:

    - Show for each table. Doesn't work for IOT.

    - Show for each table. Works for IOT also.

    - Show for specified table. Doesn't work for IOT.

    - Show for specified table. Works for IOT also.

 

- Syntax – Create:

  - When creating table you must define column names and types.

    CREATE TABLE MYTABLE (

      AGE  NUMBER  (8),    

      NAME VARCHAR2(9),

      BORN DATE

    )

 

- Syntax – Create – From Subquery:

  - New table is created only using columns and records from SELECT statement.

    CREATE TABLE SELECTED_PEOPLE AS

      SELECT NAME, AGE

      FROM   PEOPLE

    WHERE  BORN > SYSDATE - 100

 

  - If you define column names for new table, their number ant type must match the number of columns in select.

    CREATE TABLE SELECTED_PEOPLE (PERSON_NAME, PERSON_AGE) AS

      SELECT NAME, AGE

      FROM   PEOPLE

    WHERE  BORN > SYSDATE - 100

 

- Syntax – Create – With Constraints At Column Level:

  - This example shows how to define constraints at column level.

    CREATE TABLE MYTABLE (

      COLUMN1 NUMBER  (8) NOT NULL,         -- NO NULL must be defined at column level.

      COLUMN2 DATE        NOT NULL UNIQUE,  -- Multiple contraints on single column.

      COLUMN3 VARCHAR2(5) PRIMARY KEY,      

      COLUMN4 NUMBER  (5) REFERENCES MYTABLE2(COLUMN2),      

      COLUMN5 NUMBER  (5) CHECK (COLUMN5>0)

    );

 

- Syntax – Create – With Constraints At Table Level:

  - This example shows how to define constraints at table level.

    CREATE TABLE MYTABLE (

      COLUMN1 NUMBER  (8) ,

      COLUMN2 DATE        ,        

      COLUMN3 VARCHAR2(5) ,      

      COLUMN4 NUMBER  (5) ,      

      COLUMN5 NUMBER  (8) UNIQUE,      

      COLUMN6 NUMBER  (8) UNIQUE,      

      UNIQUE     (COLUMN1),        

      UNIQUE     (COLUMN1, COLUMN2),-- Composite Unique Cconstraint can be defined only at table level      

      PRIMARY KEY(COLUMN3, COLUMN4),-- Composite Primary Key        can be defined only at table level

      FOREIGN KEY(COLUMN5, COLUMN6) REFERENCES MYTABLE2(COLUMN1, COLUMN2),-- These need to PRIMARY KEY

      CHECK      (COLUMN5>0)

    );

 

- Syntax – Create – With Named Constraints

  - Constraints can be given a name as an option.

    CREATE TABLE MYTABLE (

      COLUMN1 NUMBER  (8)

        CONSTRAINT COLUMN1_NOTNULL NOT NULL        

        CONSTRAINT COLUMN1_UNIQUE  UNIQUE, 

      COLUMN2 NUMBER  (8),

      COLUMN3 NUMBER  (8), 

      CONSTRAINT PK PRIMARY KEY (COLUMN2, COLUMN3)

  );

 

- Syntax – Alter – Column:

  - After table is created columns can be independantely modified, added, droped.

    ALTER TABLE MYTABLE ADD  (

      COLUMN2 VARCHAR2 (5),

      COLUMN2 VARCHAR2 (105) DEFAULT "0"

    )

    

    ALTER TABLE MYTABLE MODIFY (

      COLUMN2 VARCHAR2 (9),

      COLUMN3 VARCHAR2 (109) DEFAULT "0"

    );

    

    ALTER TABLE MYTABLE SET UNUSED (COLUMN2, COLUMN3);

    

    ALTER TABLE MYTABLE DROP COLUMN COLUMN2; -- You can drop one column at a time.

    

    ALTER TABLE MYTABLE DROP UNUSED COLUMNS;

 

- Syntax – Alter – Constraint:

  - After table is created named column constraints can be independantely droped, enabled or disabled.

    ALTER TABLE PEOPLE DROP    CONSTRAINT CONSTRAINT1;

    ALTER TABLE PEOPLE ENABLE  CONSTRAINT CONSTRAINT2;

    ALTER TABLE PEOPLE DISABLE CONSTRAINT CONSTRAINT3;

 

- Syntax – Delete:

  - To delete table use DROP statement.

    DROP TABLE MYTABLE;

 

- Syntax – Describe:

  - To display column names and their types use DESCRIBE statement.

    DESCRIBE MYTABLE;

 

- Syntax – Comment – Create:

  - Each table and column can have a comment.

    COMMENT ON TABLE  MYTABLE

    IS                'Test table.';

    

    COMMENT ON COLUMN MYTABLE.COLUMN1

    IS                'Test column.';

 

- Syntax – Comment – Delete:

  - Table and column comments are deleted simply by setting them to an empty string.

    COMMENT ON TABLE  MYTABLE    

    IS                '';

 

    COMMENT ON COLUMN MYTABLE    

    IS                '';

 

- Example:

    This real life example shows how to create single table accessable by two users.

    Table and index are created in different tablespaces STAR_DATA and STAR_IDX .

 

  - Login as STAR:

    CREATE TABLE STARS

      (ID           NUMBER NOT NULL,

      SRCNAME      VARCHAR2(30),    

       DESTNAME     VARCHAR2(30),

       DESTSURNAME  VARCHAR2(30),

       DESTTYPE     VARCHAR2(3),

       DESCRIPTION  VARCHAR2(600),

       DESTCONTACT  VARCHAR2(30),

       ACTIONDATE   DATE,

       RELATION     VARCHAR2(30),

       USERNAME     VARCHAR2(30),

       DATESUBMITED DATE DEFAULT SYSDATE

      )  

    TABLESPACE STAR_DATA LOGGING NOCACHE NOPARALLEL;

    

    CREATE INDEX STARS_ID_IDX ON STARS(ID) TABLESPACE STAR_IDX;

    

    CREATE SEQUENCE SEQ_STARS_ID

    START WITH   0

    INCREMENT BY 1

    MINVALUE     0

    CACHE     10

    NOCYCLE

    NOORDER

     

    GRANT SELECT, INSERT ON STARS        TO STAR_APP

    GRANT SELECT         ON SEQ_STARS_ID TO STAR_APP

 

  - Login as STAR_APP:

    CREATE SYNONYM STARS        FOR STAR.STARS;

    CREATE SYNONYM SEQ_STARS_ID FOR STAR.SEQ_STARS_ID;