ORACLE Datatypes Basics

1) CHAR
-> Char contain alphanumeric data.
-> Length of Char datatype is between 1 and 255 characters.
-> If you don't specify a length, a char column stores a single character by default.
-> Char datatypes stores data in the Fixed-Length Format.
-> Be aware, when using this data type, that spaces are padded to the right of the value to supplement the total allocated length of the column.
-> It can be declared as char(size) where size is the total length allowed for a value in a column defined as char.

2) DATE
-> Date datatype is used to store all date and Time information.
-> Oracle always allocates a Fixed - 7 bytes for a DATE column.
-> Oracle uses the default format of DD-MON-YY for entering and displaying.
-> Following Include as a Part of DATE are century, year, month, day, hour, minute and second.
-> It enables you to store dates in range of January 1, 4712 B.C. To December 31, 4712 A.D.
-> To view System Date and time we can use the SQL function called SYSDATE.

3) LONG
-> It can store alphanumeric strings.
-> It stores the data in Variable-Length Format.
-> Length of Long rise upto 2 giga bytes.
-> Long is used to store more than 2000 characters in a column.
-> You cannot use any of Oracle's built-in functions or operator with LONG column.
-> You can think of a LONG column as a large container into which you can store or retrieve data but not manipulate it.
Limitations of LONG data type are as under
i> Only one column in a table can have LONG datatype, which should not contain unique or primary key constraint.
ii> Column cannot be indexed.
iii> Procedures or stored procedures cannot accept LONG datatype as argument.

4) NUMBER
-> It can store Numeric data such as Zero, Positive or Negative, Fixed or Floating point data.
-> It accepts Positive and negative integers and real number and has from 1 to 38 digits of precision.
-> Scale which refers to number of digits to the right of the decimal point, which varies between (-84) to 127.
-> Format of declaring NUMBER datatype is NUMBER( Precision, Scale)
example:-
1) NUMBER(5) - It can store numeric data upto 5 digits i.e. 99999
2) NUMBER(8,2) - It can store numeric data of 6 Integers and 2 for decimal.
i.e. 999999.99

5) VARCHAR2
-> It stores Alphanumeric data values.
-> The field must be between 1 and 2000 characters long.
-> VARCHAR2 supports Oracle built-in functions and operators.
-> It supports a variable length character string.
-> Format of declaring VARCHAR2 IS VARCHAR2( size ).

6) VARCHAR
Same as Varchar2. Always use Varchar2 instead of varchar as it may not be supported in future.

7) BLOB (Binary Large OBjects)
-> Blob include documents, graphics, sound, video, actually any type of binary file you can think of.
-> A binary large object with a limit of 4GB in length.
-> When you want to associate a BLOB with a 'NORMAL' row, two choices are available to you.
i> Store the BLOB in an operating system file(such as an MS-DOS file) and stores the directory and filename in the associated table.
ii> Store the BLOB itself in the LONG RAW column.

8) CLOB (Character Large OBject)
-> A character Large object with a limit of 4GB in length.

9) BOOLEAN
-> Boolean variables are used in PL/SQL control structure such as IF-then-ELSE and LOOP statements. A Boolean value can hold true, false or NULL only.

10) LONG RAW
-> It can store binary data upto 2GB.
-> LONG RAW datatype is designed for BLOB storage. You can't use any of the built-in functions with a LONG RAW column.

11) RAW
-> It can store binary data upto 255 bytes.
-> Because of this storing restriction, a RAW column is less useful than a LONG RAW column.

12) ROWID
-> Hexadecimal string representing the address of a row in a table.

13) INTEGER
-> Specifies size of an INTEGER(n) digits wide.

14) BINARY INTEGER
-> The number type is stored in a decimal format which is optimized for accuracy and store efficiency. This datatype is used to store signed integer values, which range from TM2147483647 to +2147483647. It is stored in a 2's complement binary format. Loop Counter are often of type BINARY INTEGER.

INTRODUCTION to DML
By data manipulation language we mean:
->The retrieval of information stored in the database.
->The insetion of new information into the database.
->The deletion of information from the database.
->The modification of data stored in the database.
Thus, it is a language that enables users to access or manipulate data as organised by the appropriate data model.

There are basically two types of DML
i>Procedural DMLs
->It requires a user to specify what data is needed and how to get it.
ii>Non-Procedural DMLs
->It requires a user to specify what data is needed without specifying how to get it.

As SQL is Non-Procedural language we will switch on to Non-Procedural DMLs as it is easy to understand and became very efficient for new users to begin with it.


The category of DML contains four basic statements:
i>select - which retrieves rows from a table.
ii>Insert - Which adds rows to a table.
iii>Update - Which modifies existing rows in a table.
iv>Delete - Which removes rows from a table.



SELECT Statement
->To view all rows or some of the rows from a table or more than one table depending upon a userdefined criteria,this command is used.
->By default, select statement display all rows which matches the criteria but we can get the unique records by using keyword distinct with it.

syntax:
SELECT [DISTINCT ALL] FROM
WHERE

->keyword ALL is optional by default it consider ALL rows in table.

example:
1)SQL>select * from emp;
->It will display all rows of emp table including all feilds.we can customize the output by selecting the columns which are needed to us.
2)SQL>select empno,ename from emp;
->It will display all rows of emp table including empno and employee_name detail for an employee.
3)SQL>select * from emp
where
job = 'clerk';
->It will display all details of employee whose status is clerk.
4)SQL>select distinct ename from emp;
->It will display all unique name of employee if employee_name is repeated twice than it will display only ones.Thus it discards the duplicate records.


INSERT Statement
->INSERT command is used to insert one or more rows in a table.
(There are many of syntax for insert command but one mentioned as under is the only standard way to insert multiple rows.)

syntax:-
INSERT INTO
(........)
VALUES
(<&Fieldname1>......<&FieldnameN>)
->Where the Fieldname should be valid field for a table.
->Field having datatype char,varchar2 and Date kind of data should be written in single quota.

examples:
1)SQL>Insert into emp
(empno,ename,job)
values
('1','SHREY','MANAGER');
->Above command will insert data for one record, here as data are mentioned directly, so we have made use of single quota.
2)SQL>Insert into emp
(empno,job)
values
(&empno,'&amp;job');
->Here we have customize the insert command to take data for only two field that is empno and job for multiple records.
->When you don't want to type the command which is used last than simply press the slash to activate the command which is used by you last.
3)SQL>Insert into emp
values
(&empno,'&amp;ename','&job');
->Note in Above command we haven't declare the field in which data is to be entered, it is optional when we have to enter data for all fields in column.
4)SQL>Insert into emp
(empno,ename,job)
values
('5','VRAJ',NULL);
->The above command will create a row and assign value 5 for empno and vraj for ename and leave the column job.
->If we doesn't want to enter value for a particular field we can just type NULL in it place during defining the INSERT command. And just press enter while entering the value.
5)SQL>Insert into emp_bkup
(select * from emp);
->The above command will copies all the rows from table emp and insert it into the table emp_bkup, provided that the structure of emp and emp_bkup is same.
->The above command is efficient to run when we want to create backup copy of our data.


UPDATE Statement
->Update command is used to modify content of table, that to under satisfaction of particular criteria.

syntax:
UPDATE
SET =
WHERE
;
->Where Clause is optional.
->Fieldname is name of column whose contents are to be manipulated.

example:
1)SQL>Update emp
set job = 'ACCOUNTANT'
where
job = 'MUNIM';
->Above sql statement will Modify Job field for employee whose status is munim, it will update the status munim with accountant.
2)SQL>Update emp
set salary = salary + (salary * 10)
where
job = 'PROGRAMMER';
->Above statement will increase salary of employee by 10% whose status is programmer.


DELETE Statement
->DELETE command is used to delete one or more rows from the table.
Note:-No warnings are given before deleting rows so be careful while performing this operation.

syntax:
DELETE FROM
WHERE
;
->Table_name is name of table from which you want to delete record/s.
->Criteria is condition under which you want to delete record/s.

example:
1)SQL>Delete from emp
Where
empno = 4;
->Above statement remove record of empno 4.
->Only one record is deleted.
2)SQL>Delete from emp
Where
job = 'OPERATOR';
->Above statement remove record/s of those employee whose status is operator in the company.