Chapter 9


A1:

A. The condition of having lastname starting with the letter P returns two rows. However, the result set is further reduced to one row to obtain the only record that has a salary greater than $38,500.

A2:

D. Unknown values are identified by NULL. You verify the nullability of a value by checking IS NULL. The values '' and "" are known values of empty strings.

A3:

C. Selecting from an UPDATE, DELETE, or INSERT statement is an SQL enhancement available in DB2 Version 8.1 FixPak 4 or later. If the statement is executed on DB2 Version 8.1 with an earlier FixPak, it will fail. With FixPak 4 or later, the statement will execute the DELETE statement and return the number of rows removed.

A4:

C. An INTERSECT operation retrieves the matching set of distinct values (there are three distinct values) from the two columns. On the other hand, the INTERSECT ALL operator will return four rows because it returns all of the matching rows.

A5:

C. The UNIX command interpreter processes the * character, so to pass this character to DB2, you must enclose the command in quotes. To select all rows from a table you use the SELECT * syntax.

A6:

D.FETCH FIRST n ROWS ONLY limits the number of rows returned from a query. Use of the OPTIMIZE FOR n ROWS clause influences query optimization, based on the assumption that n rows will be retrieved. However, all rows of the result table will be retrieved.

A7:

A.% is used as a wildcard character in a LIKE clause of a SELECT statement.

A8:

C and D. Values to be inserted into the employee table must provide an id value in INTEGER data type, optionally provide a name, and dept must not be NULL. If no value is specified for dept, a default value A00 will be used. You can choose to use the keyword DEFAULT to indicate that the default value defined in the table definition is used (in this case A00). Alternatively, simply do not specify a value for the column (as demonstrated in answers C and D).

A9:

A. Data is stored in no particular order. If data should be returned in certain order, the ORDER BY clause must be used.

A10:

B. The DB2 special register CURRENT DATE stores the date based on the time-of day clock at the DB2 server. To perform date and time calculations, simply add or subtract the number of years, months, days, hours, minutes, or seconds. For example: CURRENT DATE + 3 YEARS + 2 MONTHS 3 MINUTES

A11:

B.

A12:

B. The special register CURRENT DATE can be used to retrieve the current system date.

A13:

A.

A14:

D. There will be six rows:

 col 1                 col2 -------               ------       2               Susan       4               Mary       5               Clara       6               Jenny       8               Tom       9               Luisa 

A15:

C. There will be two rows:

 COL1        COL2                 COL1        COL2 ----------- -------------------- ----------- --------------------           2 Raul                           2 Susan           9 Glenn                          9 Luisa 

A16:

D. There will be six rows:

 COL1        COL2                 COL1        COL2 ----------- -------------------- ----------- --------------------           2 Raul                           2 Susan           - -                              5 Clara           - -                              6 Jenny           9 Glenn                          9 Luisa           4 Mary                           - -           8 Tom                            - - 

A17:

C. There will be four rows:

 COL1        COL2                 COL1        COL2 ----------- -------------------- ----------- --------------------           2 Raul                           2 Susan           4 Mary                           - -           8 Tom                            - -           9 Glenn                          9 Luisa 

A18:

C. There will be four rows:

 COL1        COL2                 COL1        COL2 ----------- -------------------- ----------- --------------------           2 Raul                           2 Susan           - -                              5 Clara           - -                              6 Jenny           9 Glenn                          9 Luisa 

A19:

B. There will be two records successfully inserted into the employee table:

 EMPNO  SALARY ------ ----------- 000999           - 001000           -   2 record(s) selected. 

A20:

E. There were 10 records and 2 more were inserted, so there will be 12 records after executing the statement.



Understanding DB2(R. Learning Visually with Examples)
Understanding DB2: Learning Visually with Examples (2nd Edition)
ISBN: 0131580183
EAN: 2147483647
Year: 2004
Pages: 313

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net