Review: The SELECT command in SQL

 

  • The SELECT command in SQL is used to make query requests to a RDBMS

  • The basic SELECT command is as follows: 

         select <list of attributes>      
   from   <list of relations>
  [where  <tuple-condition>]    (<tuple-condition> ???)

  • Effect:

      • Retrieves the specified attribute values from tuples in the specified relations that satisfy the tuple-condition

Tuple condition (1): conventional boolean expressions

  • The boolean (= logical) conditions can use:

      1. Attribute names
      2. Constants

  • We have already seen examples of this kind of tuple condition: 

       select fname, lname
 from   employee
 where  salary > 30000

    salary is an attribute name
    30000 is a constant

Tuple condition (2): pattern matching

  • Condition that checks if some attribute matches some pattern: 

        WHERE attribute-name LIKE 'pattern'

  • The pattern is used to control the matching:

      • _ will match exactly one character

      • % will match 0 or more characters

      • Other characters are matched exactly in both strings

    Let's look at some examples !

Practice queries using the LIKE clause

Formulate the following queries in SQL:

  • Find (all information) of employee whose first name starts with the letter J

  • Find (all information) of employee whose first name ends with the letter N

  • Find (all information) of employee whose first name starts with the letter J and ends with the letter N

  • Find (all information) of employee whose lives in Houston

  • Find (all information) of employee whose last name contains exactly 4 letters

  • Find first and last name of employees who has a dependent whose name contains li

  • Find first and last name of employees whose first name contains li and who has a dependent whose name contains li

Tuple condition (3): membership test

  • Condition that checks if some attribute value is (or is not) a member in some set of values: 

        WHERE attribute-name  [NOT] IN (set-of-values)

  • The (set-of-values) can be:

      • a set of constant values listed by the user

      • a set of attribute values computed by a SQL query     !!!!!!!      

    Let's look at some examples !

Practice queries using the IN clause

Formulate the following queries using IN:

  • Find (all information) of employee in department #5

  • Find the department number of the department where John Smith works.

    Now: find (all information) of employee who are in the same department as John Smith     !!!!!!!

  • Find the SSN of employees who has a dependent

  • Find fname, lname of employees who have a dependent     !!!!!!!

  • Find fname, lname of employees who do not have any dependent     !!!!!!!

Tuple condition (4): existance/empty test

 

  • Condition that checks if some set is empty (NOT EXISTS) or not empty (EXISTS) : 

        WHERE [NOT] EXISTS (set-of-values)

  • The (set-of-values) must be:

      • a set of attribute values computed by a SQL query      

    The SQL query that computes the set of attribute values usually uses some attribute value

    Let's look at some examples next.

Practice queries using the EXISTS clause

Formulate the following queries using EXISTS:

  • Find fname, lname of employees who have a dependent

  • Find fname, lname of employees who manages some other employees

    I.e.: employees who has some "supervisees"