Explanation About Operators

Arithmetic Operators

These are:

+          Addition                                  *          Multiplication

-           Subtraction                            /            Division                     

( )        Enclosed operation

Logical Operators

Logical operators that can be used in SQL sentences are:

The AND Operator:

The AND operator allows creating an SQL statement based on two or more conditions being met. It can be used in any valid SQL statement such as select, insert, update, or delete. The AND operator requires that each condition must be met for the record to be included in the result set.

Example:-

            Select ename , sal, job from emp where job=’clerk’ and sal<=1600;

The OR Operator:

The OR operator allows creating an SQL statement where records are returned when any one of the conditions being met.

Example:-

            Select ename , sal, job from emp where job=’clerk’ or  sal<=1600;

Special operator:-

RANGE SEARCHING:-

1.NOT BETWEEN:-

Example:- Select * from emp where sal not between 1000 and 1600;

The above select will retrieve all the records from the emp table except where the sal is in between 1000 to 1600.

2. BETWEEN:-

In order to select data that is within a range of values the between operator is used. The between operator allows the selection of rows that contain values within a specified lower and upper limit .Example:-

Select *from emp where sal between 1000 and 1600;

The above select will retrieve all the records from the emp table where the sal is in between 1000 and 1600.

3.IN:-

The arithmetic operator (=) compares a single value to another single value .  In case a value needs to be compared to a list of values than the IN operator is used. In operator helps reduce the need to use multiple OR condition

Example:-

Select ename,job,sal from emp where job in(‘clerk’,’manager’,’salesman’);

4.NOT IN:-

The not in operator is the opposite of the in operator. This will select all the rows where values do not match the value of the list.

Example:-

Select ename,job,sal from emp where job not in(‘clerk’,’manager’,’salesman’);

PATTERN MATCHING:-

5.LIKE:-

The comparison operators discussed so far have compared one value, such precision may not always be desired or necessary. For this purpose ORACLE provides the like operator.

The like operator allows comparison of one string value with another string value. This is achieved by using wildcard characters. Two wildcard characters that are …

1)     % allows to match any string of length.

2)     _ allows to match on a single character.

Example:-

Select ename, sal, job from emp where ename like ‘_a%’;

6.IS NULL:-

Example:-

Select *from emp where sal is NULL;

When this statement is executed all blank rows  are retrieved. This is because oracle has now changed its rules so that empty cell behave as null values.

7. ALL:-

            Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, >=, <=, Evaluates to TRUE if the query returns no rows.

Example:-

SELECT ename FROM emp where sal<=ALL(500,1200,800);

  

 Basic Data Types

Data Type	Description
CHAR (size)	This data type is used to store character strings values of fixed length. The size in brackets determines the number of characters the cell can hold. The maximum number of characters (i.e. the size) this data type can hold is 255 characters. The data held is right-padded with spaces to whatever length specified.
VARCHAR (size) / VARCHAR 2(size)	This data type is used to store variable length alphanumeric data. It is a more flexible form of the CHAR data type. The maximum this data type can hold upto 4000 characters. One difference between this data type and the CHAR data type is the inserted values will not be padded with spaces.
DATE	This data type is used to represent date and time. The standard format is DD-MON-YY as in 21-JUN-04. DateTime stores date in the 24-hour format. By default, the time in a date field is 12:00:00 am, if no time portion is specified. The default date for a date field is the first day of the current month. Valid dates range form January 1, 4712 B.C. to December 31, 4712 A.D.
NUMBER (P,S)	The NUMBER data type is used to store number (fixed or floating point). Number of virtually any magnitude maybe stored up of 38 digits of precision. Valid values are 0, and positive and negative number with magnitude 1.0E-130 to 9.9…E125. Numbers may be expressed in two ways: first, with the numbers 0 to 9, the signs + and -, and a decimal point (.); second, in scientific notation, such as, 1.85E3 for 1850.
LONG	This data type is used to store variable length character strings containing upto 2 GB. Only one LONG value can be defined per table.
RAW/ LONG RAW	The RAW /LONG RAW data types are used to store binary data, such as digitized picture or image. RAW data type can have a maximum length of 255 bytes. LONG RAW data type can contain up to 2 GB. Values stored in columns having LONG RAW data type cannot be indexed.

-------------------------------------------------------------------------------------------------------

DATA TYPE	CONTAINS
NUMBER NUMBER (w) NUMBER (W,D)	Numbers, a decimal point and minus sign. Allows 40 digits and decimal point, sign. Number column of specified size. Number column of specified width out of which 2 is reserved for decimals.
CHAR (W)	Fixed length character data. Maximum size is 255. Default is 1.
DATE	Date
VARCHAR2(W)	Variable length character string of width w. Maximum width is 4000 characters.
LONG	Any printable character – a, 3&, blank. The length can vary from 0 to 2 gigabytes. Only one LONG column may be defined per table. It cannot be used in function, expressions or in the WHERE clause.
RAW (W)	Binary data in whatever form the host computer stores. Maximum size is 255 bytes.
LONG RAW	Raw binary data and in other ways is like the LONG DATA.
ROWID	A value that uniquely identifies a row in an Oracle database. It is returned by the pseudo-column ROWID. Table columns may not be assigned this type.