Structured Query Language (SQL) contd.

Evaluating SQL Statements

We have seen how SQL syntax relates to the basic operations of the relational algebra. We have also seen that the basic form for queries is:

         SELECT column list
           FROM table list
          WHERE condition

1. First, the FROM clause is executed. Since the FROM clause may specify multiple tables, execution may involve producing a Cartesian product.
2. Second, if present, the WHERE clause is executed. 3VL will be used to evaluate the condition for each tuple of the table that results from execution of the FROM clause.
3. Third, the SELECT clause is executed. That is, a projection of the table that results from the WHERE clause will be done. Also, any expressions that are specified in the SELECT will be done.

To illustrate, consider the JOIN example from page 246 discussed previously.

         SELECT SID, Name, ClassName
           FROM STUDENT, ENROLLMENT
           WHERE SID=StudentNumber

1. The FROM clause is first executed and so the Cartesian product of STUDENT and ENROLLMENT is produced.
2. The WHERE clause is then executed and so each tuple of the resulting Cartesian product that satisfies SID=StudentNumber is retained.
3. The SELECT clause is then executed. That is, a projection of the resulting table on SID, Name, and ClassName is produced.

Note: If GROUP BY and HAVING clauses are included then they will be executed after the WHERE clause. The GROUP BY will be executed and then the HAVING will be executed.

Outer Join

SQL-92 provides full support for the Join operation defined in the relational model. This includes support for Outer Join. This support is provided by way of additional keywords that may be specified in the FROM clause. Six join-types have been provided but we will only discuss the following:

1. INNER JOIN: The basic join discussed previously (i.e. Equi-join, Natural join requires the NATURAL keyword).
2. LEFT OUTER JOIN: As defined for the Relational Algebra operator. That is, unmatched tuples from the table named on the left of the join expression will be retained with null values assigned where necessary.
3. RIGHT OUTER JOIN: As defined for the Relational Algebra operator. That is, unmatched tuples from the table named on the right of the join expression will be retained with null values assigned where necessary.
4. FULL OUTER JOIN: As defined for the Relational Algebra operator. That is, unmatched tuples from both tables will be retained with null values assigned where necessary.

Each join-type is qualified by the ON keyword which specifies the join condition.

General form (2 table join):

         SELECT column list
           FROM table_1 join-type table_2 ON condition

where join-type is one of the above and condition is a boolean expression.

Let us consider the example discussed on page 248. SQL-92 syntax for this outer join would be:

         SELECT Name, ClassName
           FROM STUDENT LEFT OUTER JOIN ENROLLMENT ON SID=StudentNumber

See the table on page 248 to see the tuples that would result from this statement.

We may rewrite the basic join discussed above using the INNER JOIN join-type thus:

         SELECT SID, Name, ClassName
           FROM STUDENT INNER JOIN ENROLLMENT ON SID=StudentNumber

See the table at the top of page 246 to see the tuples that would result from this statement. In other words, this statement is equivalent to the SQL statement at the top of page 246.

Note:

Although the general form presented above is for two tables only, it is easily extended to accommodate three or more tables. Also, notice that we did not need a WHERE clause for any of the examples above. Using this join syntax, the WHERE clause applies after the join is done (i.e. think of the ON keyword as part of the FROM clause) and so would apply to the tuples that result from the join. To illustrate, let us say we were only interested in classes that start with the letter B then we could include a WHERE clause thus:

         SELECT SID, Name, ClassName
           FROM STUDENT INNER JOIN ENROLLMENT ON SID=StudentNumber
           WHERE ClassName LIKE 'B%'

Renaming

SQL-92 provides the AS keyword for renaming. It may be specified in either the SELECT clause or the FROM clause and so, in effect, may be used to rename columns (including those that arise from expressions), and tables.

• Column renaming:

  Any column or expression in the SELECT clause may be renamed. The general form would be either:

  column_name AS new_name
  or:
  expression AS new_name

  Consider the following SQL statement discussed previously:

           SELECT SID, Hours*20
             FROM ASSISTANT
  

  Recall the resulting table:

           SID  
           --------
           100  400
           200  200
           200  100
           200  300
  

  We could use the AS keyword to provide a more descriptive name for the SID column as well as a name for the computed column.

           SELECT SID AS 'Student ID', Hours*20 AS Earnings
             FROM ASSISTANT
  

  The resulting table would now be:

           Student ID  Earnings 
           --------------------
               100       400
               200       200
               200       100
               200       300
  

  Notice the need for quotes when renaming SID as Student ID but quotes are not needed for the name Earnings. Also, remember that the new column name cannot be used in the WHERE clause (to understand why, see section on "Evaluating SQL Statements" above).

• Table renaming:

  Any table in the FROM clause may be renamed. Table renaming is more commonly referred to as aliasing. Aliasing is often used for qualification. We saw the use of qualification in practice problems for quiz #2 to resolve ambiguous column names. That is, if a SQL statement involves multiple tables that contain the same column name then we must resolve the ambiguity by qualifying ambiguous column names with the corresponding table name (see page 246, note the dot notation). Renaming is a convenient way of addressing qualification.

  The general form is:

  table_name AS new_name

  Consider the pubs database from Project #1. Let us say we are interested in titles for publishers. We need to join the titles and publishers tables but since pub_id that exists in both tables, we also need to resolve the ambiguity by qualification. We may use table renaming to simplify the qualification needed thus:

           SELECT pub_name, title
             FROM titles AS t INNER JOIN publishers AS p 
                                ON t.pub_id=t.pub_id
  

  Notice that the new table name may be used in both the WHERE and SELECT clauses! To understand why, see the section above on Evaluating SQL Statements.

  See pages 246 - 248 for other examples of qualification. Think of how to use renaming for each example.

Subqueries

SQL provides a mechanism for the nesting of queries. It is referred to as subqueries. Subqueries are SELECT...FROM...WHERE statements nested in the WHERE clause of another query. Subqueries are commonly used to perform tests for set membership. See page 244 for details of the following example.

         SELECT Name
           FROM STUDENT
           WHERE SID IN 
                     (SELECT StudentNumber
                        FROM ENROLLMENT
                        WHERE ClassName='BD445') 

Notice that we enclose the nested subquery in parentheses. We may nest to any depth (limited by implementation restrictions). Page 245 presents an example of two nested subqueries.

Subqueries will work as long as the columns in the answer come from a single table. If this is not the case then a join should be used. Also, for this formulation of subqueries (i.e. set membership), the nested query must result in a table that contains a single column. See page 247-248 (Comparison of SQL Subquery and Join) for a discussion of when to use subqueries versus joins.

Changing Data

SQL may also be used for changing data in tables. That is, the SQL standard has provided statements to insert tuples, delete tuples, and modify the values of existing tuples.

Insert

SQL provides the INSERT clause for this operation. The general form is:

         INSERT 
          INTO table_name (column list)
          VALUES (value list)

See page 250 for details of the following example:

         INSERT 
          INTO ENROLLMENT (StudentNumber, ClassName)
          VALUES (400, 'BD445')

Note that the keyword INTO is optional. Note also that any omitted columns will receive the default values specified in the column definition for the table (see DDL notes). An error will result if a column is omitted and that column has no default value.

Delete

SQL provides the DELETE clause for this operation. The general form is:

         DELETE 
          FROM table_name 
          WHERE condition

Tuples satisfying the WHERE condition will be deleted. See page 250 for details of the following example:

         DELETE 
          FROM STUDENT 
          WHERE SID=100

Note that the FROM clause is optional. Note also that the WHERE clause is optional. If the WHERE is omitted then all tuples will be deleted from the specified table.

Modify

SQL provides the UPDATE clause for this operation. The general form is:

         UPDATE table_name 
          SET column_name=expression,... 
          WHERE condition

The notation ",..." indicates that several columns, delimited by the symbol "," may be specified. Tuples satisfying the WHERE condition will be updated. See page 251 for details of the following example:

         UPDATE ENROLLMENT
          SET PositionNumber=44
          WHERE SID=400

Note that the WHERE clause is optional. If omitted, all tuples will be modified.

Notes:

1. String Expressions

   The SELECT clause may contain string expressions as well as arithmetic expressions. SQL92 provides a rich group of string operators and functions.

   • Concatenation operator:

     A string is a sequence of one or more characters. Two strings may be concatenated (i.e. attached) by using the string concatenation operator || ( SQL Server also uses the + symbol). Consider the pubs database used in Project #1. The following SQL statement illustrates the use of the concatenation operator:

              SELECT au_lname || ', ' || au_fname 
                FROM authors
                WHERE au_id='409-56-7008'
     

     The resulting tuple would contain the string "Bennet, Abraham".

   • Functions:

     Several functions are available (three are presented below). The col_name argument for each of the examples presented below must be of type character.

     • LOWER(col_name) will convert each character of col_name to its lowercase equivalent.
     • UPPER(col_name) will convert each character of col_name to its uppercase equivalent.
     • SUBSTRING(col_name, i, n) will extract a substring of length n, starting at position i from col_name.

2. Exists & Not Exists

   EXISTS and NOT EXISTS are logical operators whose value is either true or false depending on the presence or absence of rows that fit the qualifying conditions. These operators are therefore used in conjunction with a subquery for existence tests. See page 248-249 for details.

3. DDL

   DDL details are included in the section titled "E-R Model, Normalization & Design".

Readings: Chapter 9, pg 244-251