CS457 Syllabus & Progress

The tuple condition in the where clause

Recall: the basic SQL Select syntax

Recall: this is the basic SELECT syntax:

select attribute-list from relation-list where condition

The condition in the where clause is called:

Reason:

The where clause's condition is applied to the following set of tuples:

select attribute-list from relation-list

Example 1:

select fname, lname from employee where dno = 5 The condition "dno=5" is applied to the set of employee tuples

Example 2:

select fname, lname from employee, department where dno = 5 and dname = 'Research' The condition "dno=5 and dname='Research'" is applied to the tuples in the cartesian product employee × department

The complete list of tuple conditions...

The complete list of SQL tuple conditions:

"Traditional" boolean expressions:

atomic-value relOP atomic-value
e.g: salary >= 30000

The membership test operator (IN and NOT IN):

atomic-value IN ( set of values ) or: atomic-value NOT IN ( set of values )
e.g.: ssn IN ( '123456789', '111111111' )

The any selection operator (ANY):

atomic-value relOp ANY (set of values)
e.g.: salary >= ANY ( set of values )

The all selection operator (ALL)

atomic-value relOp ALL (set of values)
e.g.: salary >= ALL ( set of values )

The non-empty set test operator (EXISTS)

EXISTS (set of values) or: NOT EXISTS (set of values)

The null test operator (IS NULL)

atomic-value IS NULL or: atomic-value IS NOT NULL
e.g.: dno IS NULL

Wild card characters ( _ and % which are comparable to the ? and * characters in UNIX)

atomic-value LIKE 'pattern'
e.g.: lname LIKE 'S%'

"Traditional" boolean expression

I will not explain what a boolean expression is (you should know that from CS170 or from some course on programming)
All I want to say is:

Examples:

(1) where salary > 40000 // attr name and a constant (2) where dnumber = dno // 2 attr names and sex = 'F' // attr name and a (string) constant

The IN comparison operator

The comparison operator IN tests whether a value is contained in a set

Syntax:

atomic-value IN ( set of values )

meaning:

The (Boolean) expression evaluates to true if atomic-value is a member of the ( set of values )
The (Boolean) expression evaluates to false otherwise

Example:

Find the fname and lname of employees whose SSN is 123456789 or 333445555

Solution:

SELECT fname, lname FROM employee WHERE ssn IN ('123456789', '333445555');

The NOT IN comparison operator

The comparison operator NOT IN tests whether a value is not contained in a set

Syntax:

atomic-value NOT IN ( set of values )

meaning:

The (Boolean) expression evaluates to true if atomic-value is not a member of the ( set of values )
The (Boolean) expression evaluates to false otherwise

Example:

Find the fname and lname of employees whose SSN is not equal to 123456789 or 333445555

Solution:

SELECT fname, lname FROM employee WHERE ssn NOT IN ('123456789', '333445555');

Pedagogical alert: understandability before complete correctness

The any selection operator

Syntax:

atomic-value RelationalOperator any ( set of values )
RelationalOperator is any relational operator: =, !=, <, <=, >, >= Example: salary >= any ( set of values ) salary <= any ( set of values )

meaning:

The (Boolean) expression evaluates to true if the expression atomic-value RelationalOperator x is true for at least one member of the ( set of values )
The (Boolean) expression evaluates to false otherwise

Example: (illegal, but illustrative)

SELECT fname, lname FROM employee WHERE salary >= ANY ( 30000, 50000 )

Result of this query:

Retrieves the name of employees whose salary is greater than or equal to some value in the set {30000, 50000}.
Since 30000 is the smallest value in the set, the condition salary >= ANY ( 30000, 50000 ) is equavalent to:
Therefore, this query retrieves the name of employees whose salary is greater than or equal to 30000.

Note: why is the above quey illegal

The all selection operator

Syntax:

atomic-value RelationalOperator all ( set of values )
RelationalOperator is all relational operator: =, !=, <, <=, >, >= Example: salary >= all ( set of values ) salary <= all ( set of values )

meaning:

The (Boolean) expression evaluates to true if the expression atomic-value RelationalOperator x is true for all members of the ( set of values )
The (Boolean) expression evaluates to false otherwise

Example: (illegal, but illustrative)

SELECT fname, lname FROM employee WHERE salary >= ALL ( 30000, 50000 )

Result of this query:

Retrieves the name of employees whose salary is greater than or equal to all values in the set {30000, 50000}.
Since 50000 is the largest value in the set, the condition salary >= ALL ( 30000, 50000 ) is equavalent to:
Therefore, this query retrieves the name of employees whose salary is greater than or equal to 50000.

Note: why is the above quey illegal

The "= ANY" and "!= ALL" clauses

Example of the = ANY clause:

SELECT fname, lname FROM employee WHERE ssn = ANY ('111-11-1111', '222-22-2222')

meaning:

The ssn of employee is equal to any (one) value in the set:

('111-11-1111', '222-22-2222')

In other words:

The ssn of employee is a member of the set

('111-11-1111', '222-22-2222')

Example of the != ALL clause:

SELECT fname, lname FROM employee WHERE ssn != ALL ('111-11-1111', '222-22-2222')

meaning:

The ssn of employee is not equal to every value in the set

('111-11-1111', '222-22-2222')

In other words:

The ssn of employee is not a member of the set

('111-11-1111', '222-22-2222')

Summary:

x = ANY ( set of values ) is the same as: x IN ( set of values )
x != ALL ( set of values ) is the same as: x NOT IN ( set of values )

Warning: do not use: = ALL and != ANY clauses !!!

Facts:

Examples:

x = ALL ( 1, 2 ) is always FALSE !!! // Try: x = 0 --> 0 != 1, so false // Try: x = 1 --> 1 != 2, so false // Try: x = 2 --> 2 != 1, so false // Try: x = 3 --> 3 != 1, so false // And so on...
x != ANY ( 1, 2 ) is always TRUE !!! // Try: x = 0 --> 0 != 1, so true // Try: x = 1 --> 1 != 2, so true // Try: x = 2 --> 2 != 1, so true // Try: x = 3 --> 3 != 1, so true // And so on...

Conclusion:

The exists (non-empty set) test operator

Syntax:

exists ( set of values )
Example: exists ( 'a', 'b' ) // returns true (= not empty) exists ( ) // returns false (= empty set)

meaning:

The (Boolean) expression evaluates to true the ( set of values ) is not empty
The (Boolean) expression evaluates to false otherwise

(OK, the pedagogical alert has been cancelled....)

IS NULL: testing for null values
- Testing if an attribute (in a tuple) contains the NULL value:
- Example: find all employee tuples that has a NULL value in the salary attribute:
- Note:

LIKE: Wildcard string comparison

When comparing strings , you can use the LIKE operator to perform wildcard string comparison

Special wildcard characters:

Underscore (_) matches exactly one character
( _ is equivalent to ? in the UNIX shell)
Percent (%) matches 0 or more characters
( % is equivalent to * in the UNIX shell)

Example:

Find fname and lname of employees whose last name start with an 'S'

Solution:



     SELECT fname, lname
     FROM   employee
     WHERE  lname LIKE 'S%'

NOTE:

Wildcards can ONLY be used with the LIKE operator

The following query will find people with last name equal to 'S%':



     SELECT fname, lname
     FROM   employee
     WHERE  lname = 'S%'

Summary: the tuple condition in the WHERE clause

The WHERE clause in the SELECT command contains a condition that is applied to each tuple in the cartesian product (formed by the FROM clause of the SELECT command)
As such:

The following is a list of possible (allowable) forms of tuple conditions :

atomic-value ReationalOperator atomic-value

Examples: 1. salary > 50000 2. dno = dumber

atomic-value IN (SET of values)

Examples: 1. ssn IN ( '111111111', '123456789' ) 2. lname IN ( 'Smith', 'Wesson' )

atomic-value NOT IN (SET of values)

Examples: 1. ssn NOT IN ( '111111111', '123456789' ) 2. lname NOT IN ( 'Smith', 'Wesson' )

atomic-value ReationalOperator ANY (SET of values)

Example: salary > ANY ( 30000, 50000 )
Note: You cannot use an explicit set You must use a set obtained by a sub-query

atomic-value ReationalOperator ALL (SET of values)

Example: salary > ALL ( 30000, 50000 )
Note: You cannot use an explicit set You must use a set obtained by a sub-query

EXISTS (SET of values)

Examples: exists ( 30000, 50000 ) // returns true exists ( ) // returns false
Note: You cannot use an explicit set You must use a set obtained by a sub-query

atomic-value IS NULL

Examples: 1. lname IS NULL // Check for null value 2. salary IS NULL //

atomic-value LIKE 'wildcard-expression'

Examples: 1. lname like 'S%' // "S*" 2. lname like '___' // Any 3 letter word

Any combination of the above tuple condition using logical operators:

NOT tuple-condition

Example: not ( salary > 50000 )

tuple-condition AND tuple-condition

Example: ( dno = 5 ) and ( salary > 50000 )

tuple-condition OR tuple-condition

Example: ( dno = 5 ) and ( dno = 1 )