Restricting and Sorting Data

When retrieving data from the database, you may need to do the following:

• Restrict the rows of data that are displayed
• Specify the order in which the rows are displayed

Limiting rows with:

The WHERE clause

The comparison conditions using =, <=, BETWEEN, IN, LIKE, and NULL conditions

Logical conditions using AND, OR, and NOT operators

             SELECT * column_name  FROM table_name WHERE condition;

The WHERE clause follows the FROM clause.

You can restrict the rows that are returned from the query by using the WHERE clause. A WHERE

clause contains a condition that must be met and it directly follows the FROM clause. If the condition

is true, the row meeting the condition is returned.

The WHERE clause can compare values in columns, literal, arithmetic expressions, or functions. It

consists of three elements:

1.  Column name
2.  Comparison condition
3.  Column name, constant, or list of values

Using the WHERE Clause

                SELECT employee_id, last_name, job_id, department_id 

                               FROM employees WHERE department_id = 90 ;

Note: You cannot use column alias in the WHERE clause.



To filter the data a query retrieves, you add a WHERE clause—also called a predicate list or a set of conditions—to your SQL statement. In a nutshell, the WHERE clause specifies criteria that must be met before records are included in your query result set. The WHERE clause must specify a WHERE clause condition (or conditions) that the database software evaluates to be true or false—alternatively, the software can determine the absence of a value. A WHERE clause consists of conditional expressions. A conditional expression takes the form

<left_expression> <as compared with> <right_expression>

some examples of common types of conditional expressions: 

WHERE <column_name> = 

<literal_character_value> 

WHERE <column_name> IN (3, 7, 9) 

WHERE <column_name> >= 100 

WHERE <column_name> LIKE 'E%'; 

WHERE <column_name> BETWEEN 100 AND 500; 

A literal character value, or string, is any list of alphanumeric characters enclosed in single quotation marks, such as ‘King’, ‘73xyz’, or ’16-MAR-1965’.