Sub queries in SQL

  In this lesson ,

We need to get valuable data by Subqueries 

Using Oracle Free SQL  , by select HR schema to connect to all HR tables 

in past lesson we get data using a query like 

SELECT MAX(SALARY) "Max Salary per Department" ,

DEPARTMENTS.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES , HR.DEPARTMENTS

where HR.EMPLOYEES.DEPARTMENT_ID =  HR.DEPARTMENTS.DEPARTMENT_ID

group by DEPARTMENT_NAME

having MAX(SALARY) < 10000

ORDER BY 2

SELECT MAX(SALARY) "Max Salary per Department" ,

DEPARTMENTS.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES , HR.DEPARTMENTS

where HR.EMPLOYEES.DEPARTMENT_ID =  HR.DEPARTMENTS.DEPARTMENT_ID

group by DEPARTMENT_NAME

ORDER BY 2

In this query we get the max salary in each department with condition (this max less than 10000)

remember this equivalent to use this

(INNER JOIN HR.DEPARTMENTSON HR.EMPLOYEES.DEPARTMENT_ID = HR.DEPARTMENTS.DEPARTMENT_ID)

Now , what if we need to know who (the employee name) has this max salary in each department ,

in this case we will use subqueries like this 👇

SELECT  HR.DEPARTMENTS.DEPARTMENT_NAME AS "Department Title"

,HR.EMPLOYEES.FIRST_NAME ||' '|| HR.EMPLOYEES.LAST_NAME AS "Employee Name"

,HR.EMPLOYEES.SALARY

FROM HR.EMPLOYEES , HR.DEPARTMENTS

where HR.EMPLOYEES.DEPARTMENT_ID =  HR.DEPARTMENTS.DEPARTMENT_ID

AND SALARY = (

SELECT MAX(SALARY) "Max Salary per Department"

FROM  HR.EMPLOYEES A

where HR.DEPARTMENTS.DEPARTMENT_ID =  A.DEPARTMENT_ID

group by A.DEPARTMENT_ID )

ORDER BY 3 DESC

Here , as you see , we get salary = result of sub query ,

for your information , subqueries can be written by using IN , ANY , and ALL

as we will see soon , in another cases we can use them (in case we need data match multi records)

let we need to get name of all employees and his department match the job "AC_ACCOUNT" in the past

we can see this case in query like this 👇

SELECT  HR.DEPARTMENTS.DEPARTMENT_NAME AS "Department Title"

,HR.EMPLOYEES.FIRST_NAME ||' '|| HR.EMPLOYEES.LAST_NAME AS "Employee Name"

FROM HR.EMPLOYEES , HR.DEPARTMENTS

where HR.EMPLOYEES.DEPARTMENT_ID =  HR.DEPARTMENTS.DEPARTMENT_ID

AND HR.EMPLOYEES.EMPLOYEE_ID IN (

SELECT EMPLOYEE_ID

FROM  HR.JOB_HISTORY A

where A.JOB_ID = 'AC_ACCOUNT')

here , as you see , we use IN to get all matches in subquery , by same concept we can get data from several tables by matching data in needed format

can you write simple query to get all employees work in company in period from start of 2011 to end of 2015 ?

take your time ⌚⌚⌚

if you write it like this , you are right 👏

SELECT  HR.DEPARTMENTS.DEPARTMENT_NAME AS "Department Title"

,HR.EMPLOYEES.FIRST_NAME ||' '|| HR.EMPLOYEES.LAST_NAME AS "Employee Name"

FROM HR.EMPLOYEES , HR.DEPARTMENTS

where HR.EMPLOYEES.DEPARTMENT_ID =  HR.DEPARTMENTS.DEPARTMENT_ID

AND HR.EMPLOYEES.EMPLOYEE_ID IN (

SELECT EMPLOYEE_ID

FROM  HR.JOB_HISTORY A

where A.START_DATE >= TO_DATE('01-01-2011','DD-MM-YYYY')

AND A.END_DATE <= TO_DATE('31-12-2016','DD-MM-YYYY'))

you did it !!

yes it is easy to write SQL , just understand what you want to write logical statement

as you see , we used = to get matching with exactly 1 record , IN to get matching of multiple records ,

and now we will have a look on another usage of subquery

if we know salary in department 50 are in range 2100 - 8200

let we need to know employees has salary > any salary of this department

(Salary is greater than the minimum salary in department 50) , we will use ANY condition

SELECT

    HR.EMPLOYEES.FIRST_NAME ||' '|| HR.EMPLOYEES.LAST_NAME AS "Employee Name" ,

    SALARY

FROM HR.EMPLOYEES

WHERE salary > ANY (

    SELECT salary

    FROM hr.employees

    WHERE department_id = 50 )

ORDER by 2 ;

BUT , if we need to get Salary is greater than the maximum salary in department 50 , in this case we will use

ALL condition

SELECT

    HR.EMPLOYEES.FIRST_NAME ||' '|| HR.EMPLOYEES.LAST_NAME AS "Employee Name" ,

    SALARY

FROM HR.EMPLOYEES

WHERE salary > ALL (

    SELECT salary

    FROM hr.employees

    WHERE department_id = 50);

as a summery of the subqueries , we can list this table

Operator	Meaning
IN	Equal to any value in the list
> ANY	Greater than minimum
< ANY	Less than maximum
> ALL	Greater than maximum
< ALL	Less than minimum

=                           Match Exact 1 record 

HINT : You can nest up to 255 levels of subqueries in the WHERE clause.

in this lesson , we take a look at subqueries in oracle SQL ,

and in next lesson we will use hierarchical tree query to get more valuable information.