ORACLE SQL Basic lesson 3
In this lesson we will continue with ORACLE SQL Where clause with more details. Filtering data plays very important role in organization for generating reports or write business logics in SQL or PL / SQL.
Let's re-cap the ORACLE SQL Where clause and get more understanding. There are different data types in Oracle when we retrieve data from tables or source, we should know the data type based on this data type we apply WHERE clause.
The most common data types use in ORACLE are as below
- VARCHAR : It is combination of alpha numeric data it could be name of employees, location, address etc. Always use single Quotation ( ' ). The data inside these single quotation is case sensitive
- Number : It is number with or without decimals. When use in WHERE clause we apply it without any single quotation (')
- Date: Date also use with single quotation ( ' ) the default oracle date format is DD-MON-YYYY
Let's re-cap the where clause
SELECT column1, column2, ...
FROM table_name
WHERE condition;
In WHERE condition we use different operators such as = , != , > , < >= , <= , In , Not In , Like etc. With single or multiple columns name,
We can use logical operators with WHERE clause which are AND , OR
Below is an example of where clause from EMPLOYEE table
-- Get employees with salary greater than 6000
SELECT * FROM employees
WHERE salary > 6000;
-- Get employees with salary not equal to 55000
SELECT * FROM employees
WHERE salary != 55000;
In first SQL WHERE command we try to get data of all employees where Salary is greater then 6000. Note that we are not using any single quotation with the value of 6000 as the data type of this SALARY column is NUMBER. Even if we use single quotation with them oracle database engine automatically convert this
In second example above, we filter the data where salary is not equal to 55,000 means all data appears from employees table without salary of 55,000. In other words it will show data of all employees without those employees who are earning 55,000
* Tip: To check data type you can use command DESC Employees
Filter Data with VARCHAR / VARCHAR2 Data types
Below are some examples of get data with data type VARCHAR. Always use Single quotation in start and end of the value and remember the value is case sensitive e.g. SMITH and Smith are not same.
-- Get employees from employees where job I'd is IT_PROG
SELECT * FROM employees
WHERE job_ID=`IT_PROG`
The above query show the data of all employees who's job I'd is IT Programer. As we wrote job id in upper case because we know it was saved in database as Upper case, If it written like 'it prog' you might get no data.
We will see later how to handle this using SQL Character function if data is in mix case or we don't know data stored in which case.
-- Get employees hired after January 1, 2021
SELECT * FROM employees
WHERE hire_date > '01-jan-2021'
The above ORACLE SQL query shows data of all employees who hired after 01-jan-2021.
-- Get employees hired before July 1, 2022
SELECT * FROM employees
WHERE hire_date = '01-jul-2021'
You can use different operators based on requirements and check the data as well if it match with these requirements.
Below are some more oracle SQL queries need to execute and check the results to get more understanding about oracle SQL where clause
- Select first_name, last_name from employees where department_id in (10,20);
- Select * from departments where department_name = 'IT';
- Select* from departments where manager_id =200 and location_id=1700;
- Select* from departments where manager_id =200 or location_id=1700;
- Select* from employees where hire_date='17-jun-2003';
- Select* from employees where salary between 5000 and 9000;
- Select first_name, last_name from employees where department_id not in (10,20);
Hopefully it will clear the concept of oracle SQL where clause and filter data.
There are many operators might not cover in this lesson. You can search in oracle documentation.
What's Next
In next lesson we will cover oracle SQL character functions
0 Comments