DB2考试自测题9

fengming

（1/55）Given the following:
   TAB1    TAB2
C1  C2    CX  CY
----  ----    -----  -----
A    11    A     21
B    12    B     22
C    13    C     23
The following results are desired:
C1  C2      CX  CY
----  ----      ----  -----
A    11      A    21
B    12     -----   -----
C    13      C    22
Which of the following joins will yield the desired results?  
(Select the correct response)
A．        SELECT * FROM tab1, tab2 WHERE c1=cx
B．        SELECT * FROM tab1 INNER JOIN tab2 ON c1=cx
C．        SELECT * FROM tab1 FULL OUTER JOIN tab2 ON c1=cx
D．        SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON c1=cx

（2/55）Which of the following SQL statements can remove all rows from a tabl
e named COUNTRY?  
(Select the correct response)
A．        DELETE country
B．        DELETE FROM country
C．        DELETE * FROM country
D．        DELETE ALL FROM country

（3/55）When establishing client-server communication, passwords CANNOT be ve
rified by:  
(Select the correct response)
A．        The DRDA DB2 server.
B．        The client operating system.
C．        The gateway operating system.
D．        Looking in the catalog tables for the password.

（4/55）Which of the following occurs if an application ends abnormally durin
g 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

（5/55）Which of the following Control Center features can be used to update
information for the optimizer to choose the best path to data?  
(Select the correct response)
A．        Show Related
B．        Generate DDL
C．        Run Statistics
D．        Reorganize Table

（6/55）When granted to user1, which of the following will allow user1 to ONL
Y access 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

（7/55）With DBADM authority on the database and given the statements:
CREATE TABLE t1 (c1 CHAR(1))
INSERT INTO t1 VALUES ('b')
CREATE VIEW v1 AS SELECT c1 FROM t1 WHERE c1 ='a'
INSERT INTO v1 VALUES ('a')
INSERT INTO v1 VALUES ('b')
How many rows would be returned from the statement, SELECT c1 FROM t1?
(Select the correct response)
A．        0
B．        1
C．        2
D．        3

（8/55）Given a table T1, with a column C1 char(3), that contains strings in
upper and lower case letters, which of the following queries will find all ro
ws where C1 is the string 'ABC' in any case?  
(Select the correct response)
A．        SELECT * FROM t1 WHERE c1 = 'ABC'
B．        SELECT * FROM t1 WHERE UCASE(c1) = 'ABC'
C．        SELECT * FROM t1 WHERE IGNORE_CASE(c1 = 'ABC')
D．        SELECT * FROM t1 WHERE c1 = 'ABC' WITH OPTION CASE INSENSITIVE

（9/55）Given an embedded SQL program with a single connection, two threads a
nd the following actions:
Thread 1: INSERT INTO mytab VALUES (...)
Thread 2: INSERT INTO mytab VALUES (...)
Thread 1: ROLLBACK
Thread 2: INSERT INTO mytab VALUES (...)
Thread 1: COMMIT
How many records will be successfully inserted into the table mytab?  
(Select the correct response)
A．        0
B．        1
C．        2
D．        3

（10/55）Which of the following describes why savepoints are NOT allowed insi
de an atomic unit of work?  
(Select the correct response)
A．        Atomic units of work span multiple databases, but savepoints are limited
to units of work which operate on a single database.
B．        A savepoint implies that a subset of the work may be allowed to succeed,
while atomic operations must succeed or fail as a unit.
C．        A savepoint requires an explicit commit to be released, and commit statem
ents are not allowed in atomic operations such as compound SQL.
D．        A savepoint cannot be created without an active connection to a database,
but atomic operations can contain a CONNECT as a sub-statement.

（11/55）Given the following DDL statement:
CREATE TABLE newtab1 LIKE tab1
Which of the following would occur as a result of the statement execution?  
(Select the correct response)
A．        NEWTAB1 has same triggers as TAB1
B．        NEWTAB1 is populated with TAB1 data
C．        NEWTAB1 has the same primary key as TAB1
D．        NEWTAB1 columns have same attributes as TAB1

（12/55）The DB2 Administration Server (DAS) is required for which of the fol
lowing?  
(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

（13/55）Which of the following types of DB2 locks allows for the most concur
rency within a table?  
(Select the correct response)
A．        A row lock
B．        A page lock
C．        A field lock
D．        A column lock

（14/55）Which of the following is NOT a valid data type on CREATE TABLE?  
(Select the correct response)
A．        CLOB
B．        DOUBLE
C．        NUMERIC
D．        DATETIME

（15/55）Which of the following utilities can examine a table and its indexes
and update the system catalogs with the table's statistical information?  
(Select the correct response)
A．        runstats
B．        getstats
C．        check index
D．        chkstats

（16/55）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

（17/55）Which of the following Control Center options shows the dependencies
between a specific view and its tables?  
(Select the correct response)
A．        Show SQL
B．        Show Related
C．        Sample Contents
D．        Customize Columns

（18/55）Given the following table definition:
     STAFF
Id      INTEGER
name    CHAR(20)
dept    INTEGER
job     CHAR(20)
years   INTEGER
salary  DECIMAL(10,2)
comm.   DECIMAL(10,2)
Which of the following SQL statements will return the total number of employe
es in each department and the corresponding department id under the following
conditions:
Only return departments with at least one employee receiving a commission gre
ater than 5000. The result should be sorted by the department count from most
to least.  
(Select the correct response)
A．        SELECT dept, COUNT(id) FROM staff WHERE comm >; 5000 GROUP BY dept ORDER B
Y 2 DESC
B．        SELECT dept, COUNT(*) FROM staff GROUP BY dept HAVING comm >; 5000 ORDER B
Y 2 DESC
C．        SELECT dept, COUNT(*) FROM staff WHERE comm >; 5000 GROUP BY dept, comm OR
DER BY 2 DESC
D．        SELECT dept, comm, COUNT(id) FROM staff WHERE comm >; 5000 GROUP BY dept,
comm ORDER BY 3 DESC

（19/55）For which of the following database objects can locks be obtained?  
(Select the correct response)
A．        View
B．        Table
C．        Trigger
D．        Buffer Pool

（20/55）Given the tables:
COUNTRY
ID  NAME       PERSON  CITYES
1  Argentina   1         10
2  Canada      2         20
3  Cuba        3         10
4  Germany     1          0
5  Germany     7          5

STAFF
ID  LASTNAME
1        Jones
2        Smiths
The statement:
SELECT * FROM staff, country
will return how many rows?  
(Select the correct response)
A.        2
B.        4
C.        5
D.        7
E.        10

（21/55）A user creates the table TABLE1. Which of the following statements w
ould 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

（22/55）Which of the following tools can be used to identify inefficient SQL
statements without executing the query?  
(Select the correct response)
A.        QMF
B.        Script Center
C.        Visual Explain
D.        Performance Monitor

（23/55）Which of the following describes when indexes can be explicitly refe
renced by name within an SQL statement?  
(Select the correct response)
A.        When dropping the index
B.        When updating the index
C.        When selecting on the index
D.        When inserting using the index

（24/55） For a clustering index to be effective in keeping the data in order
, which of the following parameters must be set correctly for the index?  
(Select the correct response)
A.        FREE ROWS
B.        PERCENT FREE
C.        CLUSTERRATIO
D.        CLUSTER FACTOR

（25/55） The user USER1 is executing the statement
CREATE TABLE app1.table1 (col1 INT, col2 INT)
Which of the following privileges is required by USER1 for the statement to b
e successful?  
(Select the correct response)
A.        CREATEIN for the database
B.        CREATEIN for the schema app1
C.        CREATEIN for the schema user1
D.        CREATEIN for the table table1

（26/55） Given the statement:
CREATE TABLE t1
(
c1 INTEGER NOT NULL,
c2 INTEGER,
PRIMARY KEY(c1),
FOREIGN KEY(c2) REFERENCES t2
)
How many non-unique indexes are defined for table t1?  
(Select the correct response)
A.        0
B.        1
C.        2
D.        3

（27/55） To set up a client that can access DB2 UDB through DB2 Connect Ente
rprise Edition, which of the following is the minimum software client that mu
st be installed?  
(Select the correct response)
A.        DB2 Runtime Client
B.        DB2 Personal Edition
C.        DB2 Administration Client
D.        DB2 Application Developer's Client

（28/55）Which of the following CANNOT be used to restrict specific values fr
om being inserted into a column in a particular table?  
(Select the correct response)
A.        view
B.        index
C.        check constraint
D.        referential constraint

（29/55） Which of the following can be accomplished with a single UPDATE sta
tement?  
(Select the correct response)
A.        Updating multiple tables
B.        Updating a view consisting of joined tables
C.        Updating multiple tables based on a WHERE clause
D.        Updating a table based on a sub-select using joined tables

（30/55）User2 has DBADM authority on database DB1. This allows the user to d
o which of the following?  
(Select the correct response)
A.        Drop database DB1
B.        Backup database DB1
C.        Create tables in any database
D.        Create tables in database DB1

（31/55）Given the two following tables:
          Names
  Names            Number
Wayne Gretzky    99
Jaromir Jagr     68
Bobby Orr         4
Bobby Hull       23
Brett Hull       16
Mario Lemieux    66
Steve Yzerman    19
Claude Lemieux   19
Mark Messier     11
Mats Sundin      13

      POINTS
Names            Points
Wayne Gretzky   244
Jaromir Jagr    168
Bobby Orr       129
Bobby Hull       93
Brett Hull      121
Mario Lemieux   189
Joe Sakic        94

Which of the following statements will display the player's Names, numbers an
d points for all players with an entry in both tables?  
(Select the correct response)
A.        SELECT names.names, names.number, points.points FROM names INNER JOIN poin
ts ON names.name=points.name
B.        SELECT names.name, names.number, points.points FROM names FULL OUTER JOIN
points ON names.name=points.name
C.        SELECT names.name, names.number, points.points FROM names LEFT OUTER JOIN
points ON names.name=points.name
D.        SELECT names.name, names.number, points.points FROM names RIGHT OUTER JOIN
points ON names.name=points.name

（32/55） Which of the following can occur once connected to a database or DR
DA server with an explicit authorization name?  
(Select the correct response)
A.        Omit a user's password.
B.        Change a user's password if the server supports this function.
C.        Omit the name of the database or DRDA server if it is local.
D.        Use the commit option on the connect statement to commit in-doubt units of
work from a previous connection that was terminated.

（33/55）Which of the following DB2 components allows the analysis of multidi
mensional databases?  
(Select the correct response)
A.        DB2 Runtime Client
B.        DB2 Control Center
C.        DB2 OLAP Starter Kit
D.        DB2 Spatial Extender
E.        DB2 Performance Monitor

（34/55）Which of the following DB2 UDB isolation levels will NOT lock any ro
ws during read processing?  
(Select the correct response)
A.        Read Stability
B.        Repeatable Read
C.        Uncommited Read
D.        Cursor Stability

（35/55）Which of the following authorities should be given to the DB2 Admini
stration Server (DAS) Instance owner at the administered instance?  
(Select the correct response)
A.        DBADM
B.        SYSADM
C.        SYSCTRL
D.        SYSMAINT

（36/55）Which of the following products can be used to generate Extensible M
arkup Language documents from DB2 tables?  
(Select the correct response)
A.        Net Search
B.        XML Extender
C.        AVI Extender
D.        Text Extender

（37/55）Which of the following processing can occur for a unit of work using
an isolation level of Read Stability and scanning through the table more tha
n once 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

（38/55）Which two of the following SQL data types should be used to store bi
nary data?  
(Select all that apply)
A.        CLOB
B.        BLOB
C.        VARCHAR
D.        GRAPHIC
E.        VARCHAR FOR BIT DATA

（39/55） Given an application bound with cursor stability which will be upda
ting rows in a table and obtaining row locks, which of the following table lo
cks will DB2 acquire for the application first?  
(Select the correct response)
A.        U – update
B.        X – exclusive
C.        IU - intent update
D.        IX - intent exclusive

（40/55）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.

（41/55）In which of the following locations are the referential constraints
stored?  
(Select the correct response)
A.        The user tables.
B.        The explain tables.
C.        SYSIBM.SYSTRIGGERS.
D.        The system catalog tables.

（42/55）If a DB2 Warehouse Manager toolkit is selected during the installati
on of DB2 UDB Version 7.1, which of the following databases must be defined?

(Select the correct response)
A.        None
B.        Target Database
C.        Source Database
D.        Control Database

（43/55）Given the two following table definitions:
ORG
Deptnumb  INTEGER
Deptname  CHAR(30)
Manager   INTEGER
Division  CHAR(30)
Location  CHAR(30)

STAFF
Id     INTEGER
Name   CHAR(30)
Dept   INTEGER
Job    CHAR(20)
Years  INTEGER
Salary DECIMAL(10,2)
Comm   DECIMAL(10,2)
Which of the following statements will display each department, by name, and
the total salary of all employees in the department?  
(Select the correct response)
A.        SELECT a.deptname, SUM(b.salary) FROM org a, staff b WHERE a.deptnumb=b.de
pt ORDER BY a.deptname
B.        SELECT b.deptname, SUM(a.salary) FROM org a, staff b WHERE a.deptnumb=b.de
pt ORDER BY a.deptname
C.        SELECT a.deptname, SUM(b.salary) FROM org a, staff b WHERE a.deptnumb=b.de
pt GROUP BY a.deptname
D.        SELECT b.deptname, SUM(a.salary) FROM org a, staff b WHERE a.deptnumb=b.de
pt GROUP BY a.deptname

（44/55）Given the following scenario: An application uses a 15 digit value t
o uniquely identify customer transactions. This number is also used for arith
metic operations. Which of the following is the most efficient DB2 data type
for the column definition for this purpose?  
(Select the correct response)
A.        CHAR
B.        CLOB
C.        INTEGER
D.        NUMERIC(15,2)]
E.        DECIMAL(15,0)

（45/55）Which of the following tools allows the DBA to set limits, and be al
erted if these limits are exceeded?  
(Select the correct response)
A.        DB2 Index Wizard
B.        DB2 Script Center
C.        DB2 Command Center
D.        DB2 Performance Monitor

（46/55） Given the statement:
CREATE TABLE t1
(
c1 CHAR(3)
CONSTRAINT c1
CHECK (c1 IN ('A01','B01','C01'))
)
DB2 verifies that the table check constraint is met during which of the follo
wing actions?
(Select the correct response)
A.        Adding data using load
B.        The reorg of the table
C.        The insert of each row in t1
D.        The creation of the index for the table

（47/55）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

（48/55）Given two embedded SQL program executions with the following actions
:
Pgm1
INSERT INTO mytab VALUES (...)
COMMIT
INSERT INTO mytab VALUES (...)
ROLLBACK

Pgm2
INSERT INTO mytab VALUES (...)
ROLLBACK
INSERT INTO mytab VALUES (...)
COMMIT
How many records will be successfully inserted and retained in the table myta
b?  
(Select the correct response)
A.        1
B.        2
C.        3
D.        4

（49/55）Given the table T1 created by:
CREATE TABLE t1
(
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
c1 CHAR(10) NOT NULL,
c2 CHAR(10)
)
Which of the following INSERT statements will succeed?  
(Select the correct response)
A.        INSERT INTO t1 VALUES (1, 'abc', NULL)
B.        INSERT INTO t1 VALUES (1, NULL, 'def')
C.        INSERT INTO t1 (c1, c2) VALUES ('abc', NULL)
D.        INSERT INTO t1 (c1, c2) VALUES (NULL, 'def')

（50/55）Which of the following tasks can be performed using the ALTER TABLES
PACE statement?  
(Select the correct response)
A.        Assign a bufferpool.
B.        Change the table space name.
C.        Change the type of the table space.
D.        Change the page size of the table space.

（51/55）A view is used instead of a table for users to do which of the follo
wing?  
(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

（52/55）Given the following table definitions:
      DEPARTMENT
Deptno     CHAR(3)
Deptno     CHAR(30)
Mgrno      INTEGER
Admrdept    CHAR(3)

EMPLOYEE
Empno      INTEGER
Firstname  CHAR(30)
Midinit    CHAR
Lastname   CHAR(30)
Workdept   CHAR(3)
Which of the following statements will list the employee's employee number, l
ast name, and department name ONLY for those employees who have a department?
(Select the correct response)
A.        SELECT e.empno, e.lastname, d.deptname FROM employee e, department d WHERE
e.workdept = d.deptno
B.        SELECT e.empno, e.lastname, d.deptname FROM employee e LEFT OUTER JOIN dep
artment d ON e.workdept = d.deptno
C.        SELECT e.empno, e.lastname, d.deptname FROM employee e FULL OUTER JOIN dep
artment d ON e.workdept = d.deptno
D.        SELECT e.empno, e.lastname, d.deptname FROM employee e RIGHT OUTER JOIN de
partment d WHERE e.workdept = d.deptno

（53/55）A table called EMPLOYEE has columns: name, department, and phone_num
ber. Which of the following can limit access to the phone_number column?  
(Select the correct response)
A.        Using a view to access the table
B.        Using an index on the column
C.        Using a referential constraint on the table
D.        Using a table check constraint on the table
E.        Revoking access from the phone_number column

（54/55）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, it should only change if another agent unassigns a currently assigned sea
t.
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

（55/55）Given table EMPLOYEE with columns EMPNO and SALARY and table JOB wit
h columns ID and TITLE, what is the effect of the statement:
UPDATE employee SET salary = salary * 1.15
WHERE salary < 15000 OR
EXISTS (SELECT 1 FROM job WHERE job.id = employee.empno AND job.title = 'Mgr'
)
  (Select the correct response)
A.        Only managers that make less than 15,000 are given salary increases.
B.        Only non-managers that make less than 15,000 are given salaray increases.
C.        Employees that make less than 15,000 but no managers are given salary incr
eases.
D.        Employees that make less than 15,000 and all managers are given salary inc
reases.