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

1. 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

2. 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

3. 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

4. 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.

5. 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

6. 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

Basis Data