In this Oracle tutorial, we will talk about how the where clause uses different Number data types as filters. Let us go through different ways we can filter out number data with some practical examples.
This page contains (Skip to topic)..
Where Clause Equals To Number
We can use the “equals to” operator to compare the column value with a number. The syntax for the same would be,
SELECT * FROM table_name WHERE column_name = 99999;
We can use this to pull out employee details by their ID. The Oracle query for the same would be,
SELECT * FROM employees
WHERE employee_id = 100;
This query will fetch the row with employee id as 100. Hence, the result would be as follows.
Where clause greater than a number
We can use the greater than operator to find all employees with the salary more than 13000. The query would be as follows,
SELECT * FROM employees WHERE salary > 13000;
We can also use greater than equals to if we want the number to be included in the result. This is described in the below section.
Where clause greater than equals to a number
Let us say we want to find out all the jobs with min salary greater than or equals to 5500.
SELECT * FROM jobs WHERE min_salary >= 5500;
Where clause less than a number
To find out all the employees with maximum salary less than 16000, you can use the following query.
SELECT * FROM jobs WHERE max_salary < 16000;
Where clause less than equals to a number
Now, let us find out all the jobs whose max_salary is less than 16000 or equal to 16000. We just need the less than equals to operator instead of the less than in the above query.
SELECT * FROM jobs WHERE max_salary <= 16000;
Where clause does not equal to a number
We can use Oracle not equals to operator with the numbers to exclude certain occurrence. So, if we want to select all the departments except the ones having location if 1700 then the query will be as shown below.
SELECT * FROM departments WHERE location_id <> 1700;
Where clause between and numbers
We can filter rows based on if a numeric value falls in a range. We can use the between and operator for the same.
SELECT * FROM employees WHERE salary BETWEEN 6000 AND 7000;
The above query fetches the employees’ records whose salary is between 6000 and 7000 inclusive of the boundries.