DB2 9.7 Application Development v6.0

Page:    1 / 7   
Exam contains 100 questions

Given the two tables shown below:

TAB1 -

LETTER GRADE -

A 80 -

B 70 -

C 60 -

TAB2 -

LETTER GPA -

A 4 -

C 2 -

D 1 -
and the successful execution of the following query:
SELECT * FROM tab1 FULL OUTER JOIN tab2 ON tab1.letter = tab2.letter;
How many rows will be returned?

  • A. 2
  • B. 3
  • C. 4
  • D. 6


Answer : C

Given the two tables shown below:

COUNTRIES -
----------

COUNTRY CONTINENT_ID -

Greece 1 -

Germany 1 -

Canada 2 -

CONTINENTS -
-----------

ID CONTINENT -
1 Europe
2 North America
3 Asia
A user wants the following result:

COUNTRY CONTINENT -

Greece Europe -
Which query should the user submit to achieve the result?

  • A. SELECT * FROM countries LEFT OUTER JOIN continents ON countries.continent_id = continents.id AND countries.country = 'Greece';
  • B. SELECT * FROM countries LEFT OUTER JOIN continents ON countries.continent_id = continents.id AND continents.id = 1 FETCH FIRST 1 ROWS ONLY;
  • C. SELECT countries.country, continents.continent FROM countries RIGHT OUTER JOIN continents ON countries.continent_id = continents.id AND continents.id = 1;
  • D. SELECT countries.country, continents.continent FROM countries, continents WHERE countries.continent_id = continents.id AND country = 'Greece';


Answer : D

Which SQL procedure will retrieve all the rows from table T1 and make those rows available to the invoker as a result set?

  • A. CREATE PROCEDURE proc1() DYNAMIC RESULTS SET 1 LANGUAGE SQL SELECT * FROM t1
  • B. CREATE PROCEDURE proc1() DYNAMIC RESULTS SET 1 LANGUAGE SQL BEGIN DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM t1; OPEN c1; END
  • C. CREATE PROCEDURE proc1() DYNAMIC RESULTS SET 1 LANGUAGE SQL RETURN (SELECT * FROM t1)
  • D. CREATE PROCEDURE proc1() DYNAMIC RESULTS SET 1 LANGUAGE SQL BEGIN DECLARE c1 CURSOR FOR SELECT * FROM t1; RETURN c1; END


Answer : B

The table shown below exists within a database:
CREATE TABLE s1.t1 ( c1 INTEGER NOT NULL PRIMARY KEY, c2 CLOB( 2G ) )
A CLI/ODBC application uses the SQLGetSubString() API to retrieve the first 1024 and last
1024 bytes of data from column C2.
Which mechanism is used to minimize the amount of data sent to the client for this operation?

  • A. module
  • B. temp file
  • C. locator
  • D. log file


Answer : C

Which standard is used for XML document validation in DB2?

  • A. DTD
  • B. RelaxNG
  • C. XMLSchema
  • D. Schematron


Answer : C

In a query, which clause can reference a CLOB data type?

  • A. an ORDER BY clause
  • B. a GROUP BY clause
  • C. a WHERE clause
  • D. a DISTINCT clause


Answer : C

Given the SQL statement shown below:
UPDATE address SET number_street =
(SELECT address FROM employee
WHERE address.empid = employee.empid)

WHERE number_street IS NULL -
Which comment is true?

  • A. The UPDATE operation will succeed only if ADDRESS.EMPID has been defined as a primary key.
  • B. The UPDATE operation will succeed only if both ADDRESS.EMPID and EMPLOYEE.EMPID have been defined as primary keys.
  • C. The UPDATE operation will succeed only if the ADDRESS table does not contain duplicate values for EMPID.
  • D. The UPDATE operation will succeed only if the data retrieved by the sub query does not contain duplicate values for EMPLOYEE.EMPID.


Answer : D

Which type of join condition specifies that the result table will contain a row for each row from the Table to the left, concatenated with each row from the table to the right?

  • A. FULL OUTER JOIN
  • B. EXCEPTION JOIN
  • C. CROSS JOIN
  • D. INNER JOIN


Answer : C

Click the Exhibit button.
CREATE TABLE s1.mytab (col1 INTEGER GENERATED ALWAYS AS IDENTITY, col2
INTEGER, col3 INTEGER, CHECK (col1+col3 < 500) CREATE VARIABLE s1.var1
INTEGER DEFAULT (99) CREATE FUNCTION s1.add100 (p1 INT, p2 INT) RETURNS
INTEGER LANGUAGE SQL DETERMINISTIC NOT FENCED BEGIN SET p1= p2+100;

RETURN p1;END -
Given the table, variable, and function definitions shown in the exhibit, which two statements contain a valid invocation of the add100 function? (Choose two.)

  • A. SELECT col1, outcol=add100(col2,col3) FROM mytab
  • B. SET var1 = ABS(add100(1,99))
  • C. SELECT col1, add100(col2,col3) AS outcol FROM mytab
  • D. SELECT outcol.* FROM TABLE(add100(col2,col3)) AS outcol


Answer : B,C

Click the Exhibit button.
CONNECT TO test;
CREATE TABLE tab_a (col1 INT);
CREATE TABLE tab_b (col1 INT);
INSERT INTO tab_a VALUES (1), (2), (3), (4);
INSERT INTO tab_b VALUES (1), (1), (2), (2);
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit was executed successfully.
If the statement shown below is executed:
UPDATE tab_a SET col1 = 10 WHERE col1 IN (SELECT * FROM tab_b);
How many rows in table TAB_A will be modified?

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


Answer : C

You have created a stored procedure (MULTIRESULTS()) on the server which will return multiple result sets. Whiledeveloping a PHP application using the IBM_DB2 extension, you want to call the stored procedure. The code shown below will execute the stored procedure:
$stmt = db2_exec($conn, 'CALL multiResults()');
How can you fetch all of the result sets from $stmt?

  • A. Call db2_fetch_object($stmt) for the first result set; call db2_next_result($stmt) for more result sets.
  • B. Call db2_next_result($stmt) for each result set.
  • C. Call db2_fetch_object($stmt) for each result set.
  • D. Call db2_next_result($stmt) for each result set; call db2_fetch_object($stmt) for each row in the result set.


Answer : A

You have set your data types as CHAR(10), VARCHAR(20). Given the operation shown below: CHAR(10) UNION VARCHAR(20)What will be the final data type?

  • A. CHAR(200)
  • B. VARCHAR(30)
  • C. VARCHAR(20)
  • D. CHAR(30)


Answer : C

An application contains the statements shown below:
DECLARE csr1 CURSOR WITH HOLD FOR SELECT * FROM employee;
OPEN csr1;
FETCH csr1;
DECLARE csr2 CURSOR FOR SELECT * FROM employee;
OPEN csr2;
COMMIT;
CLOSE csr1;
When will cursor CSR1 lose position?

  • A. When the application issues the COMMIT statement.
  • B. When the last row is read from the CSR1 result set.
  • C. When cursor CSR2 is opened.
  • D. When cursor CSR1 is closed.


Answer : D

A library keeps digital copies of books in the table shown below:
CREATE TABLE library.collection (
bookid INTEGER NOT NULL PRIMARY KEY,
ebook_content BLOB(2G) )
A CLI/ODBC application is written that retrieves the EBOOK_CONTENT for a given
BOOKID in 32K pieces.
Which two APIs can be used to perform the retrieval? (Choose two.)

  • A. SQLGetLength()
  • B. SQLGetData()
  • C. SQLGetSubString()
  • D. SQLFetchNext()


Answer : B,C

Click the Exhibit button.
CONNECT TO test;
CREATE TABLE project (projno CHAR(6), deptno CHAR(3), projname VARCHAR(20));
CREATE TABLE employee (empno CHAR(6), lastname VARCHAR(25), deptno CHAR(3));
COMMIT;
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit was executed successfully.
Which query will produce a list of all projects and all employees assigned to projects and also include employees that have not been assigned a project?

  • A. SELECT empno, lastname, workdept, projname FROM employee RIGHT OUTER JOIN project ON employee.deptno = project.deptno
  • B. SELECT empno,lastname, employee.deptno, projname FROM employee FULL OUTER JOIN project ON employee.deptno = project.deptno
  • C. SELECT empno, lastname, employee.deptno, projname FROM employee LEFT OUTER JOIN project ON employee.deptno = project.deptno
  • D. SELECT empno, lastname, employee.deptno, projname FROM employee JOIN project ON employee.deptno = project.deptno


Answer : C

Page:    1 / 7   
Exam contains 100 questions

Talk to us!


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