IBM Assessment: DB2 9 Family Fundamentals - A2090-730 FREE EXAM DUMPS QUESTIONS & ANSWERS]

Question 1

Which of the following best describes the age of the data in an OLTP system?

A. Historical B. Current and projected C. Projected D. Current

Discussion 0

Correct Answer: D Vote an answer

Question 2

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?

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

Discussion 0

Correct Answer: D Vote an answer

Question 3

Given the following scenario:
Table TABLE1 needs to hold specific numeric values up to 9999999.999 in column COL1. Once TABLE1 is populated, arithmetic operations will be performed on data stored in column COL1.
Which of the following would be the most appropriate DB2 data type to use for column COL1?

A. REAL B. DECIMAL(10, 3) C. INTEGER D. NUMERIC(7, 3)

Discussion 0

Correct Answer: B Vote an answer

Question 4

Given the following two tables:
NAMES
NAME NUMBER
Wayne Gretzky 99 Jaromir Jagr 68 Bobby Orr 4 Bobby Hull 23 Brett Hull 16 Mario Lemieux 66 Mark Messier 11
POINTS
NAME POINTS
Wayne Gretzky 244 Jaromir Jagr 168 Bobby Orr 129 Brett Hull 121 Mario Lemieux 189 Joe Sakic 94
Which of the following statements will display the player name, number, and points for all players that have scored points?

A. SELECT p.name,n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name B. SELECT p.name,n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name C. SELECT p.name,n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name D. SELECT p.name,n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name

Discussion 0

Correct Answer: A Vote an answer

Question 5

What does the following statement do? GRANT ALTER ON SEQUENCE gen_empid TO user1 WITH GRANT OPTION

A. Gives USER1 the ability to change the comment associated with a sequence named GEN_EMPID, along with the ability to give this authority to other users and groups. B. Gives USER1 the ability to change the comment associated with a sequence named GEN_EMPID, along with the ability to give this CONTROL authority for the sequence to other users and groups. C. Gives USER1 the ability to change the values returned by the PREVIOUS_VALUE and NEXT_VALUE expressions associated with a sequence named GEN_EMPID, along with the ability to give CONTROL authority for the sequence to other users and groups. D. Gives USER1 the ability to change the values returned by the PREVIOUS_VALUE and NEXT_VALUE expressions associated with a sequence named GEN_EMPID, along with the ability to give this authority to other users and groups.

Discussion 0

Correct Answer: A Vote an answer

Question 6

Given the following query:
SELECT quantity,
CASE WHEN itemcode = '099' THEN 'SILVER'
WHEN itemcode = '788' THEN 'GOLD'
WHEN itemcode = '899' THEN 'PLATINUM'
ELSE 'ERROR'
END
FROM supplier
What will be the result of the query if the following data is evaluated by the CASE expression?
SUPPLIER
QUANTITY ITEMCODE 3 099 4 099 1 788 1 899 5 009 3 788 1 899

A. 3,SILVER 4,SILVER 1,PLATINUM 1,ERROR 5,SILVER 3,GOLD 1,PLATINUM B. 3,SILVER 4,SILVER 1,GOLD 1,GOLD 5,SILVER 3,ERROR 1,PLATINUM C. 3,SILVER 4,SILVER 1,GOLD 1,PLATINUM 5,ERROR 3,GOLD 1,PLATINUM D. 3,SILVER 4,GOLD 1,SILVER 1,GOLD 5,PLATINUM 3,SILVER 1,GOLD

Discussion 0

Correct Answer: C Vote an answer

Question 7

User USER1 has the privileges needed to invoke a stored procedure named GEN_RESUME. User USER2 needs to be able to call the procedure - user USER1 and all members of the group PUBLIC should no longer be allowed to call the procedure. Which of the following statement(s) can be used to accomplish this?

A. GRANT CALL ON ROUTINEgen_resume TO user2 EXCLUDE user1 PUBLIC B. GRANT CALL ON PROCEDUREgen_resume TO user2;
REVOKE CALL ON PROCEDURE gen_resume FROM user1, PUBLIC; C. GRANT EXECUTE ON ROUTINEgen_resume TO user2 EXCLUDE user1, PUBLIC D. GRANT EXECUTE ON PROCEDUREgen_resume TO user2;
REVOKE EXECUTE ON PROCEDURE gen_resume FROM user1, PUBLIC;

Discussion 0

Correct Answer: D Vote an answer

Question 8

Which of the following strings can NOT be inserted into an XML column using XMLPARSE()?

A. "<employee />" B. "<name>John Doe</name>" C. "<p></p>" D. "<?xml version='1.0' encoding='UTF-8' ?>"

Discussion 0

Correct Answer: D Vote an answer

Question 9

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?

A. UPDATE stock SET status = NULL, SET quantity = 0, SET price = 0 WHERE item LIKE 'S%' B. UPDATE stock SET (status = NULL; quantity, price = 0) WHERE item LIKE S% C. UPDATE stock SET (status, quantity,price) = (NULL, 0, 0) WHERE item LIKE S% D. UPDATE stock SET (status = NULL), (quantity = 0), (price = 0) WHERE item LIKE S%

Discussion 0

Correct Answer: C Vote an answer

Question 10

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

A. A unique index can only be created for a non-nullable column B. A unique index cannot be created for a populated table if the key column specified contains more than one NULL value C. Each column in a base table can only participate in one uniqueindex, regardless of how the columns are grouped (the same column cannot be used in multiple unique indexes) D. In order for an index to be used to support a unique constraint, it must have been defined with the UNIQUE attribute

Discussion 0

Correct Answer: A Vote an answer

Question 11

Given the following information:
Protocol: TCP/IP Port Number: 5000 Host Name: DB_SERVER Database Name: TEST_DB Database Server Platform: Linux
Which of the following will allow a client to access the database stored on the server?

A. CATALOG TCPIP NODEdb_server REMOTE db_server PORT 5000 OSTYPE LINUX;
CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER; B. CATALOG DATABASEtest_db AS test_db REMOTE TCPIP SERVER db_server PORT 5000
OSTYPE LINUX; C. CATALOG TCPIP NODE 5000 REMOTE SERVERdb_server OSTYPE LINUX;
CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER; D. CATALOG TCPIP NODEdb_server REMOTE db_server SERVER 5000 OSTYPE LINUX;
CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

Discussion 0

Correct Answer: D Vote an answer

Question 12

Which type of key is defined on the child table to implement a referential constraint?

A. Primary key B. Foreign key C. Composite key D. Unique key

Discussion 0

Correct Answer: A Vote an answer