💾 MORE COMPLEX SINGLE TABLE RETRIEVAL

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;

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);

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’);

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;

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;

Example 6

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

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.