IBM C2090-610 Online Practice Questions and Exam Preparation

IBM C2090-610 Online Questions & Answers

• Question 1:

  Given an EMPLOYEES table and a SALES table, a user wants to produce a list of all employees and their associated revenue, even if no revenue exists. Which SQL statement will produce the desired list?

  A. SELECT employees.name, sales.revenue FROM employees INNER JOIN sales ON employees.id = sales.emp_id
  B. SELECT employees.name, sales.revenue FROM employees INNER JOIN sales ON sales.emp_id = employees.id
  C. SELECT employees.name, sales.revenue FROM sales LEFT OUTER JOIN employees ON employees.id = sales.emp_id
  D. SELECT employees.name, sales.revenue FROM sales RIGHT OUTER JOIN employees ON employees.id = sales.emp_id
  D. SELECT employees.name, sales.revenue FROM sales RIGHT OUTER JOIN employees ON employees.id = sales.emp_id

• Question 2:

  In a trusted context environment, if a switch request is made with an authorization ID that is not allowed on a trusted connection, what state is the connection placed in?

  A. Locked
  B. Waiting
  C. Pending
  D. Unconnected
  D. Unconnected

• Question 3:

  Which two operations are allowed in the body of an SQL scalar user-defined function? (Choose two.)

  A. CALL statements.
  B. External file access.
  C. Use of a scratch pad.
  D. COMMIT statements.
  E. SQL control statements.
  A. CALL statements.
  E. SQL control statements.

• Question 4:

  If a table named MY_TAB contains 100 rows and the following statement is executed:

  

  What will happen?

  A. The last 5 rows in the table will be deleted.
  B. The first 5 rows in the table will be deleted.
  C. The statement will fail because a subquery cannot be used with a DELETE statement.
  D. The statement will fail because a table name was not specified with the DELETE statement.
  A. The last 5 rows in the table will be deleted.

• Question 5:

  Which is NOT a valid reason for creating an index?

  A. To allow queries to run more efficiently.
  B. To enforce constraints such as uniqueness on index keys.
  C. To order the columns of a table in ascending or descending sequence according to values in a row.
  D. To order the rows of a table in ascending or descending sequence according to the values in a column.
  C. To order the columns of a table in ascending or descending sequence according to values in a row.

• Question 6:

  An SQL function designed to convert temperatures from Fahrenheit to Celsius was created as follows:

  

  How can this function be used to convert average temperature (AVG_TEMP) data stored in a table called CLIMATE_INFO?

  A. CALL conv_temp(climate_info.avg_temp);
  B. CALL convert_ftoc(climate_info.avg_temp);
  C. SELECT conv_temp(avg_temp) FROM climate_info;
  D. SELECT convert_ftoc(avg_temp) FROM climate_info;
  C. SELECT conv_temp(avg_temp) FROM climate_info;

• Question 7:

  Which command is used to back out a subset of database changes that have been made within a unit of work?

  A. COMMIT
  B. ROLLBACK
  C. COMMIT TO SAVEPOINT
  D. ROLLBACK TO SAVEPOINT
  D. ROLLBACK TO SAVEPOINT

• Question 8:

  Which command will delete all rows from a table without generating log records?

  A. TRIM
  B. DROP
  C. DELETE
  D. TRUNCATE
  D. TRUNCATE

• Question 9:

  What is the purpose of the Query Tuner?

  A. To automatically capture and stop rogue queries.
  B. To provide recommendations and analysis for tuning a single query.
  C. To provide recommendations and analysis for tuning up to 100 queries.
  D. To recommend indexes and to guide DBAs through the process of creating new indexes.
  B. To provide recommendations and analysis for tuning a single query.

• Question 10:

  When a COMMIT statement is executed, what happens?

  A. All locks held on the database are automatically released.
  B. Data stored in global temporary tables is automatically deleted.
  C. Open cursors defined WITH HOLD are closed, but their data is retained.
  D. The current transaction is terminated and a new transaction boundary is started.
  D. The current transaction is terminated and a new transaction boundary is started.