C2090-730 Online Practice Questions and Answers

Which of the following are NOT stored in the system catalog tables?

A. SQL statements used to create views

B. SQL statements used to create triggers

C. SQL statements used to create constraints

D. Table names

Which of the following is NOT an attribute of Declared Global Temporary Tables (DGTTs)?

A. Each application that defines a DGTT has its own instance of the DGTT

B. Two different applications cannot create DGTTs that have the same name

C. DGTTs can only be used by the application that creates them, and only for the life of the application

D. Data stored in a DGTT can exist across transaction boundaries

Which of the following is an accurate statement about packages?

A. Packages provide a logical grouping of database objects.

B. Packages contain control structures that are considered the bound form for SQL statements

C. Packages describe the objects in a DB2 database and their relationship to each other

D. Packages may be used during query optimization to improve the performance for a subset of SELECT queries

Given the following information:

Protocol: TCP/IP Port Number: 5000 Host Name: DB_SERVER Database Name: TEST_DB Database Server Platform: Linux

A. CATALOG DATABASEtest_db AS test_db REMOTE TCPIP SERVER db_server PORT 5000 OSTYPE LINUX;

B. CATALOG TCPIP NODE 5000 REMOTE SERVERdb_server OSTYPE LINUX; CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

C. CATALOG TCPIP NODEdb_server REMOTE db_server SERVER 5000 OSTYPE LINUX; CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

D. CATALOG TCPIP NODEdb_server REMOTE db_server PORT 5000 OSTYPE LINUX; CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

Given the following expression:

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?

QUANTITY ITEMCODE

3 099

4 099

1 788

1 899

5 009

3 788

1 899

A. 3, SILVER 4, GOLD 1, SILVER 1, GOLD 5, PLATINUM 3, SILVER 1, GOLD

B. 3, SILVER 4, SILVER 1, GOLD 1, PLATINUM 5, ERROR 3, GOLD 1, PLATINUM

C. C. C. 3, SILVER 4, SILVER 1, PLATINUM 1, ERROR 5, SILVER 3, GOLD 1, PLATINUM

D.

E.

F. 3, SILVER 4, SILVER 1, GOLD 1, GOLD 5, SILVER 1, PLATINUM

Which two of the following statements are true about the HAVING clause?

A. The HAVING clause is used in place of the WHERE clause.

B. The HAVING clause uses the same syntax as the WHERE clause.

C. The HAVING clause can only be used with the GROUP BY clause.

D. The HAVING clause accepts wildcards.

E. The HAVING clause uses the same syntax as the IN clause.

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?

A. INSERT INTO tab1 VALUES (0, 'abc')

B. INSERT INTO tab1 VALUES (NULL, 'abc')

C. INSERT INTO tab1 VALUES (ABS(2), 'abc')

D. INSERT INTO tab1 VALUES (DEFAULT, 'abc')

A DBA has been asked to create a table which will contain a substantial amount of detailed sales information for each calendar month and maintain it to contain only the last 12 months. Which of the following methods will facilitate the online removal of the oldest month's data?

A. Create an MQT that selects the oldest month of data with the REFRESH IMMEDIATE option.

B. Create 12 separate tables, create a view based on all 12, drop the table with the oldest month's data then drop and re-create the view.

C. Create a range partitioned table, partitioned by month, and use the ALTER TABLE statement to detach the oldest month and attach storage for new data.

D. Create a single table, extract the data to be retained using UNLOAD with a SELECT statement, drop and re-create the table then load only the data to be retained.

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

A. ""

B. ""

C. ""

D. ""

Which of the following is NOT true about schemas?

A. If a schema name is not specified, either by qualifying a database object name or by executing the SET CURRENT SCHEMA statement, the authorization ID of the current session user is used as the schema name by default

B. The value assigned to the CURRENT SCHEMA special register is persistent across database restarts

C. A schema enables the creation of multiple objects in a database without encountering namespace collisions

D. When most database objects are created, they are either implicitly or explicitly assigned to a schema