DB2考试自测题7

fengming

1. Given the following table with a primary key on empid:
Emp:
Empid  Name
11     Joe Smith
23      Melanie Jones
30     Robert Bruce
49     Janice Baker
66     Mario Diaz
68     Maria Diaton
Give the following statement in an embedded SQL program bound with Repeatable
Read:
Select * from Emp where empid < 55
How many rows in the table will be locked after the statement is run?
A.0
B.1
C.4
D.5
E.6

2. Given an embedded SQL program with a single connection, two threads and th
e following actions:
Thread 1: INSERT INTO mytab VALUES (...)
Thread 2: INSERT INTO mytab VALUES (...)
Thread 1: COMMIT
Thread 2: INSERT INTO mytab VALUES (...)
Thread 1: ROLLBACK
How many records will be successfully inserted and retained in the table myta
b?  
(Select the correct response)
A.0
B.1
C.2
D.3

3. 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' WITH CHECK OPTION
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

4. 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 statements will return all of the records ordered by j
ob with the salaries in descending order?  
(Select the correct response
A.        SELECT * FROM staff ORDER BY salary DESC, job
B.        SELECT * FROM staff GROUP BY salary DESC, job
C.        SELECT * FROM staff ORDER BY job, salary DESC
D.        SELECT * FROM staff GROUP BY job, salary DESC

5. Which of the following is the result of a successful ROLLBACK statement?
  (Select the correct response)
A.Held locks are released
B. Release-pending conditions are undone
C. Tables in LOAD PENDING are released
D. Constraint checking conditions are undone
E. Existing database connections are released

6. Which of the following processing can occur for a unit of work using an is
olation level of Cursor Stability and allows scanning through the table more
than 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. Have updated result set rows changed by other processes from one scan to t
he next
D. Have accessed result set rows changed by other processes from one scan to
the next

7. 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

8. Given the following column requirements:
Col1 Numeric Identifier - From 1 to 1000000
Col2 Job Code - Variable, 1 to 2 characters long
Col3 Job Description - Variable, 1 to 100 characters long
Col4 Job Length - Length of Job in seconds
Which of the following will minimize the disk space allocated to store the re
cords if Job Description has an average length of 45?  
(Select the correct response)
A.        CREATE TABLE tab1 (col1 INT, col2 CHAR(2), col3 CHAR(100), col4 INT)
B.        B. CREATE TABLE tab1 (col1 INT, col2 VARCHAR(2), col3 CHAR(100), col4 INT)
C.        CREATE TABLE tab1 (col1 INT, col2 CHAR(2), col3 VARCHAR(100), col4 INT)
D.        CREATE TABLE tab1 (col1 INT, col2 VARCHAR(2), col3 VARCHAR(100), col4 INT)

9. Which of the following DB2 components can limit the resource consumption o
f queries?  
(Select the correct response)
A. DB2 Connect
B. DB2 Query Patroller
C. DB2 Performance Monitor
D. DB2 Net Search Extender

10. Given the table definition:
DEFIN1:
Id      SMALLINT NOT NULL
Name    VARCHAR(30)
Hired   DATE
DEFIN2:
Deptid  SMALLINT NOT NULL
Name    VARCHAR(30)
Started DATE
Which of the following statements will insert successfully into table DEFIN1?
(Select the correct response)
A.        INSERT INTO defin1 (id) VALUES (1)
B.        INSERT INTO defin1 (name) VALUES ('Florence')
C.        INSERT INTO defin1 (id, hired) AS SELECT DISTINCT 1, CURRENT DATE FROM def
in2
D.        INSERT INTO defin1 (name, hired) SELECT DISTINCT 'Florence', CURRENT DATE
FROM defin2

11. Which of the following isolation levels will lock only the rows returned
in the result set?  
A. Read Stability
B. Repeatable Read
C. Cursor Stability
D. Uncommitted Read

12. Given the following table definitions
DEPARTMENT
Deptno     CHAR(3)
Deptname   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?
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

13. 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

14. 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

15. Which of the following tools maintains a history of all executed statemen
ts/commands for the current session within the tool?  
(Select the correct response
A. Journal
B. SQL Assist
C. DB2 Alert Center
D. DB2 Command Center

16. Given table T1 with 100 rows, which of the following queries will retriev
e 10 rows from table T1?
(Select the correct response)
A. SELECT * FROM t1 MAXIMUM 10 ROWS
B. SELECT * FROM t1 READ 10 ROWS ONLY
C. SELECT * FROM t1 OPTIMIZE FOR 10 ROWS
D. SELECT * FROM t1 FETCH FIRST 10 ROWS ONLY

17. Given the table T1, created by:
CREATE TABLE t1
(
id INTEGER GENERATED BY DEFAULT AS IDENTITY,
c1 CHAR(3)
)
The following SQL statements are issued:
INSERT INTO t1 VALUES (1, 'ABC')
INSERT INTO t1 VALUES (5, 'DEF')
Which of the following values are inserted into the ID column by the followin
g statement?
INSERT INTO t1(c1) VALUES ('XYZ')
(Select the correct response)
A.0
B.1
C.2
D.5
E.6

18. 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

19. Which of the following statements will create an index and prevent table
T1 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)

20. How many DB2 Administration Server (DAS) Instances can be set up per phys
ical machine?  
(Select the correct response)
A.0
B.1
C. One for each instance on the physical machine
D. One for each database on the physical machine

21. 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 ke
ys 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
.

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

23. Which of the following DELETE RULES on CREATE TABLE will delete a depende
nt table row if the parent table row is deleted?  
(Select the correct response
A. ON DELETE REMOVE
B. ON DELETE CASCADE
C. ON DELETE RESTRICT
D. ON DELETE SET NULL
E. ON DELETE PROPAGATE

24. 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.

25. 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)

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

27. For which of the following can locks be obtained?  
(Select the correct response)
A. A trigger
B. A table view
C. A table column
D. A database buffer
E. A row referenced by an index key

28. Which of the following privileges is necessary to populate the table with
large amounts of data?  
(Select the correct response)
A. LOAD
B. ALTER
C. UPDATE
D. IMPORT

29. 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.

30. 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

31. Using the Control Center Create Table dialog box, which of the following
dialogs allows the table creation DDL to be viewed?  
(Select the correct response
A. Copy
B. Show SQL
C. Show Related
D. Sample Contents

32. 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

33. A user has a numeric data column with a maximum value of 100,000. Which o
f the following data types will use the minimum amount of storage for the col
umn?  
(Select the correct response)
A. IDENTITY
B. BIGINT
C. INTEGER
D. SMALLINT

34. Which two of the following modes can be used on the lock table statement?
  
(Select all that apply)
A. SHARE MODE
B. EXCLUSIVE MODE
C. REPEATABLE READ MODE
D. UNCOMMITTED READ MODE
E. INTENT EXCLUSIVE MODE

35. 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

36. Which of the following is possible once a user has been given maintenance
authority?
A. DB2 userids can be created.
B. Views can be created on the catalogs.
C. Statistics can be collected for database objects
D. A table can be populated by using the LOAD command.

37. 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

38. Given the following table structure:
table1
emp_num     INT NOT NULL PRIMARY KEY
emp_fname   CHAR(30) NOT NULL
emp_lname   CHAR(30) NOT NULL
emp_addr    CHAR(60) NOT NULL
emp_pin     CHAR(10) NOT NULL
Which of the following columns can be referenced by a foreign key clause from
another table?  
(Select the correct response)
A.        emp_num
B.        emp_pin
C.        emp_addr
D.        emp_fname
E.        emp_lname

39. 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.

40. Given the table definition:
CREATE TABLE student (name CHAR(30), age INTEGER)
To list the names of the 10 youngest students, which of the following index d
efinition statements on the student table may improve the query performance?

(Select the correct response)
A.        CREATE INDEX youngest ON student (age, name)
B.        CREATE INDEX youngest ON student (name, age)
C.        CREATE INDEX youngest ON student (name, age DESC)
D.        CREATE INDEX youngest ON student (name DESC) INCLUDE (age)

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

42. With tables defined as:
Table1
col1  INT
col2  CHAR(30)

Table2
col1  INT
col2  CHAR(30)
Which of the following statements will insert all the rows in TABLE2 into TAB
LE1?
(Select the correct response)
A.        INSERT INTO table1 SELECT col1, col2 FROM table2
B.        INSERT INTO table1 AS SELECT col1, col2 FROM table2
C.        INSERT INTO table1 VALUES (table2.col1, table2.col2)
D.        INSERT INTO table1 VALUES (SELECT col1, col2 FROM table2)
E.        INSERT INTO table1 (col1,col2) VALUES (SELECT col1,col2 FROM table2)

43. 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.

44. 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.0
B.1
C.2
D.3
E.4

45. Which of the following is the most appropriate reason to consider revokin
g the SELECT privilege on the catalog tables from PUBLIC after creating a dat
abase?  
(Select the correct response
A. To prevent users from creating tables without proper authority.
B. Some system catalogs record user data in some columns, and this data may b
e confidential.
C. To prevent users from viewing passwords for other DB2 userids that DB2 sto
res in the catalog tables.
D. Some catalog tables are large, so preventing users from viewing them is a
good way to keep users from submitting long-running queries against the catal
ogs.

46. 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.

47. Given table EMPLOYEE with columns EMPNO and SALARY and table JOB with col
umns 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.

48. Which one of the following SQL statements sets the default qualifier to "
user1"?  
(Select the correct response)
A. SET CURRENT ID = 'user1'
B. SET CURRENT USER = 'user1'
C. SET CURRENT SQLID = 'user1'
D. SET CURRENT QUALIFIER = 'user1'

49. A table called EMPLOYEE has columns: name, department, and phone_number.
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

50. Which of the following can be used to determine the views that are affect
ed by a DROP TABLE statement?  
(Select the correct response)
A. DB2 Script Center
B. DB2 Performance Monitor
C DB2 Control Center, Show Related
D. DB2 Control Center, Sample Contents

51. Given two embedded SQL programs and the following actions:
Pgm1                              Pgm2
INSERT INTO mytab VALUES (...)    DELETE FROM mytab
COMMIT                            ROLLBACK
DELETE FROM mytab                 INSERT INTO mytab VALUES (...)
ROLLBACK                          COMMIT
If there exists one (1) row in table mytab before the programs are executed c
oncurrently, how many records will be in the table once the programs complete
?
(Select the correct response)
A.0
B.1
C.2
D.3
E.4

52. Given the following transaction:
CREATE TABLE dwaine.mytab (col1 INT, col2 INT)
INSERT INTO dwaine.mytab VALUES (1,2)
INSERT INTO dwaine.mytab VALUES (4,3)
ROLLBACK
Which of the following would be returned from the statement:
SELECT * FROM dwaine.mytab?
(Select the correct response)
A. COL1        COL2
----------- -----------
0 record(s) selected.
B. COL1        COL2
----------- -----------
          1           2
1 record(s) selected.

C. SQLCODE -204 indicating that "DWAINE.MYTAB" is an undefined name.
D. COL1        COL2
----------- -----------
          1           2
          4           3
2 record(s) selected.

53. 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

54. Given the following:
TAB1           TAB2
C1   C2       CX    CY
---  ---      ---   ---
A    11       A     21
B    12       C     22
C    13       D     23
The following results are desired:
C1 C2 CX CY
-- -- -- --
A  11  A   21
C  13  C  22
--  --   D  23
Which of the following joins will yield the desired results?  
(Select the correct response)
A.        SELECT * FROM tab1, tab2 WHERE c1=c0078
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 RIGHT OUTER JOIN tab2 ON c1=cx

55. Which of the following products must be installed to provide a single poi
nt of control for local and remote DB2 databases?  
(Select the correct response
A. DB2 Runtime Client
B. DB2 Administration Client
C. DB2 Connect Enterprise Edition
D. DB2 Enterprise-Extended Edition