SQL DDL Operations

USING CREATE

CREATE TABLE SYNTAX

Create table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen);

Ex:

SQL> create table student (no number (2), name varchar (10), marks number (3));

USING ALTER

This can be used to add or remove columns and to modify the precision of the datatype.

a) ADDING COLUMN

    Syntax:

        alter table <table_name> add <col datatype>;

    Ex:

        SQL> alter table student add sdob date;

b) REMOVING COLUMN

    Syntax:

        alter table <table_name> drop <col datatype>;

    Ex:

         SQL> alter table student drop column sdob;

c) INCREASING OR DECREASING PRECISION OF A COLUMN

    Syntax:

          alter table <table_name> modify <col datatype>;

    Ex:

          SQL> alter table student modify marks number(5);

          * To decrease precision the column should be empty.

d) MAKING COLUMN UNUSED

    Syntax:

         alter table <table_name> set unused column <col>;

    Ex:

         SQL> alter table student set unused column marks;

  

         Even though the column is unused still it will occupy memory.

d) DROPPING UNUSED COLUMNS

    Syntax:

        alter table <table_name> drop unused columns;

    Ex:

        SQL> alter table student drop unused columns;

        * You can not drop individual unused columns of a table.

e) RENAMING COLUMN

    Syntax:

        alter table <table_name> rename column <old_col_name> to <new_col_name>;

    Ex:

        SQL> alter table student rename column marks to smarks;

USING TRUNCATE

This can be used to delete the entire table data permanently.

Syntax:

      truncate table <table_name>;

Ex:

     SQL> truncate table student;

USING DROP

This will be used to drop the database object;

Syntax:

     Drop table <table_name>;

Ex:

     SQL> drop table student;

USING RENAME

This will be used to rename the database object;

Syntax:

     rename <old_table_name> to <new_table_name>;

Ex:

     SQL> rename student to stud;