---

The "easier-to-explain" tuple-conditions

---

• "Traditional" boolean expression
  • The tuple condition can be a "traditional" boolean condition that you have learned in CS170 (or some other intro to CS class)

    ---

    ---

  • A boolean condition used as a tuple condition must satisfy:

    The factors (= items) in a boolean expression as a tuple-condition must only contain: Constants           or        Attribute names A expression (= combination) of constants and/or attribute names

    ---

  • Examples:

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

  ---

  ---

  ---

  ---

  ---

• IS NULL: testing for null values
  • How to check if some (atomic/not-compound) value in a tuple contains the NULL value:

    atomic-value IS NULL Example: lname IS NULL salary IS NULL

    ---

    ---

  • Example:     find all employee that has a NULL value in the superssn attribute:

    SELECT * FROM employee WHERE superssn IS NULL Result: +-----------+-------+-------+----------+ | ssn | fname | lname | superssn | +-----------+-------+-------+----------+ | 888665555 | James | Borg | NULL | +-----------+-------+-------+----------+

    ---

    ---

  • Note:

    To test if an atomic-value is not equal to NULL, use:           atomic-value IS NOT NULL

  ---

  ---

  ---

  ---

• Wildcard characters
  • Wildcard character:

    Wildcard character = a special character that will match a specific pattern of characters

    Example:

    In UNIX: * will match any number of characters ? will match exactly 1 character Example: ls e* // list all file names that starts with "e" ls e*s // list all file names that starts with "e" // and ends with "s" ls ? // list all file names that contains 1 character

  ---

  ---

  ---

  ---

• Wildcard characters used in SQL
  • Wildcard characters in SQL:

    % will match any number of characters _ will match exactly 1 character

  ---

  ---

  ---

  ---

• LIKE: wildcard string comparison
  • String comparison using wildcard characters in SQL:

    atomic-value LIKE 'pattern' Examples: lname LIKE 'W%' // lname starts with "W" lname LIKE 'W%g' // lname starts with "W" and ends with "g" lname LIKE '____' // lname has 4 characters

    ---

    ---

  • Example:

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

    Solution:

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

    ---

  • NOTE:

    Wildcard characters must be used with the LIKE operator !!! This query will find employee with last name equal to 'S%': SELECT fname, lname FROM employee WHERE lname = 'S%'

  ---

  ---

  ---

  ---

• The IN comparison operator

  • The comparison operator IN tests whether a (simple) value is contained in a set (of values)

    ---

  • Syntax:

    atomic-value IN ( set of values ) Examples: 123 IN ( 123, 456, 789 ) // Evaluates to true 333 IN ( 123, 456, 789 ) // Evaluates to false dno IN ( 1, 4 ) // Test whether: dno ∈ {1, 4}

    Meaning of the IN-clause:

    The IN-condition evaluates to true if: atomic-value ∈ ( set of values ) The IN-condition 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');