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.

SQL Data Types...

SQL Data Types are very useful while working with the SQL statements. Before learning about DDL statements, we need to learn Data Types which will help us in understanding  DDL statements better. In my next post I want to discuss about DDL statements in SQL. So, I am discussing SQL Data Types in this post as these are essential to learn DDL.

Data Type as specified by its name , will give us an idea about the type of the data. As, discussed in introductory topics table columns will be associated with the data types. These data types will decide the type of data which is going to be stored in those particular columns. Each column of the table will be associated with a specific data type.

  In SQL we have many data types. But in this post I will discuss only about most important data types of SQL which will be vastly used in creating the columns of the table. We have 4 important data types available in SQL.
They are,

               1. Number
               2. Char
               3. Varchar2
               4. Date

These are the most important data types in SQL.

1. NUMBER :: 


           Number data type will corresponds to the numeric data. It means the column created with NUMBER data type can store numeric data only. So, If we want to store numeric data like salary, empno... etc we can create columns with NUMBER data type.

          We have two different syntax's for using this Number data type.

                   NUMBER (m);
                   NUMBER(m,n);

      These two syntax's will be used for two different purposes. In numeric data we will be having two types of numbers called Whole numbers and Decimal point numbers. Whole numbers means numbers with out any decimal point . (Ex : 520, 44, 22,.....). Decimal point numbers means numbers with decimal point (Ex : 120.12, 34.2, 10.01, ......). So, to store these different Syntax's will be used.

     To store Whole numbers we will use the first syntax. That is, NUMBER(m). Here m will specify total number of digits allowed in the number to be stored.

    Ex : if we have created column with data type NUMBER(6), then in this column we can store numeric data up to 6 digit length like 1, 333, 22, 4444, 55555, 666666....... .
 we cannot store 1234567, as it contains 7 digits in it.

   This syntax cannot be used to store decimal point data as, it will give you improper results. If you store decimal point data using above syntax, it will store it as a whole number only.

  Ex : if we store 10.56 in a column created using above syntax, then it will store it as 10 only. So, we will get improper results.

So, If we want to store decimal point numbers we have to use the second syntax. NUMBER(m,n). Here m will specify total number of digits including digits after decimal point allowed in the number to be stored and n will specify number of digits allowed after decimal point in the number in m digits. Let me explain this with an example.

  Ex : If we have created a column with data type NUMBER(10,3) . Then in this column we can store decimal point data up to 10 digit length. In these 10 digits before decimal point 7 digits are allowed and after decimal point 3 digits are allowed.

  1235467.812  we can store . 12345678.1 we cannot store as we have 8 digits before decimal point. we can have any number of digits after the decimal point, but data base will do appropriate rounding off operation before storing that data. But the number of digits before decimal point should be <=7 in this case. In general should be <=m-n.

 Using Number data type columns we can store numeric data up to 38 digit length. If you want to use maximum size for numeric column (38 digits), then you can create column with datatype specified as NUMBER without giving any braces and size. In this case by default we can store number up to 38 digit length in that column.

2.CHAR :: 


       CHAR data type will be used to store character type of data or string data like names etc. CHAR data type specifically used for storing the fixed length character data. Fixed length character data means, data which will not vary in size at any point of time. Best example for this type of data is, Gender column which will be used to store the genders of employees. For this column values will be either 'M' or 'F'. So, at any point of time gender data will contain single character only which is fixed. To handle such type of fixed length character data we will use CHAR data type.

    The syntax for CHAR data type is,

                            CHAR(n);

     Here n means maximum number of characters allowed in the data to be stored in that particular column created using this data type. while handling character data in SQL we have to handle that data using single quotes. Wherever we use character data, we must enclose it in single quotes.

       Ex : 'Ramu', 'Raj', 'Bala', ...... etc.

   If we have any spaces between the strings in single quote then data base will treat each space also as a character (called as null character).

     Ex : 'Ram Babu' --- length of this string will be 8 (including one space)

  So, if we have created a column with data type as CHAR(20) then we can store fixed length character data up to 20 character in it.

  We can store variable length character data also in CHAR columns, but it is not advisable. Let me explain this with a small example.

  Let us say we have created a column with data type as CHAR(10). Now I want to store the name 'Ramu' in it. Then how data base will store this name is, It will all the 10 character space to store this 4 character name 'Ramu'. It will keep remaining 6 characters as null. So, here 6 character memory will be wasted. So, the problem with this data type is , It will use all the space specified while creating the column, irrespective of the length of input string.

  So, this is the disadvantage of CHAR datatype. This is the reason why it is used to store fixed length character data only.

 Using this data type we can store fixed length character data up to 2000 character length.

3. VARCHAR2 ::
       
             VARCHAR2 data type will also be used to store character or string data only. But, this data type will be specifically used to store the variable length character data. Variable length character data is a data, which varies in size from one value to another value. Example for this data is ename which can be of different sizes for different employees. To handle such type of variable length character data we will use VARCHAR2 data type.

        The syntax for using VARCHAR2 data type is,

                         VARCHAR2(n);

      Here n represents maximum number of characters allowed in the string which we want to store. As, I mentioned earlier this will be used to store variable length character data.Let me explain this with small example.

    Let us say we have created a column with data type VARCHAR2(10) and we want to store name 'Ramu' in it. Then what data base will do is, it will use only 4 characters space to store 4 character length name 'Ramu'. So, here memory is not wasted. This is the reason for using VARCHAR2 data type to store variable length character data.

     So, if we create a column with data type VARCHAR2(20), then we can store character data up to 20 character length in that particular column.

   Using this data type we can store variable length character data up to 4000 character length.
we will use
        


4. DATE ::


       DATE data type will be used to store dates in the data base. While handling dates in SQL, like handling in character data, we need to enclose them in single quotes. So, wherever we use dates in SQL, we must enclose them in single quotes. We cannot use dates in whatever format we want in SQL. There is specific format for handling the dates in SQL, which is a database understandable format. We have to use dates in that particular format only.

     The format for using dates in SQL is,

                 'dd-mon-yy'  or  'dd-mon-yyyy'.

     Here dd ---- 2 digit date, mon --- 3 character month and yy or yyyy ---- 2 digit year or 4 digit year respectively.

      Ex : '12-aug-11' ,  '22-sep-2011', ....... etc.

   So, in order to handle dates in SQL we must enclose them in single quotes and we must use above formats only.

    To store these dates in the data base we need to have columns created in the data base with DATE data type. The syntax for using this data type is,

                     DATE
 So, this data type will be used to store dates in the data base.


With this all the 4 important data types in SQL are completed. In my next post I will discuss about DDL statements in SQL.