The WHERE clause can be enhanced to be more selective.  Operators that can appear in WHERE conditions include:

• =, <> ,< ,> ,>= ,<=
• IN
• BETWEEN…AND…
• LIKE
• IS NULL
• AND, OR, NOT

Example 1

Find the student ID of all math majors with more than 30 credit hours.
SELECT STUID FROM STUDENT WHERE MAJOR = ‘Math’ AND CREDITS > 30;

STUID

S1015

S1002

Example 2

Find the student ID and last name of students with between 30 and 60 hours (inclusive).
SELECT STUID, LNAME FROM STUDENT WHERE CREDITS BETWEEN 30 AND 60;

this is the same as…
SELECT STUID, LNAME FROM STUDENT WHERE (CREDITS >= 30)  AND  (CREDITS <= 60);

STUID	LNAME
S1015	Jones
S1002	Chin

Example 3

Retrieve the ID of all students who are either a math or an art major.
SELECT STUID FROM STUDENT WHERE MAJOR IN (‘Math’,’Art’);

this is the same as…
SELECT STUID FROM STUDENT WHERE (MAJOR = ‘Math’) OR (MAJOR = ‘Art’);

STUID

S1010

S1015

S1002

S1013

Example 4

Retrieve the ID and course number of all students without a grade in a class.
SELECT STUID, COURSENUM FROM ENROLL WHERE GRADE IS NULL;

STUID	COURSENUM
S1010	ART103A
S1010	MTH103C

NOTE: IS NULL may only appear in the WHERE clause.  Also note that you say “IS NULL”, not “= NULL”.  NULL means “unknown” and does not really have a value in the normal sense.

Example 5

List the ID and course number for all students that successfully completed classes (the inverse of #4 above).
SELECT STUID, COURSENUM FROM ENROLL WHERE GRADE IS NOT NULL;

STUID	COURSENUM
S1001	ART103A
S1020	CIS201A
S1002	CIS201A
S1002	ART103A
S1020	MTH101B
S1001	HST205A
S1002	MTH103C

Example 6

List the course number and faculty ID for all math courses.
SELECT COURSENUM, FACID FROM CLASS WHERE COURSENUM LIKE ‘MTH%’;

COURSENUM	FACID
MTH101B	F110
MTH103C	F110

NOTE: % is a wildcard for any number of characters.  _ is a wildcard that replaces a single character.  They can be used together along with normal characters.

	C