Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Monday, 28 March 2016

SQL DDL Operations

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



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



SQL TRUNCATE


This can be used to delete the entire table data permanently.
Syntax:
      truncate table <table_name>;

Ex:
     SQL> truncate table student;



SQL DROP

This will be used to drop the database object;

Syntax:
     Drop table <table_name>;

Ex:
     SQL>drop table student;

SQL 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;
Share this article :

0 comments:

Post a Comment