Free C2090-730 Exam Dumps Preview

Given the following two tables:

TAB1
----------------------
COL_1 COL_2
----- -----
A 10
B 12
C 14

TAB2
----------------------
COL_A COL_B
----- -----
A 21
C 23

D 25

Assuming the following results are desired:

COL_1 COL_2 COL_A COL_B
A 10 A 21
B 12 - -
C 14 C 23
- - D 25

Which of the following joins will produce the desired results?

A table named DEPARTMENT has the following columns:

DEPT_ID
DEPT_NAME
MANAGER
AVG_SALARY

Which of the following is the best way to prevent most users from viewing AVG_SALARY data?

Given the following CREATE TABLE statement: CREATE TABLE customer(custid INTEGER, info XML)
And the following INSERT statements: INSERT INTO customer VALUES (1000,
'<customerinfo xmlns="http://custrecord.dat" custid="1000"> <name>John Doe</name>
<addr country="United States">
<street>25 East Creek Drive</street>
<city>Raleigh</city>
<state-prov>North Carolina</state-prov>
<zip-pcode>27603</zip-pcode>
</addr>
<phone type="work">919-555-1212</phone>
<email>[email protected]</email>
</customerinfo>');

INSERT INTO customer VALUES (1000,
'<customerinfo xmlns="http://custrecord.dat" custid="1001"> <name>Paul Smith</name>
<addr country="Canada">
<street>412 Stewart Drive</street>
<city>Toronto</city>
<state-prov>Ontario</state-prov>
<zip-pcode>M8X-3T6</zip-pcode>
</addr>
<phone type="work">919-555-4444</phone>
<email>[email protected]</email>
</customerinfo>');

What is the result of the following XQuery expression?

XQUERY declare default element namespace "http://custrecord.dat"; for $info in db2-fn:xmlcolumn
('CUSTOMER.INFO')/customerinfo where $info/addr/state-prov="Ontario" return $info/name/text();

Which of the following is a valid DB2 data type?

Which of the following tools can be used to schedule a backup operation that is to be run every Sunday evening?

A transaction using the Read Stability isolation level scans the same table multiple times before it terminates.

Which of the following can occur within this transaction's processing?

Given the following set of statements:

CREATE TABLE tab1 (col1 INTEGER, col2 CHAR(20));
COMMIT;
INSERT INTO tab1 VALUES (123, 'Red');
INSERT INTO tab1 VALUES (456, 'Yellow');
SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS;
DELETE FROM tab1 WHERE col1 = 123;
INSERT INTO tab1 VALUES (789, 'Blue');
ROLLBACK TO SAVEPOINT s1;
INSERT INTO tab1 VALUES (789, 'Green');
UPDATE tab1 SET col2 = NULL WHERE col1 = 789;
COMMIT;

Which of the following records would be returned by the following statement?

SELECT * FROM tab1

Given the following statements:

CREATE TABLE tab1 (col1 INT);
CREATE TABLE tab2 (col1 INT);
CREATE TRIGGER trig1 AFTER UPDATE ON tab1
REFERENCING NEW AS new1
FOR EACH ROW MODE DB2SQL
INSERT INTO tab2 VALUES(new1.col1);
INSERT INTO tab1 VALUES(2),(3);

What is the result of the following query?

SELECT count(*) FROM tab2;

Which of the following products must be installed on an AIX server in order to build an application for AIX that will access a DB2 for z/OS database?

Which of the following DB2 data types does NOT have a fixed length?

If the following statement is executed:

CREATE TABLE employee
(empid INT NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 1, INCREMENT BY 5),
name VARCHAR(20),
dept INT CHECK (dept BETWEEN 1 AND 20),
hiredate DATE WITH DEFAULT CURRENT DATE,
salary DECIMAL(7,2),
PRIMARY KEY(empid),
CONSTRAINT cst1 CHECK (YEAR(hiredate) > 2006 OR
Salary > 60500));

Which of the following INSERT statements will fail?

After the following SQL statement is executed: GRANT ALL PRIVILEGES ON TABLE employee TO USER user1 Assuming user USER1 has no other authorities or privileges, which of the following actions is user USER1 allowed to perform?

Which two of the following allow you to perform administrative tasks against database objects?

Which of the following resources can be explicitly locked?

How should the following UDF be invoked in order to convert US currency values stored in the EXPENSES table into Canadian currency?

CREATE FUNCTION getratews11 (

country1 VARCHAR(100),
country2 VARCHAR(100) )
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
EXTERNAL ACTION NOT DETERMINISTIC
BEGIN
...
END

Which of the following is a typical data warehouse query?

Which of the following is the best statement to use to create a user-defined data type that can be used to store currency values?

Application A holds a lock on a row in table TAB1.
If lock timeout is set to 20, what will happen when Application B attempts to acquire a compatible lock on the same row?

What does the following statement do?
GRANT REFERENCES (col1, col2) ON TABLE table1 TO user1 WITH GRANT OPTION

Given the following two tables:

TAB1
R1
--
A
A
A
B
B
C
C
D
E
TAB2
R2
--
A
A
B
B
C
C
D
Which of the following queries returns the following result set?

RETVAL
------E

Given the following DDL and INSERT statements:

CREATE VIEW v1 AS SELECT col1 FROM t1 WHERE col1 > 10; CREATE VIEW v2 AS SELECT col1 FROM
v1 WITH CASCADED CHECK OPTION; CREATE VIEW v3 AS SELECT col1 FROM v2 WHERE col1 < 100;
INSERT INTO v1 VALUES(5);
INSERT INTO v2 VALUES(5);
INSERT INTO v3 VALUES(20);
INSERT INTO v3 VALUES(100);

How many of these INSERT statements will be successful?

Which of the following statements allows BOB to revoke access to the SAMPLE database from user TOM?

If the following SQL statements are executed:

CREATE TABLE make (makeid SMALLINT NOT NULL PRIMARY KEY, make VARCHAR(25));

CREATE TABLE model (modelid SMALLINT,
model VARCHAR(25),
makeid SMALLINT,
CONSTRAINT const1 FOREIGN KEY (makeid)
REFERENCES make(makeid) ON DELETE RESTRICT);

And each table created is populated as follows:

MAKE

MAKEID MAKE
------ --------
1 Ford
2 Chevrolet
3 Toyota

MODEL

MODELID MODEL MAKEID
------- ------- --------
1 Mustang 1
2 Escort 1
3 Malibu 2
4 Camry 3

If the following SQL statement is executed:

DELETE FROM make WHERE makeid = 1

What is the total number of rows that will be deleted?

Given the following table:

EMPLOYEE

EMPID NAME INSTRUMENT
--- --------------- -----
1 Jagger, Mick 01
2 Richards, Keith 02
3 Wood, Ronnie 02
4 Watts, Charlie 03
5 Jones, Darryl 04
6 Leavell, Chuck 05

If the following query is executed:

SELECT name,
CASE WHEN instrument = '01' THEN 'HARMONICA'
WHEN instrument = '02' THEN 'GUITAR'
WHEN instrument = '03' THEN 'DRUMS'
ELSE 'UNKNOWN'
END AS instrument

FROM employee

What will be the results?

Which of the following will be a consequence of defining the column IDCOL2 in TABLE2 as a foreign key referencing the primary key (IDCOL1) of TABLE1?

Which of the following is a feature of a unit of work?

Which of the following is NOT a characteristic of a unique index?

Given the following table definition:

STOCK:

item VARCHAR(30)
status CHAR(1)
quantity INT
price DEC(7,2)

If items are indicated to be out of stock by setting STATUS to NULL and QUANTITY and PRICE to zero, which of the following statements would be used to update the STOCK table to indicate that all the items whose description begins with the letter "S" are out of stock?

Which of the following is used to create and debug user-defined functions?

Given the following UPDATE statement: UPDATE employees SET workdept =
(SELECT deptno FROM department WHERE deptno = 'A01') WHERE workdept IS NULL

Which of the following describes the result if this statement is executed?

An application needs to store a 5 MB JPEG image in a DB2 table.

Which data type should be specified for the column that will be used for storing the image?

Which of the following will begin a new unit of work?

Which of the following statements is used to revoke all DML privileges on table EMPLOYEE from user TOM?

If table TAB1 is created using the following statement:

CREATE TABLE tab1 (col1 INTEGER NOT NULL,
col2 CHAR(5),
CONSTRAINT cst1 CHECK (col1 in (1, 2, 3)))

Which of the following statements will successfully insert a record into table TAB1?

Given the following statement:

CREATE TABLE tab1
(col1 SMALLINT NOT NULL PRIMARY KEY,
col2 VARCHAR(200) NOT NULL WITH DEFAULT NONE,
col3 DECIMAL(5,2) CHECK (col3 >= 100.00),
col4 DATE NOT NULL WITH DEFAULT)

Which of the following definitions will cause the CREATE TABLE statement to fail?

Which of the following is an accurate statement about packages?

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

Which of the following DB2 objects is NOT considered executable using SQL?

Which of the following statements is used to prevent user TOM from adding and deleting data in table TAB1?

Which of the following actions will NOT cause a trigger to be fired?

An index named EMPID_X exists for a table named EMPLOYEE.

Which of the following will allow user USER1 to drop the EMPID_X index?

Which of the following DB2 products are required on an iSeries or System I server to enable an application running on that server to retrieve data from a DB2 database on a Linux server?

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?

Given the following table:

CURRENT_EMPLOYEES
--------------------------------------
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)
PAST_EMPLOYEES
--------------------------------------
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)

Assuming both tables contain data, which of the following statements will NOT successfully add data to table CURRENT_EMPLOYEES?

What does the following statement do?

Given the following table:

TAB1

COL1 COL2
----- -----
A 10
B 20
C 30
A 10
D 40
C 30

Assuming the following results are desired:

TAB1

COL1 COL2
----- -----
A 10
B 20
C 30
D 40

Which of the following statements will produce the desired results?