SQL of Oracle Learning (2) Restricting and Sorting Data

Restrict the return row of a SELECT statement

Syntax:

Use the WHERE keyword to restrict the return of data rows, with the WHERE clause following the FROM clause.

SQL > select * from emp where deptno=10 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -- 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

When the expression after WHERE is true, the row is returned, otherwise line breaks are skipped.

SQL > select * from emp where ename='KING' EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -- 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

Strings are case sensitive

SQL > select * from emp where ename='king';no rows selected

The comparison operators supported by conditional expressions are as follows

SQL > select * from emp where sal select * from emp where sal between 1500 and 2000 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -- 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

IN returns rows as long as the value of the column appears in the list

SQL > SELECT * FROM EMP WHERE ENAME IN ('SMITH','KING')

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

LIKE fuzzy query

Query all people whose names begin with S.

SQL > select * from emp where ename like'S%' EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 80020 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

% wildcard, indicating 0 or more arbitrary characters

Query all the people whose names contain S.

SQL > select * from emp where ename like'% S%' EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 80020 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7876 ADAMS CLERK 7788 1987 / 05/23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

The _ wildcard represents any character

Query the person whose name's second character is O

SQL > select * from emp where ename like'_ O%' EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -- 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

What if the value of the column itself contains% or _ characters? Escape with the escape keyword.

Query the person whose name contains _

SQL > select * from emp where ename like'%\% 'escape'\'; no rows selected

Filter null value

Filtering NULL values cannot be queried using =. Null has a proprietary judgment condition IS NULL.

SQL > SELECT * FROM EMP WHERE COMM IS NULL EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 80020 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981 JONES MANAGER 06 / 09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10