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 known as:

Reason:

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

select attribute-list from relation-list

Example:

select ssn, fname, lname, dno from employee +-----------+--------+---------+-----+ | ssn | fname | lname | dno | +-----------+--------+---------+-----+ | 123456789 | John | Smith | 5 | | 333445555 | Frankl | Wong | 5 | | 999887777 | Alicia | Zelaya | 4 | | 987654321 | Jennif | Wallace | 4 | | 666884444 | Ramesh | Narayan | 5 | | 453453453 | Joyce | English | 5 | | 987987987 | Ahmad | Jabbar | 4 | | 888665555 | James | Borg | 1 | +-----------+--------+---------+-----+
select ssn, fname, lname, dno from employee where dno = 5 +-----------+--------+---------+-----+ | ssn | fname | lname | dno | +-----------+--------+---------+-----+ | 123456789 | John | Smith | 5 | | 333445555 | Frankl | Wong | 5 | | 666884444 | Ramesh | Narayan | 5 | | 453453453 | Joyce | English | 5 | +-----------+--------+---------+-----+ The condition "dno=5" is applied to each tuple in 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:

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 equivalent 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

In the SQL syntax, you cannot use an explicit set of value
Instead, you must use a subquery that returns a set of values
(And I don't want to make things harder to understand with a subquery !!!)

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 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 starts with the letter '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 )