DB2 9 Family Fundamentals v5.0

Page:    1 / 21   
Exam contains 304 questions
Expand All

Given the following table:

TAB1 -

COL1 COL2 -
----- -----

A 10 -

B 20 -

C 30 -

D 40 -

E 50 -
And the following SQL statements:

DECLARE c1 CURSOR WITH HOLD FOR -
SELECT * FROM tab1 ORDER BY col_1;
OPEN c1;
FETCH c1;
FETCH c1;
FETCH c1;
COMMIT;
FETCH c1;
CLOSE c1;
FETCH c1;
Which of the following is the last value obtained for COL_2?

  • A. 20
  • B. 30
  • C. 40
  • D. 50


Answer : C

Given the following requirements:
Create a table named TESTTAB, which has an identity column named ACTIVITYNO.
Define the identity column to generate the values for the column by default. Start the values at 10 and increment by 10. Makethe identity column unique.
Which of the following CREATE statements will successfully create this table?

  • A. CREATE TABLE TESTTAB (ACTIVITYNO SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 10), ACTKWD CHAR(6) NOT NULL, ACTDESCVARCHAR(20) NOT NULL, UNIQUE(ACTIVITYNO))
  • B. CREATE TABLE TESTTAB (ACTIVITYNO SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 10), ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, UNIQUE(ACTNO))
  • C. CREATE TABLE TESTTAB (ACTIVITYNO SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 1), ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, UNIQUE(ACTIVITYNO))
  • D. CREATE TABLE TESTTAB (ACTIVITYNO SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10), ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, UNIQUE(ACTIVITYNO))


Answer : D

Which of the following will provide user USER1 and all members of the group GROUP1 with the ability to perform DML, but no other operations on table TABLE1?

  • A. GRANT INSERT, UPDATE,DELETE, SELECT ON TABLE table1 TO user1 AND group1
  • B. GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE table1 TO USER user1, GROUP group1
  • C. GRANT ALL PRIVILEGES EXCEPT ALTER, INDEX, REFERENCES ON TABLE table1 TO USER user1, GROUP group1
  • D. GRANT CONTROL ON TABLE table1 TO user1 AND group1


Answer : B

Given the following table definitions:

EMPLOYEE -

ID NAME DEPTID -
-- ------ ---------
01 Smith 10
02 Bossy 20
03 Peterson 20
04 Goss 30
05 Pape 40
06 Avery 50
07 O'Neal 60
08 Carter 50

DEPARTMENT -

ID DEPTNAME -
----------------
05 Hardware
10 Kitchen
20 Shoes
30 Toys
40 Electronics
50 Automotive
and the following query:

SELECT e.id, d.deptname -

FROM employee e, department d -
WHERE e.deptid = d.id AND e.id >4
Which of the following queries will produce the same result set as the query above?

  • A. SELECT e.id, d.deptname FROM employee e, department d WHERE e.id > 4
  • B. SELECT e.id, d.deptname FROM employee e INNER JOIN department d ON e.deptid = d.id WHERE e.id > 4
  • C. SELECT e.id, d.deptname FROM employee e FULL OUTER JOIN department d ON e.id = d.id WHERE e.id >4
  • D. SELECT e.id, d.deptname FROM employee e LEFT OUTER JOIN department d ON e.deptid = d.id WHERE e.id > 4 UNION ALL SELECT e.id, d.deptname FROM employee e RIGHT OUTER JOIN department d ON e.deptid = d.id WHERE e.id > 4


Answer : B

Which authority or privilege is granted by the DB2Database Manager configuration file?

  • A. CONNECT
  • B. CONTROL
  • C. SYSMAINT
  • D. EXECUTE


Answer : C

Given the following scenario:
Table TABLE1 needs to hold specific numericvalues up to 9999999.999 in column COL1.
Once TABLE1 is populated, arithmetic operations will be performed on data stored in column COL1.
Which of the following would be the most appropriate DB2 data type to use for column
COL1?

  • A. INTEGER
  • B. REAL
  • C. NUMERIC(7, 3)
  • D. DECIMAL(10, 3)


Answer : D

Whichof the following statements eliminates all but one of each set of duplicate rows in the
DEPT column in the STAFF table?

  • A. SELECT UNIQUE dept FROM staff
  • B. SELECT DISTINCT dept FROM staff
  • C. SELECT (dept) UNIQUE FROM staff
  • D. SELECT (dept) DISTINCT FROM staff


Answer : B

Given that tables T1 and T2 contain the followingrows:
Table T1:

C1 C2 -
--- ---
1 4
1 3
1 2
Table T2:

C1 C2 -
--- ---
1 1
1 2
1 3
Which of the following queries will return only those rows that exist in both T1 and T2?

  • A. SELECT * FROM t1 UNION SELECT * FROM t2
  • B. SELECT * FROM t1 UNION DISTINCT SELECT *FROM t2
  • C. SELECT * FROM t1 INTERSECT SELECT * FROM t2
  • D. SELECT * FROM t1 WHERE (c1,c2)= (SELECT c1,c2 FROM t2)


Answer : C

Which of the following products is used to shred extensible markup language documents?

  • A. DB2 AVI Extender
  • B. DB2 Text Extender
  • C. DB2XML Extender
  • D. DB2 Spatial Extender


Answer : C

Given the following two tables:

NAMES -

NAME NUMBER -
-------------------------

Wayne Gretzky 99 -

Jaromir Jagr 68 -

Bobby Orr 4 -

Bobby Hull 23 -

Brett Hull 16 -

Mario Lemieux 66 -

Mark Messier 11 -

POINTS -

NAME POINTS -
----------- --------------

Wayne Gretzky 244 -

Jaromir Jagr 168 -

Bobby Orr 129 -

Brett Hull 121 -

Mario Lemieux 189 -

Joe Sakic 94 -
Which of the following statements will display the player name, number, andpoints for all players that have scored points?

  • A. SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name
  • B. SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name
  • C. SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name
  • D. SELECT p.name, n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name


Answer : C

What type of constraint is used to ensure that each row inserted into the EMPLOYEE table with a value in the WORKDEPT column has a row with a corresponding value in the
DEPTNO column of the DEPARTMENT table?

  • A. A check constraint on the EMPLOYEE table
  • B. A unique constraint on the EMPLOYEE table WORKDEPT column
  • C. A foreign key reference from the DEPARTMENT tables DEPTNO column to the WORKDEPT column of the EMPLOYEE table
  • D. A foreign key reference from the EMPLOYEE tables WORKDEPT column to the DEPTNO column of the DEPARTMENT table


Answer : D

Which of the following is TRUE for the DB2 isolation level Cursor Stability (CS)?

  • A. An application process acquires at least a share lock on the current row of every cursor.
  • B. Any row that is read during aunit of work cannot be changed by other application processes until the unit of work is complete.
  • C. Any row changed by another application process can be read, even if the change has not been committed by that application process.
  • D. An application process that issues the same query more than once in a unit of work will not see additional rows caused by other application processes appending new information to the database.


Answer : A

If the following SQL statement is executed:

CREATE TABLE sales -
(invoice_no NOT NULL PRIMARYKEY,
sales_date DATE,
sales_amt NUMERIC(7,2))

IN tbsp0, tbsp1, tbsp2, tbsp3 -
PARTITION BY RANGE (sales_date NULLS FIRST)
(STARTING 1/1/2007' ENDING '12/31/2007'
EVERY 3 MONTHS)
Which of the following statements is true?

  • A. Administrative tasks such as backing up, restoring, and reorganizing data stored in the SALES table must be done at the table level; not at the partition level
  • B. Data can be quickly rolled in and out of the SALES table by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement
  • C. If an index is created for the SALES table, its data must be stored in table space TBSP0
  • D. When resolving queries against the SALES table, each partition used is scanned asynchronously and the results of each partition scan are merged to produce the result data set returned


Answer : B

Which of the following is a characteristic of a sequence?

  • A. A sequence will never generate duplicate values.
  • B. The MAXVALUE of a sequence can be equal to the MINVALUE.
  • C. It is not possible to create a sequence that generates a constant since the INCREMENT value must be greater than zero.
  • D. When a sequence cycles back to either the MAXVALUE or MINVALUE, it will always be equal to the specified value of either of these two boundaries.


Answer : B

Given the following table definition:

EMPLOYESS -
--------------------------

EMP ID INTEGER -
NAME CHAR(20)
DEPT CHAR(10)
SALARY DECIMAL (10, 2)
COMMISSION DECIMAL (8, 2)
Assuming the DEPT column contains the values ADMIN, PRODUCTION, and SALES, which of the following statements will produce a result data set in which all ADMIN department employees are grouped together, all PRODUCTION department employees are grouped together, and all SALES department employees are grouped together?

  • A. SELECT name, deptFROM employees ORDER BY dept
  • B. SELECT name, dept FROM employees GROUP BY dept
  • C. SELECT name, dept FROM employees GROUP BY ROLLUP (dept)
  • D. SELECT name, dept FROM employees GROUP BY CUBE (dept)


Answer : A

Page:    1 / 21   
Exam contains 304 questions
Expand All

Talk to us!

Have any questions or issues ? Please dont hesitate to contact us

[email protected]

Certlibrary.com is owned by MBS Tech Limited: Room 1905 Nam Wo Hong Building, 148 Wing Lok Street, Sheung Wan, Hong Kong. Company registration number: 2310926
Certlibrary doesn't offer Real Microsoft Exam Questions. Certlibrary Materials do not contain actual questions and answers from Cisco's Certification Exams.
CFA Institute does not endorse, promote or warrant the accuracy or quality of Certlibrary. CFA® and Chartered Financial Analyst® are registered trademarks owned by CFA Institute.
Terms & Conditions | Privacy Policy