Oracle SQL Introduction

Oracle SQL Introduction

WHERE Condition:

A table contains the lacks of records but if we want any "specific record" from the table,in this case we use "WHERE".

Example:

SELECT * FROM employees where Employee_Id='100';
It return the value  who all are having the id only 100.

Conditional Operators::

=,<,>,<=,>=,<>,!=,^=(The last 3 oprataors are same (Not equal operators))

Examples::

SELECT First_Name,Salary FROM employees where salary=5000;
              --Produce the output equal to 5000 salary employees

SELECT First_Name,Salary FROM employees where salary>5000;
              --Produce the output Greter than 5000 salary employees

SELECT First_Name,Salary FROM employees where salary<5000;
              --Produce the output less than 5000 salary employees

SELECT First_Name,Salary FROM employees where salary>=5000;
              --Produce the output greater then or equal to 5000 salary employees

SELECT First_Name,Salary FROM employees where salary<=5000;
             --Produce the output less than or equal to 5000  salary employees

SELECT First_Name,Salary FROM employees where salary<>5000;
            --Produce the output not equal(with out 5000) to 5000  salary employees

LOGICAL OPERATORS::

i)AND
ii)OR
iii)NOT

AND::

The AND operator is return the OUTPUT when the Condition is True.

Example::

SELECT * FROM employees where employee_Id=10 and First_Name='Arul';

10 Arul 15000 01-Jan-99
---     ----
The query returns the output if the AND operations True.

OR::

The OR Operator is return the output if the any one of the condition satisfy before or after the OR operator.

Example::

SELECT * FROM employees where employee_Id=10 OR First_Name='Steven';
The above query is return the Output the employee Id condition as well as the First Name(If available in table)

ORACLE OPERATORS::

1.IN and NOT IN
2.BETWEEN ..AND --NOT BETWEEN... AND
3.LIKE and NOT LIKE
4.IS NULL and IS NOT NULL

IN and NOT IN::

IN condition is used for the return the Output when the Inside (Values satisfy the condition)

Example:

SELECT * FROM employees where Employee_Id IN(10,100);

The query return the values who all are having the Id 10 & 100.

NOT IN is reacted Opposite to the IN Condition
SELECT * FROM employees where Employee_Id NOT IN(10,100);

The query not return the values who all are having the Id 10 & 100 and return the remaining values.

BETWEEN...AND -----NOT BETWEEN ...AND

The BETWEEN....AND used for When we use any range or [from] to [to date] case we use this.

Example::

SELECT * FROM employees where salary BETWEEN 5000 and 1000;
SELECT * FROM employees where Hire_Date BETWEEN '01-Jan-89' and '31-DEC-89';

The NOT BETWEEN....AND reacts opposite to the BETWEEN...AND.

Example::

SELECT * FROM employees where salary NOT BETWEEN 5000 and 1000;
SELECT * FROM employees where Hire_Date NOT BETWEEN '01-Jan-89' and '31-DEC-89';

LIKE and NOT LIKE::

LIKE is Otherwise Known as "Pattern Matching" and "Wildcard Search".
and mainly used for search the String Using From [Beginning Char] or [end Char] or [Containg Character] Or
[Starting and ending Char].

LIKE OPERATORS:


1.% -- Something or Nothing
2._ -- one Char Indication



Examples::

%

SELECT * FROM employees where First_Name like 'St%';
            --Return the Values Start with "St"

SELECT * FROM employees where First_Name like '%en';
            --Return the Values end with "en"

SELECT * FROM employees where First_Name like 'S%n';
            --Return the Values Start with "S" and end with "n"

SELECT * FROM employees where First_Name like '%tev%';
            --Return the Values the Word "man" Contain continuously.
_

SELECT * FROM employees where First_Name like '_t%';
           --Return the Values the char "t" contain second position from beginning

SELECT * FROM employees where First_Name like '%v_n';
           --Return the Values end with "n" and reverse the 3rd char having "v"

SELECT * FROM employees where First_Name like '_____';
           --This return the values which are having 5 character.

Printing _ (Underscore Operator)  in Output::

Example:

Consider "arul_xavier@gmail.com" is a table value

SELECT * FROM email where email_Id like '____$%_' ESCAPE '$';

IS NULL & IS NOT NULL::

The IS NULL is used for returning the NULL Values in the selected column and the IS NOT NULL is used for returning the NOT NULL Values in the Selected column.

Example:

SELECT * FORM employees where Commission_Pct IS NULL
SELECT * FORM employees where Commission_Pct IS NOT NULL

ORDER BY ::

The ORDER BY mainly used for sorting the selected output values
  There are two types of sorting available

1.ASC
2.DESC

Example::

SELECT * FROM employees Order By Salary;
         --If we are not give any key word it will sort ASC by default.(1,2,..)

SELECT * FROM employees Order By Salary ASC;
         --It return the values by ascending.(1,2,...)

SELECT * FROM employees Order By Salary DESC;
         --It return the values by descending.(100,99....9,8,...1)

SELECT * FROM employees Order By 3 DESC;
         --It return the values by descending based on Column "3"

SELECT Emp_id,12*salary a FROM employees where Order By a DESC;
         --Return the value(Multiply salary with 12 and return by DESC
   Based on Alias Name(a).

SELECT Employee_Id,First_Name,Salary,Salary*2 FROM employees Order By           employee_Id,Salary*2 DESC;

  (It Return the employee_Id Asc then Ascending value the it Descending the salary*12 Column.)

                                             --------------Session - 2 End------------------