jobs4timesLogo jobs4timesLogo

Data Definition Language (DDL)

Agenda :
  1. Oracle Introduction

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));

ALTER :

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

    Syntax:
    alter table <table_name> add <col datatype>;

    Ex:
    SQL> alter table student add sdob date;

  2. Remove Column :

    Syntax:
    alter table <table_name> drop <col datatype>;

    Ex:
    SQL> alter table student drop column sdob;

  3. Increase/Decrease Precision of a Column (OR) Modify 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.

  4. 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.

  5. 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.

  6. 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;

TRUNCATE :

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

Syntax:
truncate table <table_name>;

Ex:
SQL> truncate table student;

DROP :

This will be used to drop the database object;

Syntax:
Drop table <table_name>;

Ex:
SQL> drop table student;

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;



BACK