Comparison Conditions
| = | > | >= | < | <= | <> | BETWEEN…AND… | IN(set) | LIKE | IS NULL |
We can display rows based on a range of values using the BETWEEN range condition. The range that we specify contains a lower limit and an upper limit.
To test for values in a specified set of values, use the IN condition. The IN condition is also known as the membership condition.
We may not always know the exact value to search for. We can select rows that match a character pattern by using the LIKE condition. The character pattern-matching operation is referred to as a wildcard search. Two symbols can be used to construct the search string. Ie. %(zero or more characters) and _ (single character).
The NULL conditions include the IS NULL condition and the IS NOT NULL condition. The IS NULL condition tests for nulls. A null value means the value is unavailable, unassigned, unknown, or inapplicable. Therefore, we cannot test with = because a null cannot be equal or unequal to any value.
- select empno, ename,job, deptno from emp where deptno=20;
- select empno, ename, salary from emp where lastname=’Lama’;
- select * from emp where hiredate=’01-JAN-95’ and salary>=5000 and lastname=’Smith’;
- select ename,slary from emp where salary between 2300 and 3500;
- select empno,ename,salary,mgr from emp where deptno in(20,30,40);
- select ename,hiredate from emp where hiredate like ‘%95’;
- select ename from emp where ename like ‘_e%’;
- select ename,mgr from emp where mgr is null;
- select ename,salary,comm. from emp where comm. is null;
- select empno, ename,job,salary from emp where salary>=1000 and job like ‘SYS%’;
- select empno, ename,job,salary from emp where salary>=1000 or job like ‘SYS%’;
- select empno, ename from emp where job in (‘MANAGER’, ‘CLERK’, ‘PEON’);
The order of rows returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. If we use the ORDER BY clause, it must be the last clause of the SQL statement. We can specify an expression, or an alias, or column position as the sort condition. Syntax:
SELECT expression
FROM table
WHERE conditions
ORDER BY columns [ASC|DESC];
If the ORDER BY clause is not used, the sort order is undefined, and the oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order.
To reverse the order in which rows are displayed, specify the DESC keyword after the column name in the ORDER BY clause.
- select ename, job, depatno, hiredate from emp order by hiredate;
- select ename, job ,deptno, hiredate from emp order by hiredate desc;
- select empno, ename, salary, salry*12 Annsal from emp order by annsal;
- select ename, deptno, salary from emp order by deptno,salary desc;
