Skip to main content

BETWEEN Operators

This is best illustrated with an example. Question 4 from Activity 2.2 asked for the name and department details of all female students born in 1978 sorted by surname and forename.

This can be solved by the SQL command:

SELECT title, forename, surname, dept_number, date_of_birth

FROM student

WHERE gender = 'F' AND date_of_birth > = '01-JAN-1978' AND date_of_birth <= '31-DEC-1978'

ORDER BY surname, forename;

The selection criteria on date_of_birth can be replaced by the between clause, ie:

SELECT title, forename, surname, dept_number, date_of_birth

FROM student

WHERE gender = 'F' AND date_of_birth BETWEEN 01-JAN-1978' AND '31-DEC-1978'

ORDER BY surname, forename;

The between clause defines the limits of the selection on the specified column.

Here is another example:

Find all members of staff whose salary is between 15,000 and 25,000.

This can be solved by the SQL SELECT command:

SELECT * FROM staff WHERE salary BETWEEN 15000 AND 25000;

Note: The between clause returns all the rows that meet the criteria 'greater than and equal to' the lower limit and 'less than and equal to' the higher limit. Therefore, in the above example, staff with a salary of exactly 15,000 or 25,000 will be selected as well as all the salaries in between those ranges.

Next: LIKE Operators