Subqueries

Used sample EMP and DEPT tables from https://livesql.oracle.com


SUB QUERIES : 


1. Query to find the salary of employees whose salary is greater than the salary of employee whose id is 7698?

SELECT SAL FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE EMPNO=7698);

2.

Correlated Sub Queries 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
  )

SELECT EMPNO,ENAME,E1.DEPTNO ,SAL FROM EMP E1 
WHERE SAL = (SELECT MAX(SAL) FROM EMP E2 WHERE E2.DEPTNO = E1.DEPTNO 

             GROUP BY E2.DEPTNO) ;

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)