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. 

WHERE clause , ORDER BY clause in SQL

In this lesson ,

We need to filter data to get more specified information, using WHERE , ORDER BY 

in past lesson we get data using this simple query 

    SELECT *

    FROM HR.EMPLOYEES

Using Oracle Free SQL  , 

• Select HR schema to connect to all HR tables 

 , let's we need to get employees who first name is John   , we will Write new query like this 👇 

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

, TO_CHAR(HIRE_DATE,'DD Month YYYY')  as "Hire Date"

FROM HR.EMPLOYEES

WHERE FIRST_NAME = 'John'

    

well ,  WHERE can help in filter data , specially when join some tables (will be in coming lesson)

for now we can write some query to understand the WHERE , like  : 

• filter employees who have name start with J 

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

, TO_CHAR(HIRE_DATE,'DD Month YYYY')  as "Hire Date"

FROM HR.EMPLOYEES

where FIRST_NAME like  'J%'

if you don't know LIKE clause , you can look at this help page

• filter employees who working over 10 years 

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

, TO_CHAR(HIRE_DATE,'DD Month YYYY')  as "Hire Date" , round(months_between(sysdate,HIRE_DATE)/12) as "Working Years"

FROM HR.EMPLOYEES

where round(months_between(sysdate,HIRE_DATE)/12)> 10

if you don't know MONTHS_BETWEEN , you can look at this help page

in selecting data , mostly we need to order it in certain layout , if we need employees to be appeared 

in Descending Order of working year we can add ORDER BY as 👇

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

, TO_CHAR(HIRE_DATE,'DD Month YYYY')  as "Hire Date" , round(months_between(sysdate,HIRE_DATE)/12) as "Working Years"

FROM HR.EMPLOYEES

where round(months_between(sysdate,HIRE_DATE)/12)> 10

ORDER BY round(months_between(sysdate,HIRE_DATE)/12) DESC

which is same as 👇 , 

we can order by the position of needed column instead of write it ( specially when it is a format like this )

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

, TO_CHAR(HIRE_DATE,'DD Month YYYY')  as "Hire Date" , round(months_between(sysdate,HIRE_DATE)/12) as "Working Years"

FROM HR.EMPLOYEES

where round(months_between(sysdate,HIRE_DATE)/12)> 10

ORDER BY 4 DESC

if you don't know ORDER BY , you can look at this help page

• filter employees who work in certain department (we will filter by department name in coming lesson )

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

FROM HR.EMPLOYEES

where DEPARTMENT_ID = 30

• filter employees who work as certain job 

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME , JOB_ID AS "Job Title"

FROM HR.EMPLOYEES

where JOB_ID = 'IT_PROG'

which is same as 👇 , to know more about character formatting ,  you can look at this help page

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME , JOB_ID AS "Job Title"

FROM HR.EMPLOYEES

where lower(JOB_ID) = 'it_prog'

in this lesson , we take a look at WHERE clause , ORDER BY clause in SQL , and in next lesson we can join tables to get more specific information.

Your First SQL statement :)

 Using Oracle Free SQL  , 

• Select HR schema to connect to all HR tables 
• Write simple query like this 👇

    SELECT *

    FROM HR.EMPLOYEES

• Press RUN button 
• you will get the result like this 👇

        

• you can select certain data like this 👇

    SELECT EMPLOYEE_ID , FIRST_NAME , LAST_NAME

    FROM HR.EMPLOYEES   

• you will get the result like this 👇

 

Explanation of code:

as you see EMPLOYEES table has many columns (data) , like

EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,

JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID

you can select data by SELECT statement , the used SQL is simple one to get data from database

you can select data from one table , or many tables as will know later

you can get data as it is or format the result as well

you can filter data as needed as will know later

now , assume we need to get employees' full name ,

we can write SQL as this 👇 using symbol || which is the symbol to concatenate 2 string(character)

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

FROM HR.EMPLOYEES

as you see , we can set alias for the selected data , here FULL_NAME is called alias ,

which means a readable title for selected data

let's we need an alias with space like Hire Date , in this case we can use it inside "" and use AS clause as will see soon

now , we need to get full name , hire date in format of "dd Month yyyy" or any valid format , do you know how to get that ?

if you don't know ways to convert date to needed format , you can look at this help page

if you know how to format date , it is easy to write the SQL which will be

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

, TO_CHAR(HIRE_DATE,'DD Month YYYY')  as "Hire Date"

FROM HR.EMPLOYEES

here we use alias in 2 ways , just new title like FULL_NAME which is common used in programming ,

and new title with space like Hire Date , using as "Hire Date" , which is used for extract data to external files

the result will be like this 👇

this is how to write simple SQL statement, and in next post we will filter data using WHERE clause

Live Oracle SQL - Free SQL

 You can use Live Oracle SQL to edit and execute SQL / PL-SQL on predefined schemas from Oracle like HR , SALES , and others 

you can choose , Start Coding / View Scripts and Tutorials 

by choose Start Coding ,

• 1 : hint to inform you this live SQL has deadtime

• 2: you can choose from predefined schemas , and it will load relative tables as well , here we select HR schema

   

• 3: write your SQL / PL-SQL 

• 4: get the result when run (3👆) 

• 5: your library on the FreeSQL website (keep in mind the deadtime 😢)

enjoy by try all features of latest Oracle DB , without installing it , just select DB need to connect with 👇