ImplementationUsing AND, OR, <, > and = operators

It is possible to use the AND, OR <, > and = operators with a WHERE clause. This will refine a search further.

AND

Using AND allows the database developer to specify more than one condition that must be met. In this example, two conditions are specified.

SELECT PupilID, First Name, Surname, Class FROM Pupil

WHERE Class = ‘1T1’ AND Surname = ‘McNamee’;

This would return:

OR

Using OR allows the database developer to specify a series of conditions, where only one of the conditions needs to be met.

SELECT PupilID, First Name, Merit Points

FROM Pupil

WHERE First Name = ‘Amy’ OR Merit Points > 60;

This would return:

Even though one of the pupils named Amy has less than 60 merit points, the record is still returned because of the OR operator. The first condition means that all records containing ‘Amy’ in the First Name column/field will be returned regardless of whether the second condition is met.

Operators

<, > and = are all operators

In the example above, > was used to return all results greater than a particular mark. < and = can be used in the same way

To show all records containing 'Amy' in the First Name column OR all records containing results less than 60 points in the Merit Points column, you would change the third line of code to:

WHERE First Name = ‘Amy’ OR Merit Points < 60;

To show all records containing 'Amy' in the First Name column OR all records containing results equal to 60 points in the Merit Points column, you would change the third line of code to:

WHERE First Name = ‘Amy’ OR Merit Points = 60;