Saturday, October 13, 2012

Structured Query language(SQL) Overview



Structured Query language(SQL) :
  • SQL is not a case sensitive language. It means that we can write the SQL commands either in capital letters, small letters and combination also.
  • Every command in SQL should ends with semicolon (;).
  • SQL can also be pronounced as SEQUEL stands for Structured English Query Language.
  • This SQL language is sub divided into five sub-languages.
                      1) DDL – Data Definition Language
                      2) DML – Data Manipulation Language
                      3) DRL – Data Retrieval Language
                      4) TCL – Transaction Control Language
                      5) DCL – Data Control Language


  • Following are some basic datatypes which are required while creating tables in database through SQL Language.
DATA TYPES :

1) Numeric Datatypes :
    This data type allows numerical values such as integer (or) decimal values. In Oracle 10G number data type further divided into
  • Number(size)
  • Number(p,s)
Number (size) :
              This data type allows us to enter only integer values (either positive or negative values) such as EMP number, Salary etc.

Example: Eid number(5);

Number (P, S) :
                 This data type allows us to enter decimal values.

Example : Sal number(6, 2);
                P --- > Number of digits including decimal values
                S --- > Number of decimal places.

2)Character Datatypes: 
                 This data type allows us to enter character values such as student name and etc. These are further divided into three types

  • Char(size)
  •  Varchar(size)
  •  Varchar2(size)
Char (size):
              This data type allows only alphabets such as student names and employee names etc.The maximum size of this data type is 1-2000 characters.This data type always allocates the memory in static fashion.

Varchar(size)/Varchar2 (size): 
                        This data type allows us to enter alphabets, alpha numerical etc. The maximum size of this data type is 1-4000 characters. This data type always allocates the memory in dynamic fashion.

NOTE:

 VARCHAR(SIZE)                                 |     VARCHAR2(SIZE)

1)It does not support garbage collections |     It supports garbage collections

2)developed along with the SQL               |     Developed by the ORACLE Corp.

3)Date:
            This data type allows us to enter date values such as joining date, employee hire date etc. The default date format is oracle is DD-MON-YY .

Example: Hiredate date;

4)Timestamp:
       This data type allows us to enter both date and time, the default format of timestamp is dd-mm-yy hh:mm:ss .

5)Miscellaneous Datatypes:
                         These data types are classified into 3 types

  • CLOB(Character Large Object) 
  • BLOB (Binary Large Object) 
  • BFILE(Binary File) 

CLOB:
        This data type allows us to enter alphabets, alpha numerical values etc. and it’s an extension of VARCHAR2. The maximum length of this data type is 1-4GB. Whenever we need to enter employee history, student contact etc. then we uses this data type .

BLOB:
            This data type allows us to enter any type of images, pictures, sounds and multimedia messages etc. The maximum size of this data type is 1-4GB.

BFILE:
            This data type allows us to enter all types of values and it’s an extension of CLOB data type.The maximum size of this data type is 1-8GB.

  • If you want more information regarding this Oracle Datatypes just visit this Link.


1 comment:

back to top