Retrieving a Subset of Rows: Simple Conditions

Often you don't want to retrieve all the rows in a table but want only the rows that satisfy one or more conditions. In this case, you would include the WHERE clause in the SELECT statement to retrieve a portion, or subset, of the rows in a table.

A search condition expresses the logic by which the computer determines which rows of the table are retrieved and which are ignored. The search condition has many variations. A simple search condition is formed with a conditional expression, which specifies a comparison between two values. It has the following format:

expression    comparison operator    expression

The expressions in the conditional expression are usually a column name or a constant. The comparison operator indicates a mathematical comparison such as less than, greater than, equal to, etc. Table here shows the comparison operators allowed in Texis.

Example: Let's say there is a DEPARTMENT table which has listed in it the department code, the long form department name, the department head, the division to which the department belongs, and the annual department budget. The conditional expression to find departments with a budget above $25,000 can be written:

BUDGET > 25000

The conditional expression to find all departments in the Product Division is written:

DIV = 'PROD'

Each row in the indicated table is evaluated, or tested, separately based on the condition in the WHERE clause. For each row, the evaluation of the conditional expression is either true or false. When a condition is true, a row is retrieved; when the condition is false, the row is not retrieved. For example, if a department has a $35,000 budget, then the conditional expression "BUDGET > 25000" is true and the row is included in the query result. However, if the department had a budget of $15,000, then the result of the conditional expression "BUDGET > 25000" is false and the row is not retrieved.

Example: Let's develop a list of all departments, in long form, in the Product Division.

Enter the statement:

SELECT  DNAME
     FROM    DEPARTMENT
     WHERE   DIV = 'PROD' ;

The result displayed will be:

DNAME
  Research and Development
  Manufacturing
  Customer Support and Service
  Product Marketing and Sales

In the WHERE clause, the condition "DIV must equal PROD" results in the retrieval of the name of each department in the Product Division. As only DNAME, the long form departmental name, was requested in the SELECT statement, a list of department names is all that is shown.

Example: Let's develop a list of all departments with a budget above $25,000.

Enter the statement:

SELECT  DNAME, BUDGET
     FROM    DEPARTMENT
     WHERE   BUDGET > 25000 ;

The result displayed will be:

DNAME                                BUDGET
  Finance and Accounting               26000
  Corporate Legal Support              28000
  Research and Development             27500
  Manufacturing                        32000
  Strategic Planning and Intelligence  28500