Ensurepass.com : Ensure you pass the IT Exams
2018 Jan IBM Official New Released C2090-543
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/C2090-543.html

DB2 9.7 Application Development

Question No: 31 – (Topic 1)

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?

  1. 2

  2. 3

  3. 4

  4. 6

Answer: C

Question No: 32 – (Topic 1)

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?

  1. SELECT * FROM countries LEFT OUTER JOIN continents ON countries.continent_id =

    continents.id AND

    countries.country = #39;Greece#39;;

  2. SELECT * FROM countries LEFT OUTER JOIN continents ON countries.continent_id = continents.id

    AND

    continents.id = 1 FETCH FIRST 1 ROWS ONLY;

  3. SELECT countries.country, continents.continent FROM countries RIGHT OUTER JOIN continents

    ON

    countries.continent_id = continents.id AND continents.id = 1;

  4. SELECT countries.country, continents.continent FROM countries, continents WHERE countries.continent_id =

continents.id AND country = #39;Greece#39;;

Answer: D

Question No: 33 – (Topic 1)

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

  1. CREATE PROCEDURE proc1() DYNAMIC RESULTS SET 1 LANGUAGE SQL

    SELECT * FROM t1

  2. CREATE PROCEDURE proc1() DYNAMIC RESULTS SET 1 LANGUAGE SQL

    BEGIN

    DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM t1; OPEN c1;

    END

  3. CREATE PROCEDURE proc1() DYNAMIC RESULTS SET 1 LANGUAGE SQL

    RETURN (SELECT * FROM t1)

  4. CREATE PROCEDURE proc1() DYNAMIC RESULTS SET 1 LANGUAGE SQL

BEGIN

DECLARE c1 CURSOR FOR SELECT * FROM t1; RETURN c1;

END

Answer: B

Question No: 34 – (Topic 1)

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?

  1. module

  2. temp file

  3. locator

  4. log file

Answer: C

Question No: 35 – (Topic 1)

Which standard is used for XML document validation in DB2?

  1. DTD

  2. RelaxNG

  3. XMLSchema

  4. Schematron

Answer: C

Question No: 36 – (Topic 1)

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

  1. an ORDER BY clause

  2. a GROUP BY clause

  3. a WHERE clause

  4. a DISTINCT clause

Answer: C

Question No: 37 – (Topic 1)

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?

  1. The UPDATE operation will succeed only if ADDRESS.EMPID has been defined as a primary key.

  2. The UPDATE operation will succeed only if both ADDRESS.EMPID and EMPLOYEE.EMPID have been defined as primary keys.

  3. The UPDATE operation will succeed only if the ADDRESS table does not contain duplicate values for EMPID.

  4. The UPDATE operation will succeed only if the data retrieved by the sub query does not contain duplicate values for EMPLOYEE.EMPID.

Answer: D

Question No: 38 – (Topic 1)

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?

  1. FULL OUTER JOIN

  2. EXCEPTION JOIN

  3. CROSS JOIN

  4. INNER JOIN

Answer: C

Question No: 39 – (Topic 1)

Click the Exhibit button.

CREATE TABLE s1.mytab (col1 INTEGER GENERATED ALWAYS AS IDENTITY, col2 INTEGER, col3 INTEGER, CHECK (col1 col3 lt; 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.)

  1. SELECT col1, outcol=add100(col2,col3) FROM mytab

  2. SET var1 = ABS(add100(1,99))

  3. SELECT col1, add100(col2,col3) AS outcol FROM mytab

  4. SELECT outcol.* FROM TABLE(add100(col2,col3)) AS outcol

Answer: B,C

Question No: 40 – (Topic 1)

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?

  1. 0

  2. 1

  3. 2

  4. 3

Answer: C

100% Ensurepass Free Download!
Download Free Demo:C2090-543 Demo PDF
100% Ensurepass Free Guaranteed!
C2090-543 Dumps

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.