DB2 9 Family Fundamentals v5.0 (C2090-730)

Page:    1 / 21   
Total 301 questions

Given the following tables:

YEAR_2006 -

EMPID NAME -
----- ---------------
1 Jagger, Mick
2Richards, Keith
3 Wood, Ronnie
4 Watts, Charlie
5 Jones, Darryl
6 Leavell, Chuck

YEAR_1962 -

EMPID NAME -
----- ---------------
1 Jagger, Mick
2 Richards, Keith
3 Jones, Brian
4 Wyman, Bill
5 Watts, Charlie
6 Stewart, Ian
If the following SQL statement is executed, how many rows will be returned?

SELECT name FROM year_2007 -

UNION ALL -

SELECT name FROM year_1962 -

  • A. 6
  • B. 9
  • C. 10
  • D. 12


Answer : D

Which of the following is the lowest cost DB2 product that can be legally installed on a
Linux server that has 6 CPUs?

  • A. DB2 Express Edition
  • B. DB2 Personal Edition
  • C. DB2 Workgroup Server Edition
  • D. DB2 Enterprise Server Edition


Answer : D

Which of the following describes how DB2 9 stores an XML document if the
XMLExtender is not used?

  • A. CLOB
  • B. BLOB
  • C. Hierarchically
  • D. Rows and columns


Answer : C

Given the following table and XML data stored in the CONTACTINFO column:
CREATE TABLE clients(
id INT PRIMARY KEY NOT NULL,
name VARCHAR(50),
statusVARCHAR(10),
contactinfo XML)
<Client>
<Address>
<street>25 EastCreek</street>
<city>Toronto</city>
<prov-state>Ontario</prov-state>
<pcode-zip>M8X-3T6</pcode-zip>
</Address>
<phone>
<work>4165551358</work>
<home>9051112222</home>
<fax>9051112222</fax>
</phone>
<preferredcontact>by fax</preferredcontact>
</Client>
What is the result of the following XQuery expression?
for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client//fax return $y

  • A. 9051112222
  • B. <fax>9051112222</fax>
  • C. No results because there is no <fax> child element of <Client>
  • D. No results because the XQuery expression is not a valid FLWOR expression


Answer : B

Which of the following tools for DB2 V9 allows a user to create and debug a
SQL stored procedure?

  • A. Control Center
  • B. Development Center
  • C. Developer Workbench
  • D. Stored ProcedureBuilder Security


Answer : C

Given the following requirements:
Create a table to contain employee data, with a unique numeric identifier automatically assigned when a row is added, has an EDLEVEL column that permits only the values 'C',
'H' and 'N', and permits inserts only when a corresponding value for the employee's department exists in the DEPARTMENT table.
Which of the following CREATE statements will successfully create this table?

  • A. CREATE TABLE emp ( empno SMALLINT NEXTVAL GENERATED ALWAYS AS IDENTITY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, workdept CHAR(3) NOT NULL, edlevel CHAR(1), PRIMARY KEY emp_pk (empno), FOREIGN KEY emp_workdept_fk ON (workdept) REFERENCES department (deptno), CHECKedlevel_ck VALUES (edlevel IN ('C','H','N')), );
  • B. CREATE TABLE emp ( empno SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, workdept CHAR(3), edlevel CHAR(1), CONSTRAINT emp_pk PRIMARY KEY(empno), CONSTRAINT emp_workdept_fk FOREIGN KEY (workdept) REFERENCES department (deptno), CONSTRAINT edlevel_ck CHECK edlevel VALUES ('C','H','N') );
  • C. CREATE TABLE emp ( empno SMALLINT NEXTVAL GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(30) NOTNULL, lastname VARCHAR(30) NOT NULL, workdept CHAR(3) NOT NULL, edlevel CHAR(1) CHECK IN ('C','H','N')), CONSTRAINT emp_pk PRIMARY KEY (empno), CONSTRAINT emp_workdept_fk FOREIGN KEY department (deptno) REFERENCES (workdept) );
  • D. CREATE TABLE emp ( empnoSMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, workdept CHAR(3), edlevel CHAR(1), CONSTRAINT emp_pk PRIMARY KEY (empno), CONSTRAINT emp_workdept_fk FOREIGN KEY (workdept) REFERENCESdepartment (deptno), CONSTRAINT edlevel_ck CHECK (edlevel IN ('C','H','N')) );


Answer : D

Which ofthe following database objects can be used to raise an error if a table is updated?

  • A. Package
  • B. Trigger
  • C. Stored Procedure
  • D. Informational Constraint


Answer : B

Which of the following will DELETE all of the rows from table T03?

  • A. DELETE * FROM TABLE T03
  • B. DELETE ALL FROM T03
  • C. DELETE * FROM T03
  • D. DELETE FROMT03


Answer : D

A table was created using the following DDL:

CREATE TABLE employee -
(id SMALLINT NOT NULL,
name VARCHAR(9),
dept SMALLINT CHECK (dept BETWEEN 10AND 100),
job CHAR(10) CHECK (job IN ('Sales','Mgr','Clerk')),
hiredate DATE,
salary DECIMAL(7,2),
comm DECIMAL(7,2),
PRIMARY KEY (id),
CONSTRAINT yearsal CHECK (YEAR(hiredate) > 2004 OR salary > 80500)
);
Which of the following INSERT statements will fail?

  • A. INSERT INTO employee VALUES (2, 'Smith', 80, 'Mgr', '09/03/2006', 80000, NULL)
  • B. INSERT INTO employee VALUES (4, 'Smith', 86, 'Mgr', '07/14/2003', 90000, NULL)
  • C. INSERT INTO employee VALUES (1, 'Smith', 55, 'Sales', '07/14/2003', NULL, NULL)
  • D. INSERT INTO employee VALUES (3, 'Smith', 33, 'Analyst', '11/26/2006', 90000, NULL)


Answer : D

Which of the following commandsis used to retrieve database names from the local catalog for DRDA host databases on System i and System z?

  • A. LIST DB DIRECTORY
  • B. LIST DCS DIRECTORY
  • C. LIST NODE DIRECTORY
  • D. LIST ACTIVE DATABASES


Answer : B

Consider the following table called EMPLOYEES:

ID FIRSTNAME LASTNAME JOB LEVEL -
------------------------------------------------------------
1 Paul Jones DBA 2
2 George Baker MGR 1
3 Roger Melvin CLERK 3
4 Jim Smith MGR1
5 Kevin Street CLERK 3
6 Chris Eaton MGR 1
If the following SQL statement is executed, how many rows will be deleted? DELETE

FROM employees WHERE 1 = 1 -

  • A. 0
  • B. 1
  • C. 3
  • D. 6


Answer : D

Given the following table definitions:

EMPLOYEES -
------------------------------------------------

EMPID INTEGER -
NAME CHAR(20)
DEPTID CHAR(3)
SALARY DECIMAL(10,2)
COMMISSION DECIMAL(8,2)

DEPARTMENTS -
------------------------------------------------

DEPTNO INTEGER -
DEPTNAME CHAR(20)
Which of the following statements will produce a result data set that satisfies all of these conditions:
>Displays the total number of employees in each department
>> Displays the corresponding department name for each department ID
>> Sorted by department employee count, from greatest to least

  • A. SELECT *, COUNT(empno) FROM departments, employees WHEREdeptid = deptno GROUP BY deptname ORDER BY 2 DESC
  • B. SELECT deptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC
  • C. SELECT deptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUPBY deptname ORDER BY 2 ASC
  • D. SELECT deptname, COUNT(*) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2


Answer : B

Whileattempting to connect to a database stored on an iSeries server from a Windows client, the following message was displayed:
SQL1013N The database alias name or database name "TEST_DB" could not be found.
Which of the following actions can be used to help determine why this message was displayed?

  • A. Execute the LIST REMOTE DATABASES command on the server; look for an entry for the TEST_DB database
  • B. Execute the LIST DCS DIRECTORY command on the server; look for an entry for the TEST_DB database
  • C. Executethe LIST REMOTE DATABASES command on the client; look for an entry for the TEST_DB database
  • D. Execute the LIST DCS DIRECTORY command on the client; look for an entry for the TEST_DB database


Answer : D

Which of the following DB2 data types can be used tostore 1000 MB of single-byte character data?

  • A. BLOB
  • B. CLOB
  • C. DBCLOB
  • D. GRAPHIC


Answer : B

An application needs to store a 5 MBJPEG image in a DB2 table. Which data type should be specified for the column that will be used for storing the image?

  • A. GRAPHIC
  • B. BINARY
  • C. IMAGE
  • D. BLOB


Answer : D

Page:    1 / 21   
Total 301 questions