Data Define Language

Data Definition Language (DDL) statements are used to define the database structure or schema. 

Some examples:

    CREATE - to create objects in the database.
    ALTER  - alters the structure of the database.
        ADD
        RENAME
        MODIFY
        DROP
    DROP - delete objects from the database
    TRUNCATE - remove all records from a table, 
               including all spaces allocated for the records are removed.
    COMMENT - add comments to the data dictionary.
    RENAME - rename an object.
   

Creating Table

--------------------------------------------------------------------------
Data Types     Size            Default Size    Explanation
--------------------------------------------------------------------------
NUMBER(P,S)     P:   1 to 38              we can store number between 0-9
                S: -84 to 127
VARCHAR2        4000 Bytes                we can store 0-9, a-z, A-Z and special characters
CHAR            2000 Bytes          1           
DATE                                7     Used to store Date
TIMESTAMP                                 Includes year, month, day, hour, minute, and seconds.
LONG            2 GB                      Only one long column is allowed in a table.
                                          We can't use this column in ORDER BY clause.
CLOB            4 GB                      Character Large Object
BLOB                                      Binary Larger Object
--------------------------------------------------------------------------
   
  CREATE
   ALTER  :  ADD   RENAME   MODIFY   DROP   RENAME
  RENAME
    DROP
TRUNCATE


--creating table

CREATE TABLE friends_details_tb
    (
    Name               VARCHAR2(30) ,
    Phone              NUMBER(10)   ,
    Gender             CHAR         ,
    dob                DATE         ,
    other_details      LONG         ,
    make_dtm           TIMESTAMP
    );
   
DESC friends_details_tb;

   -----------------------------
   NAME           TYPE
   -----------------------------
   NAME           VARCHAR2(30)
   PHONE          NUMBER(10)
   GENDER         CHAR(1)
   DOB            DATE
   OTHER_DETAILS  LONG
   MAKE_DTM       TIMESTAMP(6)
   -----------------------------
   
--maximum you can keep 255 columns in a table



ALTER  :  Colum level operations
          ADD   RENAME   MODIFY   DROP  

ADD    :  Used to add a column after the table hase been created


    ALTER TABLE friends_details_tb
    ADD   email VARCHAR2(30);

    DESC friends_details_tb;

   -----------------------------
   NAME           TYPE
   -----------------------------
   NAME           VARCHAR2(30)
   PHONE          NUMBER(10)
   GENDER         CHAR(1)
   DOB            DATE
   OTHER_DETAILS  LONG
   MAKE_DTM       TIMESTAMP(6)
   EMAIL          VARCHAR2(30)
   -----------------------------
  
--adding multiple columns by using single ALTER statement

SYNTAX

ALTER TABLE friends_details_tb
ADD   (
        column_1   Data_type(size),
        column_2   Data_type(size),
        column_3   Data_type(size),
        column_4   Data_type(size)
      );




RENAME   :  Used to Rename a column

    ALTER TABLE friends_details_tb
    RENAME COLUMN email TO email_id;

    DESC friends_details_tb;

   -----------------------------
   NAME             TYPE
   -----------------------------
   NAME             VARCHAR2(30)
   PHONE            NUMBER(10)
   GENDER           CHAR(1)
   DOB              DATE
   OTHER_DETAILS    LONG
   MAKE_DTM         TIMESTAMP(6)
   EMAIL_ID         VARCHAR2(30)
   -----------------------------



MODIFY  :  Used to change the data type or size of the data type.

    ALTER TABLE friends_details_tb
    MODIFY other_details CLOB;

    DESC friends_details_tb;

   -----------------------------
   NAME             TYPE
   -----------------------------
   NAME             VARCHAR2(30)
   PHONE            NUMBER(10)
   GENDER           CHAR(1)
   DOB              DATE
   OTHER_DETAILS    CLOB
   MAKE_DTM         TIMESTAMP(6)
   EMAIL_ID         VARCHAR2(30)
   -----------------------------

--Modifying  multiple columns at a time


    ALTER TABLE friends_details_tb
    MODIFY (            column_name_1  old_datatype(new_size),
            column_name_2  new_datatype(old_size),
            column_name_3  new_datatype(new_size)
            );


DROP  :  Used to drop the column after the table has been created.

    ALTER TABLE friends_details_tb
    DROP COLUMN other_details;

DESC friends_details_tb;

   -----------------------------
   NAME             TYPE
   -----------------------------
   NAME             VARCHAR2(30)
   PHONE            NUMBER(10)
   GENDER           CHAR(1)
   DOB              DATE
   MAKE_DTM         TIMESTAMP(6)
   EMAIL_ID         VARCHAR2(30)
   -----------------------------
  

RENAME  :  Used to Rename a particular table.

SYNTAX 

    RENAME old_table_name TO new_table_name;

    RENAME friends_details_tb TO fnd_det;

DESCRIBE fnd_det;

   -----------------------------
   NAME             TYPE
   -----------------------------
   NAME             VARCHAR2(30)
   PHONE            NUMBER(10)
   GENDER           CHAR(1)
   DOB              DATE
   MAKE_DTM         TIMESTAMP(6)
   EMAIL_ID         VARCHAR2(30)
   -----------------------------

TRUNCATE   :  Used to remove the entire content of the table (not a structure)

SYNTAX 

    TRUNCATE TABLE table_name;

    TRUNCATE TABLE fnd_det;




DROP  :  Used to drop the tabel (Data + stucture of the table get removed from the database )

    DROP TABLE fnd_det;
  
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*