Outline

• SELECT
• Quiz
• Joins
• Subqueries
• Views
• Start DCL

Introduction

• Welcome back from Spring Break!
• Before the break we were discussing SQL.
• We had gone over the syntax of the basic DDL and DML commands for DB2 such as CREATE, INSERT, UPDATE, and DELETE.
• We gave an example of creating a Bank database, its tablespace, tables and constraints.
• As we did this we explained how the DB2 catalog was affected.
• Today, we are going to begin by looking at how to query a database. i.e., the syntax of the SELECT command...

SELECT

• The SELECT command allows you to query a set of database tables and find row and column data that match a given set of criteria
• It also allows you to order how the results are displayed.
• The basic syntax of the SELECT command is:

  SELECT [DISTINCT] select_list 
  FROM table_list 
  [WHERE search_condition] 
  [GROUP BY grouping_list] 
  [HAVING grouping_qualification] 
  [ORDER BY orderby_list]
  

Parts of a SELECT

• select_list is a comma separated list of columns you would like in the output. For example, FNAME, LNAME, AGE.
• table_list is a comma separated list of tables which you would like to look for data in: EMPLOYEE, PROJECT.
• search_condition is an expression consisting of any of the operators below:
  • Comparison operators: =, >, <, >=, <=, <>
  • Boolean operators: AND, OR, NOT
  • Range operator: BETWEEN (an alternative to using a < b AND b < c)
  • Set comparison operators:
    • IN (an alternative to using a = b OR a = c)
    • comparison operator ANY or SOME (for example, < ANY or < SOME
    • comparison operator ALL (for example, < ALL)
    • EXIST
  • String operator: LIKE. This uses the syntax % to mean any string of zero or more characters, and underscore _ to match any single character.

Quiz

Which of the following statements is true?

1. The catalog table SYSIBM.SYSTABLES is changed when one creates a view.
2. The KEYSEQ column of SYSIBM.SYSCOLUMNS is used to handle autoincrementing columns.
3. You can set the bufferpool when creating a database but not when creating a tablespace in DB2.

Example SELECTs and their results

These examples show that if you use * in the select_list it means all columns.

Conceptual Evaluation of SELECT

• Compute the cartesian product of the tables in table_list.
• Check conditions in search_condition, and discard rows that fail to satisfy the condition.
• Discard columns that are not in select_list.
• If the DISTINCT keyword is present eliminate duplicate rows (typically by sorting).

It should be noted this kind of brute force evaluation of SELECT statement typically won't be very efficient.

Cartesian Product (aka Cross Product) of two tables

• Given out tables of a couple slides back, consider the query:

  SELECT M.NAME, C.BOOKNO 
  FROM MEMBERS M, CHECKOUTS C 
  WHERE M.CARDNO = C.CARDNO
  

• Notice in the above we have given aliases M and C for the tables and then used those in our select_list.
• If we had used * rather than M.NAME, C.BOOKNO this query and ignored the where clause the result looks like:

Example Predicate Evaluation

• Suppose we want to find all members who have checked out a book.
• We could do this with the query:

  SELECT * 
  FROM MEMBERS M, CHECKOUTS C 
  WHERE M.CARDNO = C.CARDNO
  

• This could be visualized as discarding the rows from the previous query that fail the search criteria:

Projections

• What was the effect of the select_list M.NAME, C.BOOKNO in our query.
• We can visualize this as discarding the columns we don't want:

Aggregate Functions

• The select_list is also allowed to make use of expressions involving aggregate functions.
• Some examples of aggregate functions that DB2 supports are: COUNT, COUNT_BIG, COUNT (*), COUNT_BIG (*), MAX, MIN, SUM, AVG, STDDEV, VARIANCE, COVARIANCE, XMLAGG.
• For example, COUNT(Home_Phone) - would return a count of the number times Home_Phone was not null. COUNT(*) returns a count of all rows in the table. The _BIG variants of these functions use doubles.
• XMLAGG (XML expr) returns an XML sequence containing an item for each non-null value in a set of XML values.

Examples of Aggregate Function

• Find the due date of the next book due.

  SELECT MIN(DUEDATE) 
  FROM CHECKOUTS;
  

• Find the card number and due date of the next book due.

  SELECT C.CARDNO, C.DUEDATE 
  FROM CHECKOUTS C
  WHERE C.DUEDATE = (SELECT MIN(C2.DUEDATE)
  FROM CHECKOUTS C2);
  

• Notice we use a subselect because we can't mix C.CARDNO, MIN(C.DUEDATE).

GROUP BY

• GROUP BY is used to form groups of rows for applying aggregate functions.
• If GROUP BY is not used, the aggregate function is applied to all qualifying rows.
• For example, for each member with more than one book checked out, find the due date of the next book that is due
• We could do this using GROUP BY as follows:

  SELECT CARDNO, MIN(DUEDATE) 
  FROM CHECKOUTS 
  GROUP BY CARDNO 
  HAVING COUNT(*) > 1;
  

• We will explain having in a moment.

Conceptual Evaluation of Aggregates

• Compute the cartesian product, do a selection, then do a projection.
• Form groups
• Perform aggregation: One row per qualifying group is returned.
• Discard groups that do not satisfy the conditions in grouping_qualification.

Example

As an example, consider the query:

SELECT CARDNO, MIN( DUEDATE)
FROM CHECKOUTS 
GROUP BY CARDNO 
HAVING COUNT(*) > 1;

Joins

• Joins are used to retrieve rows from multiple tables using a single SELECT statement.
• The basic syntax looks like:

  SELECT column_list FROM
  table1
  {INNER | {LEFT | RIGHT | FULL} OUTER} JOIN
  table 2
  ON join_condition 
  [WHERE search_condition]
  

Inner Join

• SQL offers two equivalent ways to write the same inner join:

  SELECT * 
  FROM CHECKOUTS C, MEMBERS M 
  WHERE C.CARDNO = M.CARDNO;
  

  SELECT * 
  FROM CHECKOUTS C [INNER] JOIN MEMBERS M 
  ON C.CARDNO = M.CARDNO;
  

Outer Join

• Return matched row plus rows that do not satisfy the join_condition.
• Left outer join: unmatched rows from left table
• Right outer join: unmatched rows from right table
• Full outer join: unmatched rows from both tables
• Here is an example of a right outer join:

  SELECT * 
  FROM CHECKOUTS C RIGHT OUTER JOIN MEMBERS M 
  ON C.CARDNO = M.CARDNO;
  

Outer Join Examples

Subqueries

• Set comparison operators can be used to form subqueries

  SELECT [DISTINCT] select_list 
  FROM table_list 
  WHERE { expr [NOT] set_comparison_operator } 
  SELECT [DISTINCT] subquery_select_list
  FROM subquery_table_list 
  WHERE search_condition
  [GROUP BY ... ] 
  [HAVING ... ] 
  [ORDER BY ... ]
  

Example -- Non-correlated Subquery

• Find the name of the library member who checked out A Tale of Two Cities (book ID = 5).
• The inner query is independent of the outer query. Conceptually, it is evaluated first, and then its results are passed to the outer query.

Example -- Correlated Subquery

• Find all DB2 managers' salaries.

  SELECT 
  E.lastname, D.departName, E.salary 
  from emp E 
  where E.empno in
  (select D.mgrno from dept D
  where	D.deptno = E.workdept and D.departName like 'DB2%')
  

• Conceptually, the inner query is executed for each qualifying row from the outer query.

Set Operations

SQL also supports set operations of union, difference, and intersection:

• Set union (a UNION b)
• Set difference (a EXCEPT b)
• Set intersection (a INTERSECT b)

Views

• A view is a virtual table composed of the result set of a query
• View definitions are saved in the DB2 catalog
• Views can be queried just as a table is
• View can be composed
• Views can be used to provide security
• Views can also be used to simplify queries

CREATE VIEW newEmp	
AS SELECT name, empno, eduYear 
FROM Employee WHERE hireYear > 2000;

can be queried with 

SELECT * FROM newEMP WHERE eduYear > 17

DCL commands

• We are now going to look at the last group of SQL commands: the data control language commands (DCL).
• These include GRANT and REVOKE.

GRANT

• Each user has an authorization ID and password for accessing the database.
• A user holds privileges for objects that he or she creates. GRANT is used to permit others to access his or her objects.

GRANT and REVOKE

• GRANT and REVOKE on table or view objects

  GRANT {ALL | privilege_list} 
  ON {table_name | view_name} 
  TO {PUBLIC | user_list} 
  [WITH GRANT OPTION]
  

  REVOKE {ALL | privilege_list } 
  ON {table_name | view_name} 
  FROM {PUBLIC | user_list}
  

• Privilege list: SELECT | UPDATE | DELETE | INSERT | ALTER| ...
• With GRANT option: The grantee can pass on the granted privilege to other users