Learning Visually with Examples [Electronic resources] نسخه متنی

Understanding DB2® Learning Visually with Examples
By Raul F. Chong, Clara Liu, Sylvia F. Qi, Dwaine R. Snow

Publisher	: Prentice Hall PTR
Pub Date	: January 25, 2005
ISBN	: 0-13-185916-1
Pages	: 928

9.1. Querying DB2 Data

You use the

SELECT statement to query tables or views from a database. At a minimum, the statement contains a

SELECT clause and a

FROM clause. The following are two examples of a

SELECT statement. This first example uses the wildcard symbol (*) to indicate that all columns from the

employee table are selected:


SELECT * FROM employee;

In this example, the column names are specified in the

SELECT statement:


SELECT empno, firstnme, lastname FROM employee;

9.1.1. Derived Columns

When data is retrieved from a table using the

SELECT clause, you can derive new columns based on other columns. Figure 9.1 illustrates this: the column

totalpay is derived by adding the

salary and

comm columns.

Figure 9.1. Example of a derived column


SELECT empno, firstnme, lastname, (salary + comm) AS totalpay 
FROM employee 
EMPNO  FIRSTNME     LASTNAME        TOTALPAY
------ ------------ --------------- ------------
000010 CHRISTINE    HAAS                 4320.00
000020 MICHAEL      THOMPSON            44550.00
000030 SALLY        KWAN                41310.00
000050 JOHN         GEYER               43389.00
000060 IRVING       STERN               34830.00
. . .

9.1.2. The SELECT COUNT Statement

The

SELECT COUNT statement lets you get a row count of the result set. For example, the SQL statement in Figure 9.2 returns the number of rows in the

sales table whose

region column has the value

Quebec . In this case, there are 12 records that match this criteria.

Figure 9.2. Example of a SELECT COUNT statement


SELECT COUNT(*) 
FROM sales 
WHERE region = 'Quebec' 
1
-----------
12
1 record(s) selected.

9.1.3. The SELECT DISTINCT Statement

To eliminate duplicate rows in a result set, use the

DISTINCT keyword in the

SELECT statement. The SQL statement in Figure 9.3 selects the distinct values of the

region column from the

sales table.

Figure 9.3. Example of a SELECT DISTINCT statement


SELECT DISTINCT region FROM sales 
REGION
---------------
Manitoba
Ontario-North
Ontario-South
Quebec
4 record(s) selected.

You can also use the

DISTINCT keyword with the

SELECT COUNT statement. For example, the SQL statement in Figure 9.4 returns the number of distinct regions in the

sales table.

Figure 9.4. Example of a SELECT COUNT DISTINCT statement


SELECT COUNT (DISTINCT region) FROM sales 
1
-----------
4
1 record(s) selected.

The output shows that there are four distinct regions in the

sales table. This value agrees with the

SELECT DISTINCT region FROM sales result obtained in Figure 9.3.

9.1.4. DB2 Special Registers

DB2 special registers are memory registers that allow DB2 to provide information to an application about its environment. They can be referenced in SQL statements. The most commonly used special registers are listed in Table 9.1. For a complete list of DB2 special registers, refer to the

DB2 UDB SQL Reference Guide .

Table 9.1. DB2 Special Registers
DB2 Special Registers	Descriptions
CURRENT DATE	A date based on the time-of-day clock at the database server. If this register is referenced more than once in a single statement, the value returned will be the same for all references.
CURRENT ISOLATION	Identifies the isolation level for any dynamic SQL statements issued within the current session.
CURRENT LOCK TIMEOUT	Specifies the number of seconds that an application will wait to obtain a lock.
CURRENT PACKAGE PATH	Identifies the path to be used when resolving references to packages.
CURRENT PATH	Identifies the SQL path used to resolve procedure, functions, and data type references for dynamically prepared SQL statements. The value of this special register is a list of one or more schema names.
CURRENT SCHEMA	Identifies the schema name used to qualify unqualified database objects in dynamic SQL statements. The default value is the authorization ID of the current user. This special register can be modified using the `SET CURRENT SCHEMA` statement.
CURRENT TIME	A time based on the time-of-day clock at the database server. If this register is referenced more than once in a single statement, the value returned will be the same for all references.
CURRENT TIMESTAMP	A timestamp based on the time-of-day clock at the database server. If this register is referenced more than once in a single statement, the value returned will be the same for all references.
SESSION_USER	Specifies the authorization ID to be used for the current session. This is the same as the USER special register.
SYSTEM_USER	Specifies the authorization ID of the user who connected to the database.
USER	Specifies the runtime authorization ID used to connect to the database.

To display the value of a special register, use the following statement:

VALUES

special_register

For example, to display the value of the CURRENT TIMESTAMP special register, issue:

VALUES CURRENT TIMESTAMP

SQL also supports expressions using DB2 special registers. Figure 9.5 uses the CURRENT DATE register to derive the

retiredate column.

Some of the special registers are updatable. For example, to change the value of the CURRENT ISOLATION special register to RR (Repeatable Read), issue:

SET CURRENT ISOLATION RR

Figure 9.5. Example of using DB2 special registers in a SELECT statement


SELECT empno, firstnme, lastname 
, (salary + comm) AS totalpay 
, CURRENT DATE AS retiredate 
FROM employee 
EMPNO  FIRSTNME     LASTNAME        TOTALPAY     RETIREDATE
------ ------------ --------------- ------------ ----------
000010 CHRISTINE    HAAS                 4320.00 04/01/2005
000020 MICHAEL      THOMPSON            44550.00 04/01/2005
000030 SALLY        KWAN                41310.00 04/01/2005
000050 JOHN         GEYER               43389.00 04/01/2005
000060 IRVING       STERN               34830.00 04/01/2005
. . .

9.1.5. Scalar and Column Functions

Invoking a function against the column values is also very useful. Consider the following example where you want to obtain the name of the day for each employee's hire date. You can use the

DAYNAME built-in function supplied by DB2 as shown in Figure 9.6.

Figure 9.6. Example of a scalar function


SELECT empno, firstnme, lastname 
, (salary + comm) AS totalpay 
, DAYNAME(hiredate) AS dayname 
FROM employee 
EMPNO  FIRSTNME     LASTNAME        TOTALPAY     DAYNAME
------ ------------ --------------- ------------ ------------
000010 CHRISTINE    HAAS                 4320.00 Friday
000020 MICHAEL      THOMPSON            44550.00 Wednesday
000030 SALLY        KWAN                41310.00 Saturday
000050 JOHN         GEYER               43389.00 Wednesday
000060 IRVING       STERN               34830.00 Friday
. . .

The function

DAYNAME used in scalar function takes input values and returns a single value. Another type of function, called a column function, operates on the values of an entire column. The example in Figure 9.7 shows how to calculate the average values of the

salary column.

The

AVG column function, which is a built-in function, calculates the average of all the salary values in the employee table. Notice that the

DECIMAL function is also used; this casts the average result to a decimal representation with a precision of 9, and scale of 2.

Figure 9.7. Example of a column function


SELECT DECIMAL( AVG(salary), 9, 2 ) AS avgsalary 
FROM employee 
AVGSALARY
-----------
25658.28
1 record(s) selected.

9.1.6. The CAST Expression

There are many occasions where a value with a given data type needs to be cast to a different data type. For example, when manipulating data using the

DATE and

TIMESTAMP data types,

TIMESTAMP might need to be cast to

DATE . Figure 9.8 illustrates such an example.

Figure 9.8. Example of a CAST expression


SELECT CURRENT TIMESTAMP, CAST(CURRENT TIMESTAMP AS DATE) 
FROM SYSIBM.SYSDUMMY1 
1                          2
-------------------------- ----------
2005-04-01-17.00.24.637001 04/01/2005
1 record(s) selected.

9.1.7. The WHERE clause

For better performance, you should always write your SQL statements so that only the required data is returned. One way to achieve this is to limit the number of columns to be retrieved by explicitly specifying the column names in the

SELECT statement (as illustrated in previous examples). The other way is to limit the number of rows to be retrieved using the

WHERE clause. Figure 9.9 illustrates an example of a

SELECT statement that returns employees who are managers with a salary greater than $1,000.

Figure 9.9. Example of a WHERE clause


SELECT empno, firstnme, lastname 
FROM employee 
WHERE salary > 1000 
AND job = 'MANAGER' 
EMPNO  FIRSTNME     LASTNAME
------ ------------ ---------------
000020 MICHAEL      THOMPSON
000030 SALLY        KWAN
000050 JOHN         GEYER
000060 IRVING       STERN
000070 EVA          PULASKI
000090 EILEEN       HENDERSON
000100 THEODORE     SPENSER
7 record(s) selected.

9.1.8. Using FETCH FIRST
n ROWS ONLY

Sometimes you may want to obtain just the first few rows from the result set. Use the

FETCH FIRST

n

ROWS ONLY clause of the

SELECT statement to accomplish this. For example, to limit only three rows to be returned from the example illustrated in Figure 9.9, use the statement shown in Figure 9.10.

Figure 9.10. Example of FETCH FIRST
n ROWS ONLY


SELECT empno, firstnme, lastname 
FROM employee 
WHERE workdept > 'A0' 
AND job = 'MANAGER' 
FETCH FIRST 3 ROWS ONLY 
EMPNO  FIRSTNME     LASTNAME
------ ------------ ---------------
000020 MICHAEL      THOMPSON
000030 SALLY        KWAN
000050 JOHN         GEYER
3 record(s) selected.

9.1.9. The LIKE Predicate

The

LIKE predicate lets you search for patterns in character string columns. For example, the SQL statement in Figure 9.11 returns all the rows for employees whose last name starts with the letter M in the

employee table.

Figure 9.11. Example of a LIKE predicate


SELECT empno, firstnme, lastname FROM employee 
WHERE lastname LIKE 'M%' OR workdept LIKE 'D2_' 
EMPNO  FIRSTNME     LASTNAME        WORKDEPT
------ ------------ --------------- --------
000230 JAMES        JEFFERSON       D21
000260 SYBIL        JOHNSON         D21
2 record(s) selected.

In SQL, the percent sign (

% ) is a wildcard character that represents zero or more characters. It can be used any place in the search string, and as many times as you need it.

The other wildcard character used with the

LIKE predicate is the underline character (

_ ). This character represents one and only one character. In Figure 9.11, it matches items in

workdept that have strings exactly three characters long, with the first two characters of

D2 .

9.1.10. The BETWEEN Predicate

The

BETWEEN predicate lets you search for all the rows whose value falls between the values it indicates. For example, the SQL statement in Figure 9.12 returns all the rows from the employee table whose salary is between $40,000 and $50,000.

Figure 9.12. Example of a BETWEEN predicate


SELECT firstnme, lastname, salary FROM employee 
WHERE salary BETWEEN 40000 AND 50000 
FIRSTNME     LASTNAME        SALARY
------------ --------------- -----------
MICHAEL      THOMPSON           41250.00
JOHN         GEYER              40175.00
VINCENZO     LUCCHESSI          46500.00
3 record(s) selected.

9.1.11. The IN Predicate

The

IN predicate lets you search rows based on a set of values. The SQL statement in Figure 9.13 returns all the rows from the

sales table whose value in the

sales_date column is either

12/31/1995 or

03/29/1996 .

Figure 9.13. Example of an IN predicate


SELECT * FROM sales 
WHERE sales_date IN ('12/31/1995', '03/29/1996') 
SALES_DATE SALES_PERSON    REGION          SALES
---------- --------------- --------------- -----------
12/31/1995 LUCCHESSI       Ontario-South             1
12/31/1995 LEE             Ontario-South             3
12/31/1995 LEE             Quebec                    1
12/31/1995 LEE             Manitoba                  2
12/31/1995 GOUNOT          Quebec                    1
03/29/1996 LUCCHESSI       Ontario-South             3
03/29/1996 LUCCHESSI       Quebec                    1
03/29/1996 LEE             Ontario-South             2
03/29/1996 LEE             Ontario-North             2
03/29/1996 LEE             Quebec                    3
03/29/1996 LEE             Manitoba                  5
03/29/1996 GOUNOT          Ontario-South             3
03/29/1996 GOUNOT          Quebec                    1
03/29/1996 GOUNOT          Manitoba                  7
14 record(s) selected.

9.1.12. The ORDER BY Clause

SQL does not return the results retrieved in a particular order; the order of a result may be different each time when the same

SELECT statement is executed. To sort the result set, use the

ORDER BY clause as shown in Figure 9.14.

Figure 9.14. Example of an ORDER BY clause


SELECT empno, firstnme, lastname 
FROM employee 
WHERE job='MANAGER' 
ORDER BY lastname 
EMPNO  FIRSTNME     LASTNAME
------ ------------ ---------------
000050 JOHN         GEYER
000090 EILEEN       HENDERSON
000030 SALLY        KWAN
000070 EVA          PULASKI
000100 THEODORE     SPENSER
000060 IRVING       STERN
000020 MICHAEL      THOMPSON
7 record(s) selected.

Note that you must specify the column names in the

ORDER BY clause; column numbers are not allowed.

9.1.13. The GROUP BY...HAVING Clause

When you need to group multiple rows into a single row based on one or more columns, the

GROUP BY clause comes in handy. Figure 9.15 shows an example that sums up the salary of all the employees in each department. The

HAVING clause specifies which of the combined rows are to be retrieved. In the statement in Figure 9.15, only department names starting with

E are retrieved.

Figure 9.15. Example of GROUP BY and HAVING clauses


SELECT workdept, SUM(salary) AS total_salary 
FROM employee 
GROUP BY workdept 
HAVING workdept LIKE 'E%' 
WORKDEPT TOTAL_SALARY
-------- ---------------------------------
E01                               40175.00
E11                              104990.00
E21                               95310.00
3 record(s) selected.

9.1.14. Joins

Sometimes information that you want to retrieve does not reside in a single table. You can join two or more tables in a

SELECT statement. Consider the example in Figure 9.16.

Figure 9.16. Example of an INNER join


SELECT empno, firstnme, lastname, deptname 
FROM employee, department 
WHERE workdept = deptno 
AND admrdept='A00' 
EMPNO  FIRSTNME     LASTNAME        DEPTNAME                      MGRNO
------ ------------ --------------- ----------------------------- -----
000010 CHRISTINE    HAAS            SPIFFY COMPUTER SERVICE DIV.  00010
000110 VINCENZO     LUCCHESSI       SPIFFY COMPUTER SERVICE DIV.  00010
000120 SEAN         O'CONNELL       SPIFFY COMPUTER SERVICE DIV.  00010
000020 MICHAEL      THOMPSON        PLANNING                      00020
000030 SALLY        KWAN            INFORMATION CENTER            00030
000130 DOLORES      QUINTANA        INFORMATION CENTER            00030
000140 HEATHER      NICHOLLS        INFORMATION CENTER            00030
000050 JOHN         GEYER           SUPPORT SERVICES              00050
8 record(s) selected.

The example in inner join; it results in matched rows that are present in both joined tables. The

INNER JOIN keywords can be omitted as demonstrated in Figure 9.16. However, if you choose to explicitly use the

INNER JOIN syntax, the

SELECT statement in Figure 9.16 can be rewritten as the following:


SELECT empno, firstnme, lastname, deptname 
FROM employee INNER JOIN department 
    ON workdept = deptno 
WHERE admrdept='A00'

Note that

INNER JOIN is used in the

FROM clause. The

ON keyword specifies the join predicates and categorizes rows as either joined or not-joined. This is different from the

WHERE clause, which is used to filter rows.

There are three other types of joins:

LEFT OUTER JOIN, RIGHT OUTER JOIN , and

FULL OUTER JOIN . Outer joins are useful when you want to include rows that are present in the left table, right table, or both tables, in addition to the rows returned from the implied inner join. A table specified on the left side of the

OUTER JOIN operator is considered the left table, and the table specified on the right side of the

OUTER JOIN operator is considered the right table.

A left outer join includes rows from the left table that were missing from the inner join. A right outer join includes rows from the right table that were missing from the inner join. A full outer join includes rows from both the left and right tables that were missing from the inner join. Figures 9.17, 9.18, and 9.19 demonstrate information to be retrieved and an example of each join.

Figure 9.17. Example of a LEFT OUTER join

Figure 9.18. Example of a RIGHT OUTER join

Figure 9.19. Example of a FULL OUTER join

9.1.15. Working with NULLs

A NULL in DB2 represents an unknown value. The following is an example of how to check if a value is NULL:


SELECT empno FROM employee WHERE midinit IS NULL

When working with NULL values, the

COALESCE function comes in very handy: It checks whether the input is NULL. The value of the input is returned if it is not NULL, otherwise it returns the value provided in the second expression of the

COALESCE function. Refer to Figure 9.20 for an example that returns 0 if

comm is NULL.

Figure 9.20. Example of the COALESCE function


SELECT id, name, COALESCE(comm, 0) AS comm 
FROM staff 
FETCH FIRST 6 ROWS ONLY 
ID     NAME      COMM
------ --------- ---------------
10 Sanders              0.00
20 Pernal             612.45
30 Marenghi             0.00
40 O'Brien            846.55
50 Hanes                0.00
60 Quigley            650.25
6 record(s) selected.

9.1.16. The CASE Expression

When you want to perform a particular operation depending on the evaluation of a value, you can use a CASE expression to simplify your code. The example in Figure 9.21 introduces this expression.

Figure 9.21. Example of a CASE expression


SELECT firstnme, lastname, 
CASE 
WHEN salary < 10000 THEN 'Need a raise' 
WHEN salary > 10000 AND salary < 20000 THEN 'Fair pay' 
ELSE 'Overpaid' 
END AS comment 
FROM employee 
FIRSTNME     LASTNAME        COMMENT
------------ --------------- ------------
CHRISTINE    HAAS            Need a raise
MICHAEL      THOMPSON        Overpaid
SALLY        KWAN            Overpaid
JOHN         GEYER           Overpaid
IRVING       STERN           Overpaid
WILLIAM      JONES           Fair pay
6 record(s) selected.

In Figure 9.21, the values of the

salary column are evaluated. If the value is less than $10,000, the string

Need a raise is returned. If the value is between $10,000 and $20,000,

Fair pay is returned. For all other values,

Overpaid is returned.

9.1.17. Adding a Row Number to the Result Set

Recall that the