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.