DB2考试自测题11

fengming

1. Which of the following is the result of the following SQL statement:
CREATE UNIQUE INDEX empno_ind ON employee (empno)
(Select the correct response)
A. Every value for EMPNO must be unique.
B. UPDATE statements on EMPNO will be rolled back.
C. Insert statements on EMPNO will always be faster.
D. Insert statements on the EMPNO table will result in clustered data.

2. Which of the following will rebuild a package in the database from the exi
sting catalog information?  
(Select the correct response)
A. bind
B. rebind
C. update
D. rebuild

3. Why is a unique index not sufficient for creation of a primary key?  
(Select the correct response)
A.  It is sufficient - a primary key is the same thing as a unique index.
B.  Unique indexes can be defined in ascending or descending order. Primary k
eys must be ascending.
C. A unique index can be defined over a column or columns that allow nulls. P
rimary keys cannot contain nulls.
D. A unique index can be defined over a column or columns that allow nulls. T
his is not allowed for primary keys because foreign keys cannot contain nulls
.

4. Which of the following statements will create an index and prevent table T
1 from containing two or more rows with the same values for column C1?  
(Select the correct response)
A. CREATE UNIQUE INDEX ix4 ON t1 (c1)
B. CREATE DISTINCT INDEX ix1 ON t1 (c1)
C. CREATE UNIQUE INDEX ix6 ON t1 (c1,c2)
D. CREATE DISTINCT INDEX ix3 ON t1 (c1,c2)

5. Which of the following DB2 data types is used to store 50 MB of binary dat
a as a single value?  
(Select the correct response)
A. BLOB
B. CLOB
C. DBCLOB
D. FOR BIT DATA
E. VARCHAR FOR BIT DATA

6. Given the following UPDATE statement:
UPDATE address2 SET housenumber_buildingname=
(SELECT buildingname FROM address1
WHERE address2.id = address1.id)
WHERE HOUSENUMBER_BUILDINGNAME IS NULL
Which of the following describes the result of the statement?
(Select the correct response)
A.        The statement will succeed.
B.        The statement will fail because a subquery cannot exist in an UPDATE state
ment.
C.        The statement will succeed only if ADDRESS1.ID and ADDRESS2.ID are defined
as primary keys.
D.        The statement will succeed if the data retrieved from the subquery does no
t have duplicate values for ADDRESS1.ID.

7. Which two of the following types of storage management method is supported
by DB2 OLAP Server ?  
(Select all that apply)
A. Object
B. Network
C. Relational
D. Hierachical
E. Multi-dimensional

8. Given the table:
COUNTRY
ID   NAME           PERSON     CITIES
1    Argentina           1         10
2    Canada              2         20
3    Cuba                2         10
4    Germany             1          0
5    France              7          5
Which of the following clauses when added to the statement
SELECT cities, name FROM country
returns rows sorted by NAME and then sorted by the number of cities (CITIES)?
  
(Select the correct response)
A.        ORDER BY 2,1
B.        GROUP BY 2, 1
C.        ORDER BY cities, name
D.        GROUP BY cities, name

9. A view is used instead of a table for users to do which of the following?

(Select the correct response)
A. Avoid allocating more disk space per database
B. Provide users with the ability to define indexes
C. Restrict user's access to a subset of the table data
D. Avoid allocating frequently used query result tables

10. Which of the following must be set up to allow the Control Center to view
database objects?  
(Select the correct response)
A. ODBC
B. JAVA
C. DB2 Administration Server
D. Client Configuration Assistant

11. Which of the following does NOT end a unit of work?  
(Select the correct response)
A. COMMIT
B. ROLLBACK
C. TERMINATE
D. SAVEPOINT
E. CONNECT RESET

12. Given the statement:
CREATE TABLE t1 (c1 CHAR(1))
Data has been inserted into the table with rows of a,b,c,d,e,f. Given the fol
lowing command is issued:
ALTER TABLE t1 ADD CONSTRAINT con1 CHECK (c1 ='a')
Which of the following occurs?  
(Select the correct response)
A.        Rows with c1 values of b,c,d,e,f are deleted
B.        Rows with c1 values of b,c,d,e,f have c1 set to NULL
C.        The ALTER command will fail as rows violate the constraint
D.        The ALTER command will move the violating rows to the exception table

13. Which of the following DB2 CLP options specify the file that contains the
statements to be executed?  
(Select the correct response)
A. –f
B. –b
C. –o
D. –w

14. Which of the following is the best way to restrict user access to a subse
t of columns in a table?  
(Select the correct response)
A. Only grant access to the columns within a table that a user is allowed to
see.
B. Create a view that only includes the columns a user is allowed to see. Gra
nt the user access to the view, not the base table.
C. Create two tables: one with the columns that a user is allowed to see, and
one that has the confidential columns, and use a join when all data must be
presented.
D. Create two tables: one with the columns that a user is allowed to see, and
one that has the confidential columns, and use a union when all data must be
presented.

15. Given the following embedded SQL programs:
Program 1:
CREATE TABLE mytab (col1 INT, col2 CHAR(24))
COMMIT
Program 2:
INSERT INTO mytab VALUES ( 20989,'Joe Smith')
INSERT INTO mytab VALUES ( 21334,'Amy Johnson')
COMMIT
DELETE FROM mytab
ROLLBACK
INSERT INTO mytab VALUES ( 23430,'Jason French')
ROLLBACK
INSERT INTO mytab VALUES ( 20993,'Samantha Jones')
COMMIT
DELETE FROM mytab WHERE col1=20993
ROLLBACK
Which of the following indicates the number of records that will be returned
by the statement:
SELECT * FROM mytab?
(Select the correct response)
A..
B.1
C.2
D.3
E.4

16. When manually establishing communications from a Windows NT client throug
h a DB2 Connect gateway to DB2 UDB for OS/390, which of the following is NOT
required to catalog?  
(Select the correct response)
A. The client.
B. The database on the DRDA server.
C. The Database Connection Service database.
D. The node where the DB2 Connect Gateway is.

17. Which of the following products can be used to perform a dictionary-based
search?  
(Select the correct response)
A. Net.Data
B. XML Extender
C. AVI Extender
D. Text Extender

18. Given the following DDL statements,
CREATE TABLE t1 (a INT, b INT, c INT)
CREATE VIEW v1 AS SELECT a, b, c FROM t1
WHERE a >; 250
WITH CHECK OPTION
Which of the following INSERT statements will fail?
(Select the correct response)
A.        INSERT INTO t1 VALUES (200, 2, 3)
B.        INSERT INTO v1 VALUES (200, 2, 3)
C.        INSERT INTO t1 VALUES (300, 2, 3)
D.        INSERT INTO v1 VALUES (300, 2, 3)

19. Given the two following tables:
Points
Name            Points
Wayne Gretzky   244
Jaromir Jagr    168
Bobby Orr       129
Bobby Hull      93
Brett Hull      121
Mario Lemieux   189
  
PIM
Name            PIM
Mats Sundin     14
Jaromir Jagr    18
Bobby Orr       12
Mark Messier    32
Brett Hull      66
Mario Lemieux   23
Joe Sakic       94
Which of the following statements will display the player's Names, points and
PIM for all players?
(Select the correct response)
A.        SELECT points.name, points.points, pim.name, pim.pim FROM points INNER JOI
N pim ON points.name=pim.name
B.        SELECT points.name, points.points, pim.name, pim.pim FROM points FULL OUTE
R JOIN pim ON points.name=pim.name
C.        SELECT points.name, points.points, pim.name, pim.pim FROM points LEFT OUTE
R JOIN pim ON points.name=pim.name
D.        SELECT points.name, points.points, pim.name, pim.pim FROM points RIGHT OUT
ER JOIN pim ON points.name=pim.name

20. Which of the following is the implicit qualifier for a declared temporary
table?  
(Select the correct response)
A. The schema name SYSCAT.
B. The schema name SESSION.
C. The schema name TEMPUSER.
D. The userid specified with the BIND command.
E. The userid who established the connection to the database and declared the
temporary table.

21. Which of the following SQL statements can remove all rows from a table na
med COUNTRY?  
(Select the correct response
A. DELETE country
B. DELETE FROM country
C. DELETE * FROM country
D. DELETE ALL FROM country

22. Which of the following occurs if an application ends abnormally during an
active unit of work?  
(Select the correct response)
A. Current unit of work is committed
B. Current unit of work is rolled back
C. Current unit of work remains active
D. Current unit of work moves to pending state

23．A user creates the table TABLE1. Which of the following statements would
explicitly give USER1 the ability to read rows from the table?  
(Select the correct response)
A．        GRANT VIEW TO user1 ON TABLE table1
B．        GRANT READ TO user1 ON TABLE table1
C．        GRANT SELECT ON TABLE table1 TO user1
D．        GRANT ACCESS ON TABLE table1 TO user1

24．Which of the following tools can be used to identify inefficient SQL stat
ements without executing the query?  
(Select the correct response)
A．        QMF
B．        Script Center
C．        Visual Explain
D．        Performance Monitor

25．The DB2 Administration Server (DAS) is required for which of the followin
g?  
(Select the correct response)
A．        For the administrator user id to install or remove DB2
B．        For the remote clients to use the Control Center against the instance
C．        For checking authorities in the database manager configuration for SYSADM
D．        For maintaining authorities added and removed by the SQL GRANT and REVOKE
commands respectively

26．Which of the following authorities should be given to the DB2 Administrat
ion Server (DAS) Instance owner at the administered instance?  
(Select the correct response)
A．        DBADM
B．        SYSADM
C．        SYSCTRL
D．        SYSMAINT

27．Which two of the following DB2 authorization groups are authorized to cre
ate a table within database sample?  
(Select all that apply)
A．        DBADM
B．        DBCTRL
C．        SYSADM
D．        DBMAINT
E．        ALTERIN
F．        SYSMAINT

28．Given the following:
A table containing a list of all seats on an airplane. A seat consists of a s
eat number and whether or not it is assigned. An airline agent lists all the
unassigned seats on the plane. When the agent refreshes the list from the tab
le, the list should not change.
Which of the following isolation levels should be used for this application?

(Select the correct response)
A．        Read stability
B．        Repeatable read
C．        Cursor stability
D．        Uncommitted read

29．Which of the following is the result of the following SQL statement:
ALTER TABLE table1 ADD col2 INT WITH DEFAULT  
(Select the correct response)
A．        The statement fails with a negative SQL code.
B．        The statement fails because no default value is specified.
C．        A new column called COL2 is added to TABLE1 and populated with zeros.
D．        A new column called COL2 is added to TABLE1 and populated with nulls.
E．        A new column called COL2, which cannot contain nulls, is added to TABLE1.

30．To set up a client that can access DB2 UDB through DB2 Connect Enterprise
Edition, which of the following is the minimum software client that must be
installed?  
(Select the correct response)
A．        DB2 Runtime Client
B．        DB2 Personal Edition
C．        DB2 Administration Client
D．        DB2 Application Developer's Client

31．Which of the following types of DB2 locks allows for the most concurrency
within a table?  
(Select the correct response)
A．        A row lock
B．        A page lock
C．        A field lock
D．        A column lock

32．Which of the following processing can occur for a unit of work using an i
solation level of Read Stability and scanning through the table more than onc
e within the unit of work?  
(Select the correct response)

A．        Access uncommitted changes made by other processes
B．        Update uncommitted changes made by other processes
C．        Rows added to a result set by other processes from one scan to the next
D．        Rows changed in a result set by other processes from one scan to the next

33．Given the following SQL statements:
CREATE TABLE tab1 (col1 INT)
CREATE TABLE tab2 (col1 INT)
INSERT INTO tab1 VALUES (NULL),(1)
INSERT INTO tab2 VALUES (NULL),(1)
SELECT COUNT(*) FROM tab1
    WHERE col1 IN
(SELECT col1 FROM tab2)
Which of the following is the result of the SELECT COUNT(*) statement?  
(Select the correct response)
A．        1
B．        2
C．        3
D．        4
E．        0

34．Given an application bound with cursor stability which will be updating r
ows in a table and obtaining row locks, which of the following table locks wi
ll DB2 acquire for the application first?  
(Select the correct response)
A．        U – update
B．        X – exclusive
C．        IU - intent update
D．        IX - intent exclusive

35．When granted to user1, which of the following will allow user1 to ONLY ac
cess table data?  
(Select the correct response)
A．        DBADM authority
B．        SYSADM authority
C．        SELECT privilege on the table
D．        SELECT privilege WITH GRANT OPTION on the table