db2考试自测题1带答案选项.docx

1、db2考试自测题1带答案选项1) Which of the following products is required to be installed in order to build an application on AIX, which will access a DB2 UDB for OS/390 database? a) DB2 Connect Personal Edition b) DB2 Universal Database Workgroup Edition c) DB2 Personal Developers Edition d) DB2 Universal Devel

2、opers Edition 2) Which of the following tools can be used to catalog a database? a) Journal b) Alert Center c) License Center d) Client Configuration Assistant 3) Which of the following utilities would you run to order data and reclaim space from deleted rows in a table: a) reorg b) db2look c) db2mo

3、ve d) runstats 4) The purpose of the USE privilege is to: a) query data in a table. b) load data into a table. c) create tables within a table space. d) create table spaces within a database. 5) Which two of the following authorities can create a database? a) DBADM b) SYSADM c) DBCTRL d) SYSCTRL e)

4、SYSMAINT 6) Cataloging a remote database is: a) Performed on a PC or UNIX machine to identify the server the DB2 database manager is on. b) Performed on a PC or UNIX machine to identify the DB2 database to users and applications. c) Never performed in DB2, as only one database per node is allowed, s

5、o cataloging a node automatically catalogs the database at that node. d) Performed on a PC or UNIX machine to open the catalogs in the DB2 database and present a user with a list of all accessible tables in that database. 7) Given the create statements: CREATE DISTINCT TYPE kph AS INTEGER WITH COMPA

6、RISONS CREATE DISTINCT TYPE mph AS INTEGER WITH COMPARISONS CREATE TABLE speed_limits (route_num SMALLINT, canada_sl KPH NOT NULL, us_sl MPH NOT NULL) Which of the following is a valid query? a) SELECT route_num FROM speed_limits WHERE canada_sl 80 b) SELECT route_num FROM speed_limits WHERE canada_

7、sl kph c) SELECT route_num FROM speed_limits WHERE canada_sl us_sl d) SELECT route_num FROM speed_limits WHERE canada_sl kph(80) 8) If, for a given table, the Control Center does not show the choice Generate DDL, which of the following describes the reason? a) The table is a system object. b) The ta

8、ble is a summary table. c) The table is in LOAD PENDING. d) The table is a replicated table. e) The table was created by a different user. 9) Given the tables: 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 STAFF ID LASTNAME 1 Jones 2 Smith Which

9、of the following statements removes the rows from the COUNTRY table that have PERSONS in the STAFF table? a) DELETE FROM country WHERE id IN (SELECT id FROM staff) b) DELETE FROM country WHERE id IN (SELECT person FROM staff) c) DELETE FROM country WHERE person IN (SELECT id FROM staff) d) DELETE FR

10、OM country WHERE person IN (SELECT person FROM staff) 10) The table STOCK has the following column definitions: type CHAR (1) status CHAR(1) quantity INTEGER price DEC (7,2) Items are indicated to be out of stock by setting STATUS to NULL and QUANTITY and PRICE to zero. Which of the following statem

11、ents updates the STOCK table to indicate that all the items except for those with TYPE of S are temporarily out of stock? a) UPDATE stock SET status=NULL, quantity=0, price=0 WHERE type S b) UPDATE stock SET (status, quantity, price) = (NULL, 0, 0) WHERE type S c) UPDATE stock SET (status, quantity,

12、 price) = (NULL, 0, 0) WHERE type S d) UPDATE stock SET status = NULL, SET quantity=0, SET price = 0 WHERE type S 11) Which of the following products can be used to store image data in a DB2 database? a) Net.Data b) Net Search c) DB2 AVI Extenders d) DB2 XML Extenders e) DB2 Text Extenders 12) Which

13、 of the following CANNOT have an autocommit setting? a) Embedded SQL b) The Command Center c) The Command Line Processor d) The DB2 Call Level Interface 13) Which of the following statements eliminates all but one of each set of duplicate rows in the final result table? a) SELECT UNIQUE * FROM t1 b)

14、 SELECT DISTINCT * FROM t1 c) SELECT * FROM DISTINCT T1 d) SELECT UNIQUE (*) FROM t1 e) SELECT DISTINCT (*) FROM t1 14) Given the table: STAFF ID LASTNAME 1 Jones 2 Smith 3 Which of the following statements removes all rows from the table where there is a NULL value for LASTNAME? a) DELETE FROM staf

15、f WHERE lastname IS NULL b) DELETE ALL FROM staff WHERE lastname IS NULL c) DELETE FROM staff WHERE lastname = NULL d) DELETE ALL FROM staff WHERE lastname = NULL 15) Given the following table definitions: DEPARTMENT deptno CHAR(3) deptname CHAR(30) mgrno INTEGER admrdept CHAR(3) EMPLOYEE empno INTE

16、GER firstname CHAR(30) midinit CHAR lastname CHAR(30) workdept CHAR(3) Which of the following statements will list every employees number and last name with the employee number and last name of their manager, including employees without a manager? a) SELECT e.empno, e.lastname, m.empno, m.lastname F

17、ROM employee e LEFT INNER JOIN department INNER JOIN employee m ON mgrno = m.empno ON e.workdept = deptno b) SELECT e.empno, e.lastname, m.empno, m.lastname, FROM employee e LEFT OUTER JOIN department INNER JOIN employee m ON mgrno = m.empno ON e.workdept = deptno c) SELECT e.empno, e.lastname, m.em

18、pno, m.lastname FROM employee e RIGHT OUTER JOIN department INNER JOIN employee m ON mgrno = m.empno ON e.workdept = deptno d) SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e RIGHT INNER JOIN department INNER JOIN employee m ON mgrno = m.empno ON e.workdept = deptno 16) Given the fol

19、lowing tables: NAMES Name 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 Name Points Wayne Gretzky 244 Jaromir Jagr 168 Bobby Orr 129 Bobby Hull 93 Brett Hull 121 Mario Lemieux

20、189 PIM Name PIM Mats Sundin 14 Bobby Orr 12 Mark Messier 32 Brett Hull 66 Mario Lemieux 23 Joe Sakic 94 Which of the following statements will display the players Names, numbers, points and PIM for all players with an entry in all three tables? a) SELECT names.name, names.number, points.points, pim

21、.pim FROM names INNER JOIN points ON names.name=points.name INNER JOIN pim ON pim.name=names.name b) SELECT names.name, names.number, points.points, pim.pim FROM names OUTER JOIN points ON names.name=points.name OUTER JOIN pim ON pim.name=names.name c) SELECT names.name, names.number, points.points,

22、 pim.pim FROM names LEFT OUTER JOIN points ON names.name=points.name LEFT OUTER JOIN pim ON pim.name =names.name d) SELECT names.name, names.number, points.points, pim.pim FROM names RIGHT OUTER JOIN points ON names.name=points.name RIGHT OUTER JOIN pim ON pim.name=names.name 17) Given the tables: E

23、MPLOYEE emp_num emp_name dept 1 Adams 1 2 Jones 1 3 Smith 2 4 Williams 1 DEPT dept_id dept_name 1 Planning 1 Support and the statement: ALTER TABLE employee ADD FOREIGN KEY (dept) REFERENCES (dept_id) ON DELETE CASCADE How many units of work will be needed to process this statement: DELETE FROM dept

24、 WHERE dept_id=1 a) 0 b) 1 c) 2 d) 3 e) 4 f) 6 18. Given the requirements to store names, employee numbers, and when the employees were hired, which of the following DB2 data types CANNOT be used to contain the day the employee was hired? a) CLOB b) TIME c) VARCHAR d) TIMESTAMP 19) Given the transac

25、tion: CREATE TABLE t1 (id INTEGER,CONSTRAINT chkid CHECK (id100) INSERT INTO t1 VALUES (100) COMMIT Which of the following results from the transaction? a) The row is inserted with a NULL value b) The row is inserted with a value of 100 c) The row insertion with a value of 100 is rejected d) The tri

26、gger called chkid is fired to validate the data 20) If a table is defined with a check constraint for one or more columns, which of the following will perform the data validation after the table is loaded with the load utility. a) Reorg b) Check c) Runstats d) Image Copy e) Set Integrity 21) Given t

27、he statement: CREATE VIEW v1 AS SELECT c1 FROM t1 WHERE c1=a WITH CHECK OPTION Which of the following SQL statements will insert data into the table? a) INSERT INTO v1 VALUES (a) b) INSERT INTO v1 VALUES (b) c) INSERT INTO v1 VALUES (b) d) INSERT INTO v1 VALUES (a) e) INSERT INTO v1 VALUES (ab) 22)

28、An update lock gets released by an application using the repeatable read isolation level during which of the following? a) If the cursor accessing the row is closed. b) If the transaction issues a ROLLBACK statement. c) If the cursor accessing the row is moved to the next row. d) If the transaction

29、changes are made via an UPDATE statement. 23) Which of the following isolation levels is most likely to acquire a table level lock during an index scan? a) Read Stability b) Repeatable Read c) Cursor Stability d) Uncommitted Read 24) Which of the following releases a lock by an application using the

30、 cursor stability isolation level? a) If the cursor accessing the row is moved to the next row b) If the cursor accessing the row is used to update the row c) If the applications current row is deleted by the application d) If the applications current row needs to be updated by another applic ation

31、25) Which of the following processes is NOT performed by DB2 Warehouse Manager? a) Query b) Loading c) Extraction d) Transformation 26) Which of the following DB2 components allows reference to Oracle and DB2 databases in a single query? a) DB2 Query Patroller b) DB2 Warehouse Manager c) DB2 Relational Connect d) DB2 Connect Enterprise Edition 27) Given the table: STAFF ID LASTNAME 1 Jones 2 Smith When issuing the query SE