Less02Restricting and Sorting Data

After completing this lesson, you should be able to do the following:

Limit the rows that are retrieved by a query

Sort the rows that are retrieved by a query

Use ampersand substitution in iSQL*Plus to restrict and sort output at run time 

Character strings and date values are enclosed by single quotation marks.

Character values are case-sensitive, and date values are format-sensitive.

The default date format is DD-MON-RR.

SELECT employee_id, last_name, job_id, department_id

FROM   employees

WHERE  department_id = 90 ;

SELECT last_name, salary

FROM   employees

WHERE  salary <= 3000 

Use the BETWEEN condition to display rows based on a range of values:

SELECT last_name, salary

FROM   employees

WHERE  salary BETWEEN 2500 AND 3500 ;

Use the IN membership condition to test for values in a list:

SELECT employee_id, last_name, salary, manager_id

FROM   employees

WHERE  manager_id IN (100, 101, 201) ;

SELECT last_name

FROM   employees

WHERE  last_name LIKE '_o%' 

 Test for nulls with the IS NULL operator.

SELECT last_name, manager_id

FROM   employees

WHERE  manager_id IS NULL ;

AND requires both conditions to be true:

SELECT employee_id, last_name, job_id, salary

FROM   employees

WHERE  salary >=10000

AND    job_id LIKE '%MAN%' ;

OR requires either condition to be true:

SELECT employee_id, last_name, job_id, salary

FROM   employees

WHERE  salary >= 10000

OR     job_id LIKE '%MAN%' ;

SELECT last_name, job_id

FROM   employees

WHERE  job_id

       NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;

SELECT last_name, job_id, salary

FROM   employees

WHERE  job_id = 'SA_REP'

OR     job_id = 'AD_PRES'

AND    salary > 15000;

 SELECT   last_name, job_id, department_id, hire_date

FROM     employees

ORDER BY hire_date ;