Tree Query , Hierarchical Query in SQL

  In this lesson ,

We need to get the data from table has self foreign key 

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

in past lessons we get data from one table or many tables joined to each other by foreign key , but now we will get the data from table has self foreign key like EMPLOYEES ,

as you can see employee has manager , this manager is (of course) an employee

in EMPLOYEES table , you can see Foreign key on MANAGER_ID refer to EMPLOYEE_ID

let we need to know the name of employee and his manager as well , we can get this information by query like this 👇

SELECT e.EMPLOYEE_ID , e.FIRST_NAME ||' '|| e.LAST_NAME   FULL_NAME ,

m.FIRST_NAME ||' '|| m.LAST_NAME  AS "Manager Name"

FROM HR.EMPLOYEES e

LEFT JOIN  HR.EMPLOYEES  m ON e.MANAGER_ID =  m.EMPLOYEE_ID

ORDER BY 1

using same data , we need to list this data in more formal layout (shape) , this is known as Tree Query , Hierarchical Query

and this can be like this 👇

SELECT

    LEVEL,

    employee_id,

    first_name || ' ' || last_name AS employee_name,

    manager_id

FROM hr.employees

START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id

Explanation of Tree Query , Hierarchical Query

to understand this query , we must know the keys of any Tree Query , Hierarchical Query , we can list them as 👇

• LEVEL → hierarchy depth
• START WITH → from which node of tree will need to start ( here root node is CEO / top manager)
• CONNECT BY , PRIOR → what is the relation is the tree , which is parent node, which is child node , PRIOR , means the parent node

The above query get all employees followed by the staff under this employee

in this result we can see the following ,

level 1 is employee has no manager (top management )

level 3 has many employees under him (employee 101 is manager of 5 employees , and they appear on level 4)
connect by was CONNECT BY PRIOR employee_id = manager_id , which means list employees followed by staff under his managment

BUT

if we need to get the tree by each employee and all managers above of him the query will be

SELECT

    LEVEL,

    employee_id,

    first_name || ' ' || last_name AS employee_name,

    manager_id

FROM hr.employees

START WITH employee_id = 113

CONNECT BY  employee_id =PRIOR  manager_id

in this way , we get tree start from certain employee (113) , and list all manager above him until Steven (which has no manager)

this type of query is very important , and the best practice of it is when applying workflow depend on the hierarchical relationship

now think about get all employee under management of employee has id = 114 , take your time ⌚ ⌚ ⌚

if you write it as this 👇 , you are right 👏

SELECT

    LEVEL,

    employee_id,

    first_name || ' ' || last_name AS employee_name,

    manager_id

FROM hr.employees

START WITH employee_id = 114

CONNECT BY PRIOR employee_id = manager_id

yes , you did it , as we agreed , when understand the logic of data + the right syntax , you can write the right query in easy way

in the Hierarchical Query , we can use WHERE clause as well , to filter the output data ,

let we want to get the employee in tree format with only salary > 12000 like this 👇

SELECT

    LEVEL,

    employee_id,

    first_name || ' ' || last_name AS employee_name,

    manager_id , SALARY

FROM hr.employees

WHERE SALARY > 12000

START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id

for more information about Hierarchical Query , you can see this page

in this lesson we talk about Tree Query , Hierarchical Query ,

in coming lessons we will talk about WHERE clause with CASE statement in Oracle

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. 

Aggregate Functions (Group Functions) , HAVING clause in SQL

 In this lesson ,

We need to get valuable data by join multiple tables 

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 EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME ,

DEPARTMENTS.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES , HR.DEPARTMENTS

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

ORDER BY 2 

which list data of employee and related department name 

Let's we need to get how many employees in each department 

we can write query like this 👇

SELECT COUNT(EMPLOYEE_ID) "Count of Employees" ,

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

Explanation of Query :

we get "how many employees" using COUNT function , for more information about COUNT , you can see this page

now , let we want to know MAXIMUM salary in each department , we will write this 👇 query

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

by same concept , we can get MINIMUM, AVERAGE , TOTAL (SUMMATION) of salary per each department

you can use this by yourself using MIN(SALARY) , AVG(SALARY) , SUM(SALARY) , it is amazing to get valuable information when know more in SQL

for more information about aggregate function , you can see this page

as we use aggregate function , we must talk about HAVING clause .

firstly what is HAVING clause ? simply , this clause filters /restricts data by aggregate function ,

or we can say ,The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list

for example , let's try to get department name which only has max salary less than 10000 using this 👇query

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

as you see here , only 4 rows are selected , which match the condition MAX(SALARY) < 10000

hint1 : condition in HAVING clause must use the same used aggregate function in such query , as HAVE will filter data coming by execute the query itself

hint2 : HAVING clause must be used with GROUP BY clause

in this lesson , we take a look at Aggregate Function , HAVING clause in oracle SQL ,

and in next lesson we will use Subqueries to get more valuable information. 

JOIN multiple tables , Foreign Key in SQL

 In this lesson ,

We need to get data by join multiple tables 

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 EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME , DEPARTMENT_ID

FROM HR.EMPLOYEES

where DEPARTMENT_ID = 30

which list data of employee and his/her department_id 

Let's we need to get department name instead of department_id , to get data with direct meaning

we can write query like this 👇

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME ,

DEPARTMENTS.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES , HR.DEPARTMENTS

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

ORDER BY 2 

Meaning of the condition  

HR.EMPLOYEES.DEPARTMENT_ID = HR.DEPARTMENTS.DEPARTMENT_ID leads us to explain the foreign key between 2 tables ,  in EMPLOYEES table we see the column DEPARTMENT_ID , this column refer to the DEPARTMENT_ID in table DEPARTMENTS

and this relation is called Foreign Key which means primary key in one table can be called in another table

Important Role :

( any Foreign Key must be Primary Key in its table , as any ambassador is important in his country. )

this join condition is used to get only data matches the condition , and can be written as 👇 too

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME ,

d.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES e

INNER JOIN  HR.DEPARTMENTS d ON E.DEPARTMENT_ID =  D.DEPARTMENT_ID

ORDER BY 2

• to get all data from 2 tables even match condition or not , we can write this 👇 query

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME ,

d.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES e

FULL OUTER JOIN  HR.DEPARTMENTS d ON E.DEPARTMENT_ID =  D.DEPARTMENT_ID

ORDER BY 3 DESC

• to get all employees, even those who haven't been assigned to a department yet (like a new hire), we can write this 👇 query

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME ,

d.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES e

LEFT OUTER JOIN  HR.DEPARTMENTS d ON E.DEPARTMENT_ID =  D.DEPARTMENT_ID

ORDER BY 3 DESC

Hint 1 :

you must know the Old Oracle Syntax (+) , as you may say it in any SQL code

E.DEPARTMENT_ID =  D.DEPARTMENT_ID(+) was used instead of LEFT OUTER JOIN  HR.DEPARTMENTS d ON E.DEPARTMENT_ID =  D.DEPARTMENT_ID







• to get all departments, including those that currently have no employees assigned to them , we can write this 👇 query

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME ,

d.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES e

RIGHT OUTER JOIN  HR.DEPARTMENTS d ON E.DEPARTMENT_ID =  D.DEPARTMENT_ID

ORDER BY 3 DESC

Hint 2:

you must know the Old Oracle Syntax (+) , as you may say it in any SQL code

E.DEPARTMENT_ID(+)=  D.DEPARTMENT_ID was used instead of RIGHT OUTER JOIN  HR.DEPARTMENTS d ON E.DEPARTMENT_ID =  D.DEPARTMENT_ID

finally ,

as we list JOIN conditions , we must talk about the SELF-JOIN which is related to the Organizational Tree ,

what the mean of this is foreign key is in same table itself , like manger in table EMPLOYEES , is basically an employee

let's we need to get all employees and their manager's name , we can write query like this 👇

SELECT e.EMPLOYEE_ID , e.FIRST_NAME ||' '|| e.LAST_NAME   FULL_NAME ,

m.FIRST_NAME ||' '|| m.LAST_NAME  AS "Manager Name"

FROM HR.EMPLOYEES e

LEFT JOIN  HR.EMPLOYEES  m ON e.MANAGER_ID =  m.EMPLOYEE_ID

ORDER BY 1

for more information about JOIN , you can see this page

for more information about Foreign Key , you can see this page

in this lesson , we take a look at JOIN , Foreign Key in oracle SQL , and in next lesson we will use aggregating function to get more valuable information.