SUB QUERY

               SUB QUERY

*The Query with in another is query is known as "SUB QUERY".
*In Sub query the inner part is execute first and the Outer query will execute second.
*Can not use the WHERE condition after the GROUP BY Clause.
*Sub query start with "(" and ends with ")" after the conditional operator.

Simple Example 1::


SELECT * FROM employees WHERE Salary>
(SELECT Salary FROM employees where First_Name='Neena');

In this query,The inner part executes first and get the "Salary" of employee "Neena" and then will returns the Output,Who
all are having more than(Greater than) "Neena's" salary.

Simple Example 2::


SELECT * FROM employees WHERE Salary>
(SELECT ROUND(AVG(Salary)) FROM employees);

In this query the same Inner part is executes first and then Get the "Average salary" of employees and then return the Output
Who all are having greater then "AVG" salary.

TYPES OF SUB QUERIES::


There are following two types of subsidiaries are there.

1.Single Row Sub Queries.
2.Multiple Row Sub Queries.

1.Single Row Sub Queries::

Query that return only one row from the inner SELECT statement is Known as "Single Row Sub Queries".

Here below i have mentioned some single-row comparison operators::
 -----------------------------------------
= - Equal To
< - Less than
<= - Less than or Equal to
> - Greater than
>= - Greater than or Equal to
<> - Not Equal to
------------------------------------------


Places Which we are Using SUB Queries::

1.USING Group Functions in Sub Query(MIN,MAX,AVG....)
2.After SELECT Key word
3.After HAVING clause

Examples and their explanations for Single Row Sub quries::


SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141);

OUTPUT::
In this the Inner Query returns the Output is "ST_CLERK"

So it gives the Output like Below format::
----------------------------------
Nayer ST_CLERK
Mikkilineni ST_CLERK
Landry ST_CLERK
Markle ST_CLERK
Bissot ST_CLERK
Atkinson ST_CLERK
....Cont...
------------------------

SELECT last_name, job_id
FROM employees
WHERE salary>
(SELECT salary
FROM employees
WHERE employee_id = 143);

OUTPUT::
In this the Inner Query returns the Output is "2600" Then outer query execute then gives the Outputs.

So it gives the Output like Below format::
-------------------------------------
King AD_PRES 24000
Kochhar AD_VP 17000
De Haan AD_VP 17000
Hunold IT_PROG 9000
...Cont....


USING SUBQUERIES AFTER HAVING CALUES::


SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id);

OUTPUT::
---------------------------
JOB_ID AVG(SALARY)
---------------------------
PU_CLERK 2780
---------------------------

2.Multiple Row Sub Queries::

*If Inner Query returns more than one row then it's known as "Multiple Row Sub queries".
*Using Following Operators after the Conditional Operators to Solve this Simple.

--------------------------------------------------------
IN - Equal to any member in the list

ANY - Compare value to each value returned by
the sub query

ALL - Compare value to every value returned
by the sub query
--------------------------------------------------------

USING IN::

While Using in Don't Specify the Conditional Operator With the Word "IN"

Example::

SELECT Employee_Id,First_Name,Salary
FROM employees
WHERE Salary IN
(SELECT Salary from employees where First_Name='Alexandar');

This returns the Out Put in Below Format because the First_Name='Alexandar' having 2 times and the Same Alexander Salary is
equal to Some employees in employee tables.


--------------------------------------
EMPLOYEE_ID FIRST_NAME SALARY
--------------------------------------
158 Allan 9000
152 Peter 9000
109 Daniel 9000
103 Alexander 9000
196 Alana 3100
181 Jean 3100
142 Curtis 3100
115 Alexander 3100
--------------------------------------

USING "ANY" OPERATOR::

Using ANY Operator Which Compares the Each sub query value with the Outer Query and give the OUTPUT based on below
CONDITION BASES.
=================================================================
< ANY --- Get the Max Value in the Sub Query and Give Output Lessthan than the MAX                                 Value.
------------------------------------------------------------------------------------------------
> ANY --- Get the MIN Value in the Sub Query and give Output Greterthan than the MIN                               Value
------------------------------------------------------------------------------------------------
= ANY --- Which Gives the result same as IN Condition
=================================================================

Example::
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(select Salary from employees where Job_Id='IT_PROG');

Here first the Sub query will excute and it check the MAX Salary of IT_PROG(MAX is 9000) and returns the output like Less than 9000
who all are get

------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
132 Olson ST_CLERK 2100
128 Markle ST_CLERK 2200
136 Philtanker ST_CLERK 2200
127 Landry ST_CLERK 2400
135 Gee ST_CLERK 2400
119 Colmenares PU_CLERK 2500
131 Marlow ST_CLERK 2500
..Cont.........
------------------------------------------------------

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ANY
(select Salary from employees where Job_Id='IT_PROG');
OUTPUT::

                ------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
100 King AD_PRES 24000
101 Kochhar AD_VP 17000
102 De Haan AD_VP 17000
145 Russell SA_MAN 14000
146 Partners SA_MAN 13500
201 Hartstein MK_MAN 13000
108 Greenberg FI_MGR 12000
-------------------------------------------------------

USING "ALL" OPERATOR::

Following are the conditions for using ALL Operator.

=================================================================
< ALL --- Get the MIN Value in the Sub Query and Give Output Less than than the MIN Value.
------------------------------------------------------------------------------------------------
> ALL --- Get the MAX Value in the Sub Query and give Output Grater than than the MAX Value
=================================================================

Examples::


SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL
(select Salary from employees where Job_Id='IT_PROG');

OUTPUT::

------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
115 Khoo PU_CLERK 3100
116 Baida PU_CLERK 2900
117 Tobias PU_CLERK 2800
118 Himuro PU_CLERK 2600
119 Colmenares PU_CLERK 2500
125 Nayer ST_CLERK 3200
126 Mikkilineni ST_CLERK 2700
127 Landry ST_CLERK 2400
128 Markle ST_CLERK 2200
-------------------------------------------------------

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ALL
(select Salary from employees where Job_Id='IT_PROG');

OUTPUT::

------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
100 King AD_PRES 24000
101 Kochhar AD_VP 17000
102 De Haan AD_VP 17000
108 Greenberg FI_MGR 12000
114 Raphaely PU_MAN 11000
145 Russell SA_MAN 14000
146 Partners SA_MAN 13500
147 Errazuriz SA_MAN 12000
148 Cambrault SA_MAN 11000
------------------------------------------------------

 =====>SUB QUERY END<=====

Read More

JOINS

Joins In Oracle

       JOIN is used for selecting the data's from one or more tables.

TYPES OF JOINS::

1.EQUI JOIN
2.OUTER JOIN
   i)LEFT OUTER JOIN
  ii)RIGHT OUTER JOIN
 iii)FULL JOIN
3.SELF JOIN
4.CROSS JOIN

EQUI JOIN::

The EQUI JOIN is return the OUTPUT when the condition satisfy(Join Satisfy)returns only the "Matched Values".

Example Table::


Employees:(Table 1)
----------------------------------------------
E_Id E_Name Dept_Id Hire_date Salary
----------------------------------------------
1 Arul              1 01-Jan-11 15000
2 Xavier 2 01-Feb-10 18000
3 Vino              3 25-Sep-08 25000
4 Mathi 4 25-Dec-09 23000
5 Divya             6       05-Jan-06 45000
----------------------------------------------

Departments:(Table 2)
--------------------------
Dept_Id Dept_Name
--------------------------
1 HR
2 SALES
3 TEAM LEAD
4 DEVELOPER
5 MANAGER
---------------------------

1.EQUI JOIN QUERY::
      It return only the Matched record from the tables.

SELECT E_Id,E_Name,Hire_date,Dept_Name,Salary
FROM Employees a JOIN Department b
ON a.Depet_Id=b.Dept_Id;

OUTPUT::
------------------------------------------------------------
1 Arul 01-Jan-11 HR 15000
2 Xavier 01-Feb-10 SALES 18000
3 Vino 25-Sep-08 TL             25000
4 Mathi      25-Dec-09     DEV 23000
-------------------------------------------------------------

2.OUTER JOIN::

The OUTER JOIN returns the Output the Common values(Matched Values ) and Unmatched Values.

OUTER JOIN QUERY::

SELECT E_Id,E_Name,Hire_date,Dept_Name,Salary
FROM Employees a OUTER JOIN Department b
ON a.Depet_Id=b.Dept_Id;

OUTPUT::
-----------------------------------------------------
1 Arul        01-Jan-11         HR                15000
2 Xavier 01-Feb-10 SALES 18000
3 Vino        25-Sep-08       TEAM LEAD 25000
4 Mathi 25-Dec-09       DEVELOPER     23000
5 Divya 05-Jan-06         MANAGER 45000

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

2)i)LEFT OUTER JOIN::
The LEFT OUTER JOIN or LEFT JOIN is return the OUTPUT like Matched records and unmatched records from left side table.

LEFT OUTER JOIN QUERY::
SELECT  a.E_Id
,a.E_Name
,a.Hire_date
,b.Dept_Name
,a.Salary
FROM Employees a
LEFT JOIN Department b
ON a.Depet_Id=b.Dept_Id;

OUTPUT::
-------------------------------------------------------------
1 Arul 01-Jan-11        HR 15000
2 Xavier 01-Feb-10 SALES 18000
3 Vino 25-Sep-08 TL 25000
4 Mathi 25-Dec-09 DEV 23000
5 Divya           05-Jan-06          - 45000
-------------------------------------------------------------
2)ii)RIGHT OUTER JOIN::
The RIGHT OUTER JOIN or RIGHT JOIN returns the Output like Matched records and Unmatched records from the right side table.

RIGHT OUTER JOIN QUERY::
SELECT
E_Id
,E_Name
,Hire_date
,Dept_Name
,Salary
FROM Employees a
RIGHT JOIN Department b
ON a.Depet_Id=b.Dept_Id;

OUTPUT::
-----------------------------------------------------
1 Arul 01-Jan-11 HR 15000
2 Xavier 01-Feb-10 SALES 18000
3 Vino 25-Sep-08 TL             25000
4 Mathi     25-Dec-09 DEV           23000
- - - MANAGER -
-----------------------------------------------------

JOIN Using More than One Table::

Consider the below three table,

JOIN QUERY::
SELECT a.E_Id
,a.E_Name
,a.Hire_date
,b.Dept_Name
,a.Salary
,c.Loc_Name
FROM Employees a
JOIN Department b
ON a.Depet_Id=b.Dept_Id
JOIN Locations c
ON b.Loc_Id=c.Loc_Id;

OUTPUT::
-----------------------------------------------------------------------------
1 Arul 01-Jan-11       HR 15000 Chennai
2 Xavier 01-Feb-10 SALES 18000 Madurai
3 Vino 25-Sep-08 TEAM LEAD 25000 Tirunelveli
4 Mathi 25-Dec-09 DEVELOPER 23000 Trichy
-----------------------------------------------------------------------------

3.SELF JOIN::

SELF JOIN used for JOIN the table Itself.

Example Query::

SELECT   Emp.Employee_Id "Employee ID"
,Emp.First_Name "Employee Name"
,Emp.Salary "Employee Salary"
,Man.First_Name "Manager Name"
,Man.Salary "Manager Salary"
FROM employee Emp
INNER JOIN
employee Man
ON Emp.Manager_Id=Man.Employee_Id;

CROSS JOIN::

*It's a wrong Join (n x m)
*It wroking like all the rows in the left table will join all the row in the right table.
*It will happen if u ignore the JOIN Condition or If you provide invalid Join.
=======================================================================
JOIN NOTES::
If we are using "N" number of tables then we used (N-1) JOINs in the Query.
=======================================================================
JOIN ---USING()::

*While Using the USING() don't specify the alias name for the JOINING column.
*Only one join happens from left table to right table then(We can perform only one column joining) we use USING()

Example::

SELECT   e.Employee_Id
,e.First_Name
,Department_Id
,Department_Name
FROM employees e JOIN
Departments d
USING(Department_Id);

NATURAL JOIN::

*NATURAL JOIN also happen's only one possibilities happen to join left table to right table(only one column)
*Here also don't specify the alias for the common column.

Example::

SELECT   e.Employee_Id
,e.First_Name
,Department_Id
,Department_Name
FROM employees e
NATURAL JOIN
Departments d;
                                                    ====>JOINS END<====

Read More

Func Part..3

FUNCTIONS CONTINUES..

GENERAL FUNCTIONS::

The General functions are mainly used for Process the "NULL" Values.

There are following four types of General functions are there::

1.NVL()
  --It's having two arguments
2.NVL2()
  --It's having three arguments
3.NULLIF()
  --It's having two arguments
4.COALESCE()
  --It's having "n" number of arguments.

1.NVL()::(Argument1,Argument2)

The NVL() function used for if the first argument having NULL value then it's return the second argument value.

Example::

SELECT NVL(Commission_PCT,'0') From employees;
O/P---> -
-
1.2
3.3
-
2.NVL2()::(Argument1,Argument2,Argument3)

The NVL2() function used for if the first argument having "NULL" value then it's return the third argument value.

Example::

SELECT NVL2(Commission_Pct,'0','1') From employees
O/P--O/P---> 1
1
2.2
3.3
1
3.NULLIF()::(Argument1,Argument2)

The NULLIF() function used for return,If the two argument having same values then its return "NULL",else it print
the "first" argument value.

Example::

SELECT NULLIF (1,,1)
,(2,2)
,(1,2)
,(2,7)
,(7,9)
FROM dual;

O/P-------->>> -
-
1
2
7
4.COALESCE()::(1,2,3,....n)

The COALESCE() function returns the first "non null Value" from the Passed arguments.

Examples:

SELECT COALESCE(NULL,NULL,NULL,NULL,NULL,89,NULL,NULL,99,100,101) from dual;
O/P--89

II.MULTI ROW FUNCTIONS
======================
GROUP FUNCTIONS or AGGREGATE FUNCTIONS::

1.MIN()
2.MAX()
3.SUM()
4.AVG()
5.COUNT()

Example Table::

-----------------------------------------------
Name Dept_Id Salary
-----------------------------------------------
n1 10 25000
n2 20 45000
n3 20 17000
n4 30 13500
n5 30 73000
n6 30 72000
n7 40 1700
n8 40 2900
------------------------------------------------

1.MIN()::

The MIN() function is mainly used for display the Minimum of Value in the selected column.

Example::
SELECT MIN(Salary) FROM employees;
O/P--1700

2.MAX()::

The MAX() function is used for return the maximun of value in the selected column.

Example::

SELECT MAX(Salary) From employees;
O/P---73000
3.SUM()::

The SUM() function is used for returning the sum(Addition of selected column) selected column from the table.

Example::

SELECT SUM(1000 + 2000 + 3000) From dual;
O/P---6000
4.AVG()::

The AVG() function is used for display the value like (Add the Values and/divid the Number Of columns or Number of row)

Example::

SELECT ROUND(AVG(salary)) from employees;
O/P---6462

5.COUNT()::

The COUNT() function is used for Count the number in the selected rows.

Example::

SELECT COUNT(Salary) FROM employees;
O/P--107

GROUP BY & HAVING::

When ever use the Group function Must use the GROUP BY Clause for the non group function columns.

USING GROUP BY::

SELECT First_Name,COUNT(Salary) FROM employees;
GROUP BY First_Name;
O/P--Steven   2
    Neena    1
USING HAVING::

*When ever we want to check the condition while using the Group function case we must use the "HAVING",do not use the
"WHERE" condition.
*We can use "HAVING" with out GROUP BY


Example::

SELECT First_Name,COUNT(Salary) FROM employees
GROUP BY First_Name
HAVING Count(Salary)>1;

O/P---Steven  2
     Peter   3
     John    3
USING ORDER BY While Use GROUP BY & HAVING::

If the "ORDER BY" Comes in the query ,It Should place after the "HAVING" statement.
Example::

SELECT First_Name,COUNT(Salary) FROM employees
GROUP BY First_Name
HAVING Count(Salary)>1
ORDER BY First_Name Asc;

O/P--- Alexander 2
David 3
James 2

DECODE & CASE::


*Decode is a Function
*CASE is a expression
*Decode produce the OUTPUT based on If the next ..next value satisfy the condition else it print the Final Value.
*In Case we can used Oracle Operators "LIKE,BETWEEN AND,=,<,>,<>"
*Compare to Decode , CASE is fast because it followes ANSII Standards.
*DECODE is an SQL Standard but CASE is an SQL and PL/SQL Standard.

Example Using DECODE::

SELECT DECODE('Infycle','Infycle','YES','NO')"EXAMPLE" FROM dual;
O/P---YES
SELECT DECODE('Infycle','infycle','YES','NO') "EXAMPLE" FROM dual;
O/P---NO
SELECT DECODE('Infycle','Infycle_Chennai','YES_1','Infycle','YES_2','NO') "EXAMPLE" FROM dual;
O/P---YES_2
SELECT DECODE('Infycle','Infycle_Chennai','YES_1','InFyCle','YES_2','NO MATCH') "EXAMPLE" FROM dual;
O/P---NO MATCH

Example Using CASE::


SELECT FIRST_NAME,DEPARTMENT_ID,
CASE DEPARTMENT_ID
WHEN 10
THEN 'ADMIN'
WHEN 20
THEN 'PURCHASE'
WHEN 30
THEN 'SALES'
WHEN 50
THEN 'HR'
ELSE 'OTHERS'
END
FROM employees WHERE DEPARTMENT_ID in (10,20,30,50);

Read More