Table of Contents
Restricting and Sorting Data in Oracle:
Restricting Data:
We can restrict data in oracle by using WHERE clause in a SQL query. WHERE clause can contain single or multiple condition to restrict data as per user needs.
Restricting Data using numeric column:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID = 30;
Restricting Data using character column:
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE FIRST_NAME = 'Alexander';
Restricting Data using date column:
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID
FROM EMP_SELECT
WHERE HIRE_DATE = TO_DATE('03-JAN-2006', 'DD-MON-RRRR');
Restricting Data using multiple conditions:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID = 30 AND FIRST_NAME = 'Alexander';
Comparison Operators:
| Operators | Description |
|---|---|
| = | Equal to |
| > | Greater than |
| >= | Greater than or equal to |
| < | Less than |
| <= | Less than or equal to |
| <> or != | Not equal to |
Example of Equal to operator (=):
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE EMPLOYEE_ID = 116;
Example of Greater than operator (>):
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE SALARY > 15000;
Example of Greater than or equal to operator (>=):
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE SALARY >= 13000;
Example of Less than operator (<):
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE SALARY < 2500;
Example of Less than or equal to operator (<=):
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE SALARY <= 2200;
SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID FROM DEPARTMENTS WHERE LOCATION_ID <> 1700;
SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID FROM DEPARTMENTS WHERE LOCATION_ID != 1700;
Other comparison operators:
| Operator Name | Description |
|---|---|
| BETWEEN AND | Between two values (inclusive values) |
| NOT BETWEEN AND | Other than between two values (exclusive values) |
| IN | Matching values inside the IN list |
| NOT IN | Matching values outside the IN list |
| LIKE | Match a character pattern |
| NOT LIKE | Not match a character pattern |
| IS NULL | Is a NULL value |
| IS NOT NULL | Is not a NULL value |
Example of BETWEEN AND:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE SALARY BETWEEN 2200 AND 2500;
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE SALARY NOT BETWEEN 2200 AND 24000;
The salaries return are out of the above mentioned range 2200 and 24000.
Example of IN:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID IN (10, 20, 30);
Example of NOT IN:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID NOT IN (10, 20, 30, 40, 50, 60, 70, 80, 90);
Example of LIKE:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE FIRST_NAME LIKE 'John%';
Example of NOT LIKE:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE JOB_ID NOT LIKE 'IT%';
Example of IS NULL:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID IS NULL;
Example of IS NOT NULL:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID IS NOT NULL;
Logical Conditions:
| Condition | Description |
|---|---|
| AND | AND is works with two component conditions. It returns TRUE if both the conditions are TRUE. |
| OR | OR is works with two component conditions. It returns TRUE if either of the conditions is TRUE. |
| NOT | It returns TRUE if the condition is FALSE |
Example of AND logical condition:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID = 30 AND FIRST_NAME = 'Alexander';
Example of OR logical condition:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID = 30 OR FIRST_NAME = 'John';
In the above example data is returned for both the conditions as both the conditions are separately TRUE.
But if we use AND condition then no rows will be returned.
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID = 30 AND FIRST_NAME = 'John';
Example of NOT logical condition:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE NOT DEPARTMENT_ID = 30;
In the above example, Department ID 30 is excluded.
Order of Precedence:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE JOB_ID = 'IT_PROG';
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE JOB_ID = 'ST_MAN';
Let's say we want to show the list of Job ID IT_PROG with salary > 6000 or the list of employees having Job ID as ST_MAN.
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE JOB_ID = 'IT_PROG' OR JOB_ID = 'ST_MAN' AND SALARY > 6000;
But the query returns (all the employees having Job ID IT_PROG) and (all the employees having Job ID ST_MAN whose salary is greater than 6000).
To set the order of precedence we need to add parentheses and the query will be like below:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE (JOB_ID = 'IT_PROG' AND SALARY > 6000) OR JOB_ID = 'ST_MAN';
Now the query is returning the data as per requirement.
Sorting Data:
Sorting in SQL is achieved by ORDER BY clause.
There are two ways to order any statement:
- ASC (Default)
- DESC
ASC:
Ascending order. This is the default option.
Example of ASC:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID = 30 ORDER BY EMPLOYEE_ID;
Or we can write the query in below manner, both will produce the same result.
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID = 30 ORDER BY EMPLOYEE_ID ASC;
In the above example Employee Ids are sorted in ascending order.
DESC:
This will sort the query result in descending order.
Example of DESC:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID = 30 ORDER BY EMPLOYEE_ID DESC;
Sorting by column position:
Instead of using column name we can use numeric column position in ORDER BY clause.
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID = 30 ORDER BY 1;
Now soring the data with Salary column.
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID = 30 ORDER BY 5;
Example of sorting with multiple columns:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID = 30 ORDER BY JOB_ID, FIRST_NAME;
Or we can write:
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID FROM EMP_SELECT WHERE DEPARTMENT_ID = 30 ORDER BY 3, 2;
RELATED TOPICS:
