Hi Friends,

Lets understand the Sub queries Which is an important concept in SQL.



Sub query:-A sub query is a SELECT statement which is used in another SELECT statement. Sub queries are very useful when you need to select rows from a table with a condition that depends on the data of the table itself. You can use the sub query in the SQL clauses including WHERE clause, HAVING clause, FROM clause etc.

The sub query can also be referred as nested SELECT, sub SELECT or inner SELECT. In general, the sub query executes first and its output is used in the main query or outer query.

Types of Sub queries:-

There are two types of sub queries in oracle:
Single Row Sub queries: The sub query returns only one row. Use single row comparison operators like =, > etc while doing comparisons.
SELECT * from emp where sal=(select max(sal) from emp);
Multiple Row Sub queries: The sub query returns more than one row. Use multiple row comparison operators like IN, ANY, ALL in the comparisons.
SQL> SELECT * from emp where sal IN ( select max(sal) from emp group by deptno);
<ANY:-It means less than the maximum value from the list.
>ANY:- It means greater than the minimum value from the list.
<ALL:-It means less than the minimum value from the list.
>ALL:- It means greater than the maximum value from the list.

Single Row Sub query Examples

1. Write a query to find the salary of employees whose salary is greater than the salary of employee whose id is 100?
SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY >
    (
  SELECT SALARY
  FROM EMPLOYEES
  WHERE EMPLOYEED_ID = 100
  )

2. Write a query to find the employees who all are earning the highest salary?
SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY =
  (
  SELECT  MAX(SALARY)
  FROM EMPLOYEES
  )

3. Write a query to find the departments in which the least salary is greater than the highest salary in the department of id 200?
SELECT DEPARTMENT_ID,
 MIN(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MIN(SALARY) >
  (
  SELECT MAX(SALARY)
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 200
  )

Multiple Row Sub query Examples

1. Write a query to find the employees whose salary is equal to the salary of at least one employee in department of id 300?
SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY IN
  (
  SELECT  SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 300
  )

2. Write a query to find the employees whose salary is greater than at least on employee in department of id 500?
SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY > ANY
  (
  SELECT  SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 500
  )

3. Write a query to find the employees whose salary is less than the salary of all employees in department of id 100?
SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY < ALL
  (
  SELECT  SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 100
  )

4. Write a query to find the employees whose manager and department should match with the employee of id 20 or 30?
SELECT EMPLOYEE_ID,
 MANAGER_ID,
 DEPARTMENT_ID
FROM EMPLOYEES
WHERE (MANAGER_ID,DEPARTMENT_ID) IN
  (
  SELECT MANAGER_ID,
   DEPARTMENT_ID
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID IN (20,30)
  )

5. Write a query to get the department name of an employee?
SELECT EMPLOYEE_ID,
 DEPARTMENT_ID,
 (SELECT DEPARTMENT_NAME
 FROM DEPARTMENTS D
 WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
 )
FROM EMPLOYEES E
NOTE:- We can nested the sub query till 255 select stmt.
What is oracle correlated sub-queries? Explain with an example.
A query which uses values from the outer query is called as a correlated sub query. The sub query is executed once and uses the results for all the evaluations in the outer query.
Example:
Here, the sub query references the employee_id in outer query. The value of the employee_id changes by row of the outer query, so the database must rerun the subquery for each row comparison. The outer query knows nothing about the inner query except its results.
select employee_id, appraisal_id, appraisal_amount From employee
where
appraisal_amount < (select max(appraisal_amount)
from employee e
where employee_id = e. employee_id);
Correlated SubQueries Examples

Correlated sub query is used for row by row processing. The sub query is executed for each row of the main query.

1. Write a query to find the highest earning employee in each department?
SELECT DEPARTMENT_ID,
 EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES E_0
WHERE 1 =
  (
  SELECT  COUNT(DISTINCT SALARY)
  FROM EMPLOYEES E_I
  WHERE E_O.DEPARTMENT_ID = E_I.DEPARTMENT_ID
  AND E_O.SALARY <=  E_I.SALARY
  )

2. Write a query to list the department names which have at lease one employee?
SELECT DEPARTMENT_ID,
 DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE EXISTS
 (
 SELECT 1
 FROM EMPLOYEES E
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)

3. Write a query to find the departments which do not have employees at all?
SELECT DEPARTMENT_ID,
 DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE NOT EXISTS
 (
 SELECT  1
 FROM EMPLOYEES E
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)


Comments

Post a Comment

Popular posts from this blog

How to Customize AP Invoice Approval in Oracle Fusion cloud Application using Business Process Management(BPM)

Image
Dear Viewers ,   Lets Understand with different case studies How to Customize AP Invoice Approval in Oracle Fusion cloud Application using Business Process Management(BPM) .   Case Study 1 #Configure an approval rule to route a notification to a requestor  It covers the most common business use cases of invoice approval in a Payables Department.   Each business use case provides how to configure the invoice approval rules, using various participants, invoice attributes, and list builders such as:   • Single Approver   • Approval Group   • Supervisor Hierarchy   • Job level Hierarchy   • Position Hierarchy   The information presented in this document is intended to help various users (Payables business users, Approval workflow administrators and Consultants) who are involved in the configuration of invoice approval policy to meet the various business use cases of invoice approvals in a Payables Department.  Note: 1) Refer to the Appendix section for navigation details for configuring in
Read more
Image
Sometime , We need to know specific relationship between some important tables . Lets come to know one such important SQL Query to know the relationship between Transfer Order, Pick Slip Number, and Supply Orchestration SQL Query can be used to show the link between a Transfer Order, Pick Slip Number, and Supply Orchestration Belo is the screenshot for instance    The Required output We need from the report is below  The Query used for above requirement is below  SELECT  a.pick_slip_number ,   b.source_header_number transfer_order_number,   c.supply_order_number ,   c.supply_order_reference_number FROM   INV_PICK_SLIP_NUMBERS a ,   WSH_DELIVERY_DETAILS b ,   DOS_SUPPLY_HEADERS c ,   INV_TRANSFER_ORDER_HEADERS d,   INV_TRANSFER_ORDER_LINES e WHERE 1 =1   AND a.PICK_SLIP_NUMBER = 11022   AND a.pick_slip_batch_id = b.batch_id   AND b.source_header_number = d.header_number   AND d.header_id = e.header_id   AND e.source_header_id = c.header_id Thank you , Please do comment and share here .
Read more
Dear Friends ,  Below Query can be useful to find the Unit cost of Item at costing Module . We can have  FOR Unit cost OF Material under specified Val Unit code AND Cost Book SELECT TO_CHAR ( CICT . total_cost , 'fm99999999d00' ) UNIT_COST FROM CST_ITEM_COSTS_V CICT , CST_TRANSACTIONS CT WHERE 1 = 1 AND CICT . ITEM_NUMBER = :P_Item_Number AND CICT . TRANSACTION_ID = CT . TRANSACTION_ID AND TRUNC ( CICT . PREVIOUS_COST , 6 ) = ( SELECT TRUNC ( UNIT_COST_AVERAGE , 6 ) FROM cst_perpavg_cost WHERE PERPAVG_COST_ID = ( SELECT MIN ( PERPAVG_COST_ID ) FROM cst_perpavg_cost WHERE TRANSACTION_ID = CICT . TRANSACTION_ID ) ) AND CICT . VAL_UNIT_CODE LIKE 'XXXXXX' AND TO_CHAR ( CT . TRANSACTION_DATE , 'DD-MM-YYYY' ) BETWEEN TO_CHAR ( :P_START_DATE , 'DD-MM-YYYY' ) AND TO_CHAR ( :P_END_DATE , 'DD-MM-YYYY' ) And If you want to have the Query to fetch Current Item cost
Read more