SQL Statements Part2 (DDL) .....

In this post I want to discuss about DDL statements in SQL.

DDL ::

     DDL means Data Definition Language. These statements will work on structure of the tables . Structure of the table means, Name of the table, columns and their corresponding data types and constraints on the table ... etc. These statements will not effect the data present in the tables. Data definition involves, Creating the new tables, modifying the existing tables like changing names of tables or columns , adding new columns to the table, removing existing columns from the table, removing tables from the database ..... etc.

    DDL statements will carry implicit commit. So, these statements will not require any transaction control. Implicit commit means, whatever DDL statement issued against the database will be automatically committed. There is no rollback for the DDL statements. So, while working with the DDL statements we need to be very careful.

NOTE ::  One important point to remember here is, the implicit commit which is carried by DDL statements will be applicable to total transactions happened in that session till that point. It means that If we have any un handled (using TCL) DML statements issued against the database before issuing the DDL statements, they will also be committed to the database due to the implicit commit carried by DDL. So, we need to take care of this. If any pending DML transactions are there then first we have to handle them using commit or rollback before issuing any DDL operation in that session.

  We have 5 types of DDL statements available in SQL . They are,

                           

                              1. CREATE

                              2. ALTER

                              3. TRUNCATE

                              4. DROP

                              5. RENAME

1. CREATE :: 

            Create will be used to create new tables in the Database. We can also say that Create will be used to create new objects in the database. Objects means Table, Views, Indexes, Synonyms..... etc. We can create any of these objects using Create statement.  But in this post I will discuss only about tables. In upcoming posts I will discuss about remaining database objects.

          The syntax for creating new table using CREATE statement is,

            CREATE TABLE tab_name (
             col1 datatype,
             col2 datatype,
             ---------------
             ---------------
             coln datatype);

 So, using this syntax we can create new tables in the database. We can use any of the data types which we have discussed in the previous post to create columns of the database depending on type of data we want to store in those columns.

Example for Create statement is,

         CREATE TABLE Emp(
         empno number(10),
         ename varchar2(20),
         hiredate date,
         sal number(10,2),
         deptno number(10));

  This statement will create Emp table with columns empno (numeric type), ename (character type), hiredate (date type), sal (numeric) and deptno (numeric) .

This is how we can create new tables in the database.

If we want to see the structure of the tables without seeing the data present in the tables, we can use the describe command. Syntax for that is,

    DESC tab_name;

 Ex :   DESC Emp;

 This statement will show us the structure of Emp table.

ALTER ::


      ALTER command will be used to modify the structure of the existing tables. Using ALTER we can do many things. In this post I will discuss about basic things which we can do with ALTER. In coming posts I will discuss about remaining. Alter can be used to,

   1. Add new column to the table
   2. Remove existing column from the table
   3. Modify size of character columns of the table
   4. change the name of a column of the table
   5. change the name of the table itself.

 The syntax for adding new column to the table is,

 ALTER TABLE tab_name ADD col_name datatype;

 EX :  ALTER TABLE Emp ADD gender CHAR(1);

 This statement will add new column gender of character type to Emp table.

 The syntax for removing existing column from the table is,

 ALTER TABLE tab_name DROP COLUMN col_name;

 EX : ALTER TABLE Emp DROP COLUMN gender;

 This statement will remove gender column from the Emp table.

 The syntax for modifying size of character column from the table is,

 ALTER TABLE tab_name MODIFY col_name datatype with new size;

 EX : ALTER TABLE Emp MODIFY ename VARCHAR2(30);

 This statement will be used to change the size of ename column to 30.

 This statement can be used to change the size of any column to >= max(size) of the data already present in that column. For example if the max(size) of data already present in the column is 6 , then we can modify its size to >=6.

 The syntax for changing the name of the column is,

 ALTER TABLE tab_name RENAME COLUMN old_name TO new_name;

 EX : ALTER TABLE Emp RENAME COLUMN sal TO salary;

 This will change the name of sal column of Emp to salary.

 The syntax for changing name of a table using ALTER is,

 ALTER TABLE tab_name RENAME to new_name;

 EX : ALTER TABLE Emp RENAME to Employee;

 This statement will change the name of Emp table to Employee.

 So, these are the some basic things which we can do with ALTER statement.

TRUNCATE ::

      TRUNCATE will be used to remove the data from the tables. Truncate will be used to remove all the data present in the tables. We cannot remove specific rows of a table using TRUNCATE command. It means that we cannot write WHERE clause with this statement.

So, using truncate we can remove entire rows from the table only. The syntax for writing TRUNCATE is,

TRUNCATE TABLE tab_name;

EX : TRUNCATE TABLE Emp;

This will remove all the rows of Emp table.

 Even though TRUNCATE is similar to DELETE statement of DML, there are some advantages of TRUNCATE over DELETE which I will discuss after discussing TCL statements.

DROP ::

    DROP will be used to remove tables from the database. This will remove entire table from the database along with data present in it. Syntax for DROP statement is,

 DROP TABLE tab_name;

 EX : DROP TABLE Emp;

 This will remove Emp table from the database.

 RENAME ::


    RENAME will be used to change the names of database objects like tables, views, ..... etc. Using this command we can change names of database objects only. We cannot change names of columns of the table.

 Syntax for this statement is,

  RENAME old_name TO new_name;

EX : RENAME Emp TO Employee;

 This statement will change the name of Emp table to Employee.

 We can change name of table using ALTER also, but we cannot change names of remaining database objects using ALTER statement. Similarly we cannot change name of column of table using RENAME statement. We have to use ALTER only for that.

So, These are the DDL statements available in SQL.