The Where Clause

Usually you will want to restrict the rows returned based on some criteria. i.e. certain values or ranges within one or more columns.

In this example we are only interested in rows where the value in the gender column column is F.

Select first_name, last_name, gender
from employees
Where gender = 'F';

In addition to using the '=' we can use many other operators such as <, <=, >, >=, <>

Select first_name, last_name, hire_date
from employees
Where hire_date < 1990;

Using more complex logical expressions in the Where clause

We can also use the AND and OR keywords to build more complex selection criteria.

Select first_name, last_name, hire_date
from employees
Where hire_date < 1990 and gender = 'M';

The following query returns the rows where the value of hire_date is between 1990 and 1995

Select first_name, last_name, hire_date
from employees
Where hire_date > 1990 and hire_date < 1993;

The same results could be obtained by using the BETWEEN operator

Select first_name, last_name, hire_date
from employees
Where hire_date between 1990 and 1993;

Using the LIKE operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It uses the Percent sign (%) to represent any value

Select first_name, last_name
from employees
Where first_name LIKE 'F%';

Exercise 2

Write a query which returns the emp_no, first_name, and last_name from the employees table table. The gender should all be Male and the emp_id should be between 10050 and 10060.

Solution to Exercise 2