sqlplus5

SQL Exercise 5
LOGICAL OPERATORS: AND and OR

AND

Sometimes you will want to specify more than one condition in a WHERE clause. To do this use AND between your search conditions in the WHERE clause. AND will ensure that each search criteria is equal to "true" before ORACLE will retrieve the row. If and only if the conditions on each side of AND are true will the row be returned. You may AND as many conditions as is necessary in the WHERE clause. For example: if you wanted to search the EMPLOYEE table for the names of all of the managers who are female, the query would be formatted as follows:

SQL> SELECT <column_name, …>

FROM <table_name>

WHERE <column_name> = <qualifier>

AND <column_name> = <qualifier>;

If the columns were called 'NAME', 'JOB' and 'SEX', the table was named 'EMPLOYEE' and the data qualifiers we're matching are the 'JOB' and 'SEX' columns, then the SQL query would be as follows:

SQL> SELECT NAME, JOB, SEX

FROM EMPLOYEE

WHERE JOB = 'Mngr' ç qualifier; equal predicate

AND SEX = 'F'; ç qualifier

The results table will look like this:

NAME	JOB	S
Allen	Mngr	F
Chen	Mngr	F

In this example, the only rows that are returned are those where the employee's JOB is equal to 'Mngr' and the employee's SEX is equal to 'F'. Suppose an employee is male and is a manager, would the row be returned? No, only when the search criteria is true on both sides of the AND will the row be returned.

In addition to selecting rows based on all of the conditions being true, you are also able to search for rows which meet any of several conditions. In this case you would use the connector, OR.

Using the query shown in the previous example, we may simply substitute OR for AND. The query would return all the rows where the employee has the job title 'Mngr' and all the rows where sex is listed as 'F'. The re-written query is as follows:

SQL> SELECT NAME, JOB, SEX

FROM EMPLOYEE

WHERE JOB = 'Mngr' ß qualifier

OR SEX = 'F'; ç qualifier

The results table looks like this:

NAME	JOB	S
Smith	Slsm	F
Watson	Mngr	M
Allen	Mngr	F
Chen	Mngr	F
Ramirez	Mngr	M
Schwartz	Slsm	F

When using combinations of AND and OR it is very important to use parentheses to ensure the proper selection of rows returned to the results table. The parentheses provide for specific criteria in the selection process. For example, the following query wants to know the employee number and name for anyone in the EMPLOYEE table who is a manager and all the clerks working in department 50:

SQL> SELECT EMPNO, NAME, JOB, DEPTNO

FROM EMPLOYEE

WHERE JOB = 'Mngr'

OR (JOB = 'Clrk' AND DEPTNO = 50);

The results table looks like this:

EMPNO	NAME	JOB	DEPTNO
105	Watson	Mngr	30
109	Allen	Mngr	40
110	Turner	Clrk	50
200	Chen	Mngr	10
210	Ramirez	Mngr	50

The following query looks much the same as the previous one with the exception that the parentheses were moved. That movement makes quite a bit of difference in the results returned. This query is asking for the employee number and name of anyone who is a manager or a clerk working in department 50.

SQL> SELECT EMPNO, NAME

FROM EMPLOYEE

WHERE (JOB = 'Mngr' OR JOB = 'Clrk')

AND DEPTNO = 50;

The results table for this query looks like this:

EMPNO	NAME	JOB	DEPTNO
110	Turner	Clrk	50
210	Ramirez	Mngr	50

For you to try:

Write a SQL query to return the employee number, name and salary of all females who work in Department 10.
Write a SQL query to return the employee number, name and salary of all male managers, ordered alphabetically by name.
Write a SQL query to return the name and job of all salesmen and managers who are female.

Go here to check your answers.

Last revised on 10/21/97 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems

Return to Oracle/SQL Tutorial Contents Page | Return to Exercise 4 | Go to SQL Exercise 6