SUBQUERY / NESTED QUERY:
**************************
JOINS:
******
-->If you want to fetch multiple data types of data from multiple tables we can write join queries but unneccesarily don't write any join query.
It will degrade your database performance or it will decrease your query performance that's why we need to write subqueries.
-->In which case we have to write a subquery.
-->In some cases we don't have to write any joins.
-->Whenever we want to perform some operations on table data which will not posible by using query then only we are using subqueries.
-->The requirement where we can't reach by using query those kind of operations we can perform subqueries.
EX:
***
-->If you want get data from one table based on input value of other table.
If you want get data from one table.from this table i want to display the data but before getting the data from this table i need to check the condition in this table.
means you are verifyng condition under one table and then you are fetching the data from other table.
in this cases don't use joins.
SUBQUERY / NESTED QUERY:
************************
--> A query inside another query is called as subquery or nested query or child query.
-->Outer query is always depends on the result of inner query.
-->The result set returned by the inner query will be used as input to outer query.
--> A Subquery is having two more queries those are,
i) INNER / CHILD / SUB QUERY
ii) OUTER / PARENT / MAIN QUERY
SYNTAX:
*******
SELECT * FROM <TN> WHERE <CONDITION> (SELECT * FROM ..................(SELECT * FROM ..........(SEELCT * FROM .....)));
--> AS PER THE EXECUTION PROCESS OF SUBQUERY IT AGAIN CLASSIFIED INTO TWO CATEGORIES.
1) NON - CORELETED SUBQUERIES
2) CORELATED SUBQUERIES:
--> IN NCRSQ -------- FIRST ------INNER QUERY EXEC---> RETURN VALUE---->LATER OUTER QUERY WILL EXECUTE.
-->Outer query is always depends on the result of inner query.
--> IN CRSQ ----- FIRST -----OUTER QUERY EXEC-----RETURN VALUE -------LATER INNER QUERY WILL EXECUTE.
-->First outer query is executed and later inner query will execute.
-->Inner query always depends on the results of outer query.
NON-CORELATED SUBQUERIES:
*************************
> SINGLE ROW SUBQUERY
> MULTIPLE ROW SUBQUERY
> MULTIPLE COLUMN SUBQUERY
> INLINE VIEW
SINGLE ROW SUBQUERY:
********************
-->When a subquery returns a single value is called single row subquery.
-->In single row subquery we can use operators are =,<,>,<=,>=,!=
EX1:
****
-->Display DNAME for EMPNO 7654.
EMPNO -->I/P
DNAME -->O/P
SELECT D.DNAME
FROM EMP E,
DEPT D
WHERE 1=1
AND E.EMPNO=7654
AND E.DEPTNO=D.DEPTNO
;
14-->1-->4
FIRST IT WILL GO TO EMP TABLE
14 EMPLOYEE IDS ARE VERIFIED
OUT OF 14 ONE VALUE IS MATCHED THAT ONE VALUE FROM THIS RECORD IT IS TAKING DEPTNO THAT DEPTNO IS VERIFIED WITH ALL DEPARTMENTS IN DEPT TABLE.
14+4=18 VERFICATIONS IT IS MAKING --BUT THIS CAN BE REDUCED BY SUBQUERY.
SUBQUERY:
*********
SELECT D.DNAME FROM DEPT WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE EMPNO=7654);
-->If you want fetch data from multiple tables atleast one column from table1 and another column from table2 so means you are selecting two columns from two tables.for this case you need to write joins.
-->If you are expecting data from one table but based on conditions on other tables then we need to write subqueries.
EX2:
****
--Accounting Employee details
EMP --I/P
DNAME --O/P
SELECT DNAME FROM DEPT WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE EMPNO='7782');
EMP --O/P
DEPT --I/P
SELECT *
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='ACCOUNTING');
/
EX3:
****
-->WHO ARE GETTING THE FIRST HIGH SALARY FROM EMP TABLE ?
-->Write a query to find the name of person who is getting max salary.
=========================
|| SUBQUERY = OUTER + INNER ||
=========================
STEP1:(INNER QUERY):
********************
SELECT MAX(SAL) FROM EMP;
STEP2:(OUTER QUERY):
********************
SELECT * FROM EMP WHERE SAL=(INNER QUERY);
STEP3: (SUBQUERY= OUTER+INNER):
*******************************
SELECT * FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP);
EX4:
*****
-->WHOSE EMPLOYEE JOB IS SAME AS THE JOB OF 'SMITH' ?
SELECT * FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='SMITH');
EX5:
****
-->WHOSE SALARY IS MORE THAN MAX.SALARY OF THE JOB IS "SALESMAN"?
SELECT * FROM EMP WHERE SAL>(SELECT MAX(SAL) FROM EMP WHERE JOB='SALESMAN');
EX6:
****
-->WHOSE EMPLOYEE JOB IS SAME AS THE JOB OF "BLAKE" AND WHO ARE EARNING SALARY MORE THAN "BLAKE" SALARY ?
SELECT * FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='BLAKE')
AND SAL>(SELECT SAL FROM EMP WHERE ENAME='BLAKE');
EX7:
****
-->DISPLAY SENIOR EMPLOYEE ?
SELECT * FROM EMP WHERE HIREDATE=(SELECT MIN(HIREDATE) FROM EMP);
EX8:
****
-->TO FIND SECOND HIGH. SALARY FROM EMP TABLE ?
SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP)
EX9:
****
-->WASQ TO DISPLAY EMPLOYEE DETAILS WHO ARE GETTING SECOND HIGH. SALARY IN EMP TABLE ?
SELECT * FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE SAL< (SELECT MAX(SAL) FROM EMP));
EX10:
*****
-->DISPLAY EMPLOYEE DETAILS WHO ARE GETTING 3rd HIGH. SALARY IN EMP TABLE ?
-->SELECT * FROM EMP WHERE SAL=
(SELECT MAX(SAL) FROM EMP WHERE SAL<
(SELECT MAX(SAL) FROM EMP WHERE SAL<
(SELECT MAX(SAL) FROM EMP)));
EX11:
*****
-->NO.OF EMPLOYEE OF DEPT. NUMBERS.IN WHICH DEPTNO NO.OF EMPLOYEE IS LESS THAN THE NO.OF EMPLOYEE OF DEPTNO IS 20 ?
SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO
HAVING COUNT(*)<(SELECT COUNT(*) FROM EMP WHERE DEPTNO=20);
EX12:
*****
-->SUM OF SALARY OF JOBS.IF SUM OF SALARY OF JOBS ARE MORE THAN SUM OF SALARY OF THE JOB IS 'CLERK' ?
SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB
HAVING SUM(SAL)>(SELECT SUM(SAL) FROM EMP WHERE JOB='CLERK');
EX13 :
*****
-->DISPLAY EMPLOYEES WHO ARE IN SALES DEPARTMENT
SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');
SUBQUERY WITH "UPDATE" :
*************************
EX13:
*****
-->TO UPDATE EMPLOYEE SALARY WITH MAX.SALARY OF EMP TABLE WHOSE EMPNO IS 7900?
UPDATE EMP1 SET SAL=(SELECT MAX(SAL) FROM EMP1) WHERE EMPNO=7900);
SUBQUERY WITH "DELETE" :
************************
EX14:
*****
-->WASQ TO DELETE EMP1 DETAILS FROM EMP TABLE WHOSE JOB IS SAME AS THE JOB OF 'SCOTT' ?
DELETE FROM EMP1 WHERE JOB=(SELECT JOB FROM EMP1 WHERE ENAME='SCOTT');
MULTIPLE ROW SUBQUERY:
**********************
--> When a subquery returns more than one value is called multiple row subquery.
-->In this subquery we can use the following operators are:
IN,
ANY
ALL
IN OPERATOR:
************
-->The IN operator return TRUE if the comparision value is contained in the list,in this case,the results of the subquery.
EX:
***
The following statement finds the employees whose salary is the same as the minmum salary of the employees in some department.
SELECT ENAME,SAL FROM EMP WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);
EX:
****
SELECT *
FROM EMP
WHERE DEPTNO IN
(
SELECT DEPTNO
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*)>3
);
EX1:
****
-->EMPLOYEE DETAILS WHOSE EMPLOYEE JOB IS SAME AS THE JOB OF THE EMPLOYEE "SMITH","CLARK" ?
SELECT * FROM EMP WHERE JOB IN(SELECT JOB FROM EMP WHERE ENAME='SMITH' OR ENAME='CLARK');
EX2:
*****
-->DISPLAY EMPLOYEE DETAILS WHO ARE GETTING MIN,MAX SALARIES ?
SELECT * FROM EMP WHERE SAL IN(
SELECT MIN(SAL) FROM EMP
UNION
SELECT MAX(SAL) FROM EMP);
EX3:
****
SELECT * FROM EMP WHERE HIREDATE IN(SELECT MIN(HIREDATE) FROM EMP GROUP BY DEPTNO);
EX4:
****
SELECT * FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY JOB);
EX5:
****
SELECT * FROM EMP WHERE SAL IN(SELECT SAL FROM EMP WHERE JOB='CLERK');
WORKING WITH "ANY" , "ALL" OPERATORS:
**************************************
ANY : IT IS USED TO COMPARE ANY OF THE VALUES IN THE GIVEN
LIST.
EX: A > ANY(10,20,30)
A = 40 - TRUE
A = 09 - FALSE
A = 25 - TRUE
A < ANY(10,20,30)
A = 40 - FALSE
A = 09 - TRUE
A = 25 - TRUE
EX:
***
SELECT *
FROM EMP
WHERE SAL>ANY
(SELECT SAL
FROM EMP
WHERE DEPTNO=30
);
/
SELECT *
FROM EMP
WHERE SAL<ANY
(SELECT SAL
FROM EMP
WHERE DEPTNO=30
);
ALL : IS USED TO COMPARE ALL OF THE VALUES IN THE GIVEN LIST.
EX: A > ALL(10,20,30)
A = 40 - TRUE
A = 09 - FALSE
A = 25 - FALSE
A < ALL(10,20,30)
A = 40 - FALSE
A = 09 - TRUE
A = 25 - FALSE
EX:
***
SELECT *
FROM EMP
WHERE SAL>ALL
(SELECT SAL
FROM EMP
WHERE DEPTNO=30
);
/
SELECT *
FROM EMP
WHERE SAL<ALL
(SELECT SAL
FROM EMP
WHERE DEPTNO=30
);
EX6:
****
-->DISPLAY ALL EMPLOYEES,WHOSW SALARY IS MORETHAN ANY 'SALESMAN' SALARY?
SELECT *
FROM EMP
WHERE SAL>ANY
(SELECT SAL
FROM EMP
WHERE JOB='SALESMAN')
EX7:
****
-->DISPLAY ALL EMPLOEES,WHOSE SALARY IS MORETHAN ALL 'SALESMAN' SALARY.
SELECT *
FROM EMP
WHERE SAL>ALL
(SELECT SAL
FROM EMP
WHERE JOB='SALESMAN')
MULTIPLE COLUMN SUBQUERY:
*************************
--> MULTIPLE COLUMNS VALUES OF INNER QUERY COMPARING WITH MULTIPLE COLUMNS VALUES OF OUTER QUERY IS CALLED AS MCSQ.
-->Subquery is going to compare multiple columns with the outer query columns.
-->Return more than one column as it's result.
SYNTAX:
********
SELECT * FROM <TN> WHERE(<COL1>,<COL2>,............)
IN(SELECT <COL1>,<COL2>,....... FROM <TN>);
EX:
***
SELECT * FROM EMP WHERE(JOB,SAL) IN(SELECT JOB,MAX(SAL) FROM EMP GROUP BY JOB);
SELECT * FROM EMP WHERE(JOB,SAL) IN(SELECT JOB,SAL FROM EMP WHERE DEPTNO=10);
EX:
***
SELECT * FROM EMP
WHERE 1=1
AND SAL=(SELECT SAL FROM EMP WHERE ENAME='ALLEN')
AND COMM=(SELECT COMM FROM EMP WHERE ENAME='ALLEN')
EX:
***
SELECT * FROM EMP
WHERE 1=1 AND(SAL,COMM)=(SELECT SAL,COMM FROM EMP WHERE ENAME='ALLEN');
EX:
****
SELECT *
FROM EMP
WHERE (DEPTNO,SAL) IN
(SELECT DEPTNO,MAX(SAL)
FROM EMP
GROUP BY DEPTNO)
EX:
***
-->WAQ TO DISPLAY EMPLOYEE WHOSE JOB,MGR ARE SAME AS THE JOB,MGR OF THE EMPLOYEE "SCOTT" ?
SELECT * FROM EMP WHERE(JOB,MGR) IN(SELECT JOB,MGR FROM EMP WHERE ENAME='SCOTT');
CO-RELATED SUBQUERY:
*******************
-->IN CO-RELATED SUBQUERY FIRST OUTER QUERY IS EXECUTED AND LATER INNER QUERY WILL EXECUTE.
-->Generally in non correlated subquery child queries are executed first where as in correlated subquery parent queries are executed first.
-->In correlated subquery we must create on alias name in parent query table and pass that alias name into child query where clause.
-->Correlated subqueries are used for row by row processing.
-->Each subquery is executed once for every row of outer query.
SYNTAX:
********
SELECT column1, column2, ....
FROM table1 aliasname1
WHERE column1 operator
(SELECT column1, column2
FROM table2 aliasname2
WHERE aliasname2.columnname =
aliasname1.columnname);
/
select *
from emp a
where sal in
(select sal
from emp b
where b.sal=a.sal
);
/
select *
from emp a
where sal in
(select sal
from emp b
where a.sal=b.sal
and b.comm is not null
);
/
--Write a query to find the highest earning employee in each department?
SELECT DEPTNO,
EMPNO,
SAL
FROM EMP E
WHERE 1 =
(
SELECT COUNT(DISTINCT SAL)
FROM EMP E1
WHERE E.DEPTNO = E1.DEPTNO
AND E.SAL <= E1.SAL
);
--FIND ALL EMPLOYEES WHO EARN MORE THAN THE AVERAGE SALARY IN THEIR DEPARTMENT.
SELECT AVG(SAL) FROM EMP WHERE DEPTNO=30;
/
SELECT *
FROM EMP E
WHERE SAL>(SELECT AVG(SAL)
FROM EMP B
WHERE B.DEPTNO=E.DEPTNO
);
/
SYNTAX TO FIND "Nth" HIGH / LOW SALARY:
***************************************
SELECT * FROM <TN> <TABLE ALIAS NAME1> WHERE N-1=(SELECT COUNT(DISTINCT <COLUMN NAME>) FROM <TN> <TABLE ALIAS NAME2> WHERE <TABLE ALIAS NAME2>.<COLUMN NAME> (< / >) <TABLE ALIAS NAME1>.<COLUMN NAME>);
EX1:
****
-->TO FIND OUT EMPLOYEE WHO ARE GETTING FIRST HIGHEST SALARY FROM EMPLOYEE TABE ?
SELECT * FROM EMP E1 WHERE 0=(SELECT COUNT(DISTINCT SAL) FROM EMP E2 WHERE E2.SAL>E1.SAL);
SELECT * FROM EMP E1 WHERE &N=(SELECT COUNT(DISTINCT SAL) FROM EMP E2 WHERE E2.SAL>E1.SAL);
/
SELECT *
FROM EMP E1
WHERE :N=
(SELECT COUNT(DISTINCT SAL)
FROM EMP E2
WHERE E2.SAL>E1.SAL
);
/
SELECT *
FROM EMP E1
WHERE :N=
(SELECT COUNT(DISTINCT SAL)
FROM EMP E2
WHERE E2.SAL<E1.SAL
);
/
EX2:
****
-->TO FIND OUT EMPLOYEE WHO ARE GETTING SECOND HIGHEST SALARY FROM EMPLOYEE TABLE ?
SELECT * FROM EMP E1 WHERE 1=(SELECT COUNT(DISTINCT SAL) FROM EMP E2 WHERE E2.SAL>E1.SAL);
SYNTAX TO DISPLAY "TOP n" HIGH / LOW SALARIES:
============================================
SELECT * FROM <TN> <TABLE ALIAS NAME1> WHERE N>(SELECT COUNT(DISTINCT <COLUMN NAME>) FROM <TN> <TABLE ALIAS NAME2> WHERE <TABLE ALIAS NAME2>.<COLUMN NAME> (< / >) <TABLE ALIAS NAME1>.<COLUMN NAME>);
EX1:
****
-->WAQ TO DISPLAY TOP 3 HIGHEST SALARIES EMPLOYEE DETAILS FROM EMPLOYEE TABLE ?
SELECT * FROM EMP E1 WHERE 3>(SELECT COUNT(DISTINCT SAL) FROM EMP E2 WHERE E2.SAL>E1.SAL);
SELECT * FROM EMP E1 WHERE &N>(SELECT COUNT(DISTINCT SAL) FROM EMP E2 WHERE E2.SAL>E1.SAL);
SELECT *
FROM EMP E1
WHERE 1 =
(SELECT COUNT(*)
FROM EMP E2
WHERE E2.SAL>=E1.SAL);
SELECT *
FROM EMP E1
WHERE &N =
(SELECT COUNT(*)
FROM EMP E2
WHERE E2.SAL>=E1.SAL);
NOTE:
*****
1. TO FIND OUT "Nth" HIGH / LOW SALARY ----------> N-1
2. TO DISPLAY "TOP n" HIGH / LOW SALARIES -----> N>
USING A CORRELATED SUBQUERY IN AN UPDATE:
*****************************************
EX:
***
UPDATE EMP1 E1
SET SAL=(SELECT AVG(SAL) FROM EMP1 E2 WHERE E1.DEPTNO=E2.DEPTNO)
WHERE SAL<(SELECT AVG(SAL) FROM EMP1 E3 WHERE E1.DEPTNO=E3.DEPTNO);
/
USING A CORRELATED SUBQUERY IN A DELETE:
****************************************
DELETE FROM EMP1 E1
WHERE E1.SAL=(SELECT MAX(SAL) FROM EMP1 E2 WHERE E2.DEPTNO=E1.DEPTNO);
EXISTS OPERATOR:
****************
-->IT IS A SPECIAL OPERATOR WHICH IS USED IN CO-RELATED SUBQUERY ONLY.
THIS OPERATOR IS USED TO CHECK WETHER ROW / ROWS EXISTS IN THE TABLE OR NOT.
--> IT RETRUNS EITHER TRUE (OR) FALSE.IF SUBQUERY RETRUNS AT LEAST ONE ROW THEN RETRUNS TRUE OR ELSE IF SUBQUERY NOT RETRUNS ANY ROW THEN RETURN FALSE.
SYNTAX:
*******
WHERE EXISTS(<SELECT STATEMENT>)
NOTE: WHEN WE USE EXISTS OPERATOR IN WHERE CLAUSE CONDITION THERE IS NO NEED TO MENTION A COLUMN NAME BY EXPLICITLY BECAUSE INTERNALLY ORACLE SERVER WILL TAKE COLUMN NAME OF A TABLE BY DEFAULT.
EX1:
****
-->WAQ TO DISPLAY DEPARTMENT DETAILS IN WHICH DEPARTMENT EMPLOYEE ARE WORKING?
SELECT * FROM DEPT D WHERE EXISTS(SELECT DEPTNO FROM EMP E WHERE E.DEPTNO=D.DEPTNO);
EX2:
*****
-->WAQ TO DISPLAY DEPARTMENT DETAILS IN WHICH DEPARTMENT EMPLOYEE ARE NOT WORKING?
SELECT * FROM DEPT D WHERE NOT EXISTS(SELECT DEPTNO FROM EMP E WHERE E.DEPTNO=D.DEPTNO);
/
select *
from po_headers_all poh
where 1=1
and exists
(select pol.po_header_id
from po_lines_all pol
where poh.po_header_id=pol.po_header_id
);
/
select *
from po_headers_all poh
where 1=1
and not exists
(select pol.po_header_id
from po_lines_all pol
where poh.po_header_id=pol.po_header_id
);
/
select *
from ap_invoices_all aia
where exists
(select aila.invoice_id
from ap_invoice_lines_all aila
where aia.invoice_id=aila.invoice_id);
/
select *
from ap_invoices_all aia
where not exists
(select aila.invoice_id
from ap_invoice_lines_all aila
where aia.invoice_id=aila.invoice_id);
No comments:
Post a Comment