Oracle SQL Tutorials – Chapter 2 (Part 3 of 3)

OPERATOR PRECEDENCE

 

  • Let’s list the employees which JOB_ID is SA_REP or AD_PRES , and have salary more than 15000.
SELECT first_name,last_name, job_id, salary 
FROM hr.employees 
WHERE job_id = 'SA_REP‘ OR job_id = 'AD_PRES‘ AND salary > 15000;

 

This way, all employess which JOB_ID is SA_REP listed as result. We need to add parenthesis to correct this querry ;

SELECT first_name,last_name, job_id, salary 
FROM hr.employees 
WHERE (job_id = 'SA_REP‘ OR job_id = 'AD_PRES‘) AND salary > 15000;

 

ORDER BY OPERATOR

  • The ORDER BY operator is used to put in order the data that returned from querry.
  • ASC : Sorts the data in ascending direction.
  • DESC : Sorts the data in descending direction.
  • The default sorting is ASC. If you don’t write any or them, the data will be sorted in ascending direction.
  • Let’s list the employees by the hir_date in ascending direction, older to newer.
SELECT first_name,last_name, job_id, department_id, hire_date "Hire Date“ 
FROM hr.employees 
ORDER BY hire_date ASC ;

 

  • Let’s find the yearly salary of employees and list them by the salary in descending direction.
SELECT employee_id, first_name,last_name, salary*12 "Yearly Salary“
FROM hr.employees
ORDER BY 4 desc ;

 

ASSIGNING VARIABLE

  • Let’s list the employees which Department ID is 90, using variable.
SELECT employee_id, last_name, salary, department_id
FROM hr.employees
WHERE department_id = &department_num ;

One thought on “Oracle SQL Tutorials – Chapter 2 (Part 3 of 3)

Leave a Reply

Your email address will not be published. Required fields are marked *