10 tips on How to C2090-735 Test Like a Badass [21 to 30]

Act now and download your IBM C2090-735 test today! Do not waste time for the worthless IBM C2090-735 tutorials. Download Improve IBM DB2 9.5 SQL Procedure Developer (C2090-735) exam with real questions and answers and begin to learn IBM C2090-735 with a classic professional.

2016 Jul C2090-735 Study Guide Questions:

Q21. Click the Exhibit button. 

***MISSING EXHIBIT*** 

CREATE PROCEDURE proc.conv_temp(IN o_temp REAL, 

IN conv CHAR(3), 

OUT n_temp REAL) 

DYNAMIC RESULT SETS 0 

CONTAINS SQL 

LANGUAGE SQL 

BEGIN 

DECLARE temp_value REAL; 

IF conv = 'F-C' THEN 

SET temp_value = (o_temp – 32); 

SET n_temp = (5 * temp_value) / 9; 

ELSEIF conv = 'C-F' THEN 

SET temp_value = (9 * o_temp) / 5; 

SET n_temp = (temp_value + 32); 

END IF; 

END 

An SQL procedure was created using the statement shown in the exhibit.What is the correct way to invoke this procedure from the DB2 Command Line Processor? 

A. CALL proc.conv_temp(98.6, 'F-C', 0) 

B. SELECT location, proc.conv_temp(temp, F-C, 0) AS temp FROM geo_data 

C. CALL proc.conv_temp(98.6, 'F-C', ?) 

D. SELECT location, proc.conv_temp(temp, F-C, ?) AS temp FROM geo_data 

Answer: C 

Q22. Which two procedures demonstrate the correct use of dynamic SQL? (Choose two.) 

A. CREATE PROCEDURE update_count1 (IN new_count INTEGER, IN item_code 

INTEGER) 

BEGIN 

DECLARE v_dynSQL VARCHAR(200); 

SET v_dynSQL = 'UPDATE stock SET quantity_on_hand=? WHERE item_number=?'; 

PREPARE v_stmt1 FROM v_dynSQL; 

EXECUTE v_stmt1 USING new_count, item_code; 

END 

B. CREATE PROCEDURE update_count2 (IN tab_name VARCHAR(128), IN 

new_count INTEGER, IN 

item_code INTEGER) 

BEGIN 

DECLARE v_dynSQL VARCHAR(200); 

SET v_dynSQL = 'UPDATE ? SET quantity_on_hand=? WHERE item_number=?'; 

PREPARE v_stmt1 FROM v_dynSQL; 

EXECUTE v_stmt1 USING tab_name, new_count, item_code; 

END 

C. CREATE PROCEDURE update_count3 (IN new_count INTEGER, IN item_code 

INTEGER) 

BEGIN 

DECLARE v_dynSQL VARCHAR(200); 

SET v_dynSQL = 'UPDATE stock SET quantity_on_hand=' || CHAR(new_count) || ' 

WHERE 

item_number=' || CHAR(item_code); 

EXECUTE IMMEDIATE v_dynSQL; 

END 

D. CREATE PROCEDURE update_count4 (IN tab_name VARCHAR(128), IN 

col_name1 VARCHAR(128), IN 

col_name2 VARCHAR(128), IN new_count INTEGER, IN item_code INTEGER) 

BEGIN 

DECLARE v_dynSQL VARCHAR(200); 

SET v_dynSQL = 'UPDATE ? SET ?=? WHERE ?=?'; 

PREPARE v_stmt1 FROM v_dynSQL; 

EXECUTE v_stmt1 USING tab_name, col_name1, new_count, col_name2, item_code; 

END 

E. CREATE PROCEDURE update_count5 (IN new_count INTEGER, IN item_code 

INTEGER) 

BEGIN 

DECLARE v_dynSQL VARCHAR(200); 

DECLARE v_col_name VARCHAR(128); 

SET v_col_name = 'item_number'; 

SET v_dynSQL = 'UPDATE stock SET quantity_on_hand=? WHERE ?=?'; 

PREPARE v_stmt1 FROM v_dynSQL; 

EXECUTE v_stmt1 USING new_count, v_col_name, item_code; 

END 

Answer: A,C 

Q23. Click on the Exhibit button. 

***MISSING EXHIBIT*** 

CREATE TYPE phonenumbers AS VARCHAR(12) ARRAY[1000]@ CREATE 

PROCEDURE find_customers( IN numbers_in phonenumbers, IN area_code CHAR(3), 

OUT 

numbers_out phonenumbers) BEGIN DECLARE i, j, max INTEGER; 

SET i = 1; 

SET j = 1; 

SET numbers_out = NULL; 

SET max = CARDINALITY(numbers_in); 

WHILE i <= max DO 

IF SUBSTR(numbers_in[i], 1, 3) = area_code THEN 

SET numbers_out[j] = numbers_in[i]; 

SET j = j + 1; 

END IF; 

SET i = i + 1; 

END WHILE; 

END 

Referring to the exhibit, what is the OUT_PHONENUMBERS result set when the SQL procedure is invoked by the call statement shown below? 

CALL find_customers(ARRAY[416-305-3745, 

905-414-4565, 

416-305-3746, 

'905-414-4566'], 

416,?) 

A. [416-305-3745,905-414-4565, 416-305-3746'] 

B. [416-305-3745,905-414-4565, 416-305-3746, '905-414-4566'] 

C. [905-414-4565, '905-414-4566'] 

D. [416-305-3745, 416-305-3746] 

Answer: D 

C2090-735  exam guide

Regenerate C2090-735 vce:

Q24. Click the Exhibit button. 

***MISSING EXHIBIT*** 

CREATE PROCEDURE p_exit (IN p_in INTEGER, OUT p_out INTEGER) BEGIN 

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SET p_out = 99; 

END; 

IF (p_in < 5) THEN SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'Custom 

SQL State'; 

ELSEIF (p_in > 10) THEN RETURN 3; 

ELSE SET p_out = 11; 

END IF; 

RETURN 29; 

END 

Given the SQL procedure definition shown in the exhibit, what will the value of the P_OUT parameter be if the procedure is invoked with a value of 11 for the P_IN parameter? 

A. 3 

B. 29 

C. 99 

D. NULL 

Answer: D 

Q25. Which steps must be followed to return a result set from an SQL procedure? 

A. 1. Create the procedure using the DYNAMIC RESULT SETS clause. 

2. Declare the cursor. 

3. Open the cursor in the SQL procedure. 

4. Close the cursor. 

5. Return to the application. 

B. 1. Create the procedure using the DYNAMIC RESULT SETS clause. 

2. Declare the cursor using the WITH RETURN clause. 

3. Open the cursor in the SQL procedure. 

4. Return to the application. 

C. 1. Create the procedure using the WITH RETURN clause. 

2. Declare the cursor using the DYNAMIC RESULT SETS clause. 

3. Open the cursor in the SQL procedure. 

4. Return to the application. 

D. 1. Create the procedure using the WITH RETURN clause. 

2. Declare the cursor using the DYNAMIC RESULT SETS clause. 

3. Open the cursor in the SQL procedure. 

4. Close the cursor. 

Answer: B 

Q26. What are two valid DECLARE statements in an SQL procedure? (Choose two.) 

A. DECLARE var1 INTEGER; 

B. DECLARE var1 DECIMAL [9]; 

C. DECLARE var1 XML; 

D. DECLARE var1 CURRENT DATE; 

E. DECLARE var1[10] INTEGER; 

Answer: A,C 

certleader.com

Breathing C2090-735 torrent:

Q27. Given the SQL statement shown below: 

CREATE TRIGGER hr.raise_limit 

AFTER UPDATE OF salary ON employee 

REFERENCING NEW AS n OLD AS o FOR EACH ROW 

WHEN (n.salary > 1.1 * o.salary) 

SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%' 

Which two privileges allow a user to execute the CREATE TRIGGER statement shown? 

(Choose two.) 

A. DBADM authority 

B. SYSCTRL authority 

C. SELECT and UPDATE privilege on the EMPLOYEE table 

D. SYSMAINT authority 

E. SELECT and ALTER privilege on the EMPLOYEE table 

Answer: A,C 

Q28. Which CREATE PROCEDURE statement option should be used if you plan on issuing a DECLARE GLOBAL TEMPORARY TABLE statement from within the SQL procedure body? 

A. CONTAINS SQL 

B. READS SQL DATA 

C. MODIFIES SQL DATA 

D. LANGUAGE SQL 

Answer: C 

Q29. Which statement is true for a global temporary table? 

A. Results from manipulation of the data cannot to be stored temporarily in a table. 

B. A user temporary table space must exist before global temporary tables can be created. 

C. The data in the temporary table continues to persist after termination anddisconnection from the database. 

D. Global temporary tables can be created on demand. 

Answer: B 

Q30. Click the Exhibit button. 

***MISSING EXHIBIT*** 

CREATE FUNCTION e_degree(edlevel SMALLINT) 

RETURNS CHAR(12) 

LANGUAGE SQL 

CONTAINS SQL 

BEGIN ATOMIC 

DECLARE degree CHAR(12); 

IF edlevel= 12 THEN 

SET degree = 'HIGH SCHOOL'; 

ELSEIF edlevel = 14 THEN 

SET degree = 'ASSOCIATE'; 

ELSEIF edlevel = 16 THEN 

SET degree = 'BACHELOR'; 

ELSEIF edlevel = 18 THEN 

SET degree = 'MASTER'; 

ELSEIF edlevel = 20 THEN 

SET degree = 'DOCTORATE'; 

ELSE 

SET degree = 'UNKNOWN'; 

END IF; 

RETURN degree; 

END 

An SQL function was created using the statement shown in the exhibit. Assume this function is invoked by executing the SQL statement shown below: 

VALUES e_degree(SMALLINT(17)) 

What will be returned? 

A. SQL0440N No authorized routine named "E_DEGREE" of type "FUNCTION"having compatible arguments was found. SQLSTATE=42884 

B. 1 

BACHELOR 1 record(s) selected. 

C. 1 

MASTER 1 record(s) selected. 

D. 1 

UNKNOWN 1 record(s) selected. 

Answer: D 

About C2090-735 Information: C2090-735 Dumps

Certleader Dumps
Certleader is a company specialized on providing high quality IT exam materials and fully committed to assist our respected clients crack any IT certification tests on their 1st efforts.