Question No: 151

Which two statements are true regarding operators used with subqueries? (Choose two.

The NOT IN operator is equivalent to IS NULL

The lt;ANY operator means less than the maximum.

C. =ANY and =ALL operators have the same functionality.

The IN operator cannot be used in single-row subqueries.

The NOT operator can be used with IN, ANY and ALL operators.

Answer: B,E

Question No: 152

Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply.)

It is possible to retrieve data only in top-down hierarchy.

It is possible to retrieve data in top-down or bottom-up hierarchy.

It is possible to remove an entire branch from the output of the hierarchical query.

You cannot specify conditions when you retrieve data by using a hierarchical query.

Answer: B,C

Question No: 153

Evaluate the following ALTER TABLE statement:

ALTER TABLE orders SET UNUSED order_date;

Which statement is true?

The DESCRIBE command would still display the ORDER_DATE column.

ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.

The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.

After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.

Answer: D

Question No: 154

Which statement is true regarding synonyms?

Synonyms can be created for tables but not views.

Synonyms are used to reference only those tables that are owned by another user.

A public synonym and a private synonym can exist with the same name for the same table.

The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid.

Answer: C

Question No: 155

Evaluate the following statement:

CREATE TABLE bonuses(employee_id NUMBER, bonus NUMBER DEFAULT 100);

The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table. The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who have made sales get a bonus of 1 % of their salary and also a salary increase of 1 %. The salary of each employee can be obtained from the EMPLOYEES table.

Which option should be used to perform this task most efficiently?

MERGE

Unconditional INSERT

Conditional ALL INSERT

Conditional FIRST INSERT

Answer: A

Question No: 156

Evaluate the SQL statements:

CREATE TABLE new_order (orderno NUMBER(4),

booking_date TIMESTAMP WITH LOCAL TIME ZONE);

The database is located in San Francisco where the time zone is -8:00. The user is located in New York where the time zone is -5:00.

A New York user inserts the following record:

INSERT INTO new_order

VALUES(1, TIMESTAMP 007-05-10 6:00:00 -5:00quot;);

Which statement is true?

When the New York user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000#39;

When the New York user selects the row, booking_date is displayed as 2007-05-10 6.00.00.000000 -5:00#39;.

When the San Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000#39;

When the San Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000 -8:00#39;

Answer: C

Question No: 157

Which three statements indicate the end of a transaction? (Choose three.)

after a COMMIT is issued

after a ROLLBACK is issued

after a SAVEPOINT is issued

after a SELECT statement is issued

after a CREATE statement is issued

Answer: A,B,E

Question No: 158

View the Exhibit and examine the description of the EMPLOYEES and DEPARTMENTS tables.

You want to display the LAST_NAME for the employees, LAST_NAME for the manager of the employees, and the DEPARTMENT_NAME for the employees having 100 as MANAGER_ID. The following SQL statement was written:

The statement would not execute because the ON clause is written twice.

The statement would not execute because the WHERE clause is wrongly placed.

The statement would not execute because the self join uses the ON clause instead of the USING clause.

Answer: C

Question No: 159

Given below is a list of datetime data types and examples of values stored in them in a random order:

Datatype Example

INTERVAL YEAR TO MONTH a) 2003-04-15 8:00:00 -8:00#39;

TIMESTAMP WITH LOCAL TIME ZONE b) #39;-K)6 03:30:16.000000#39;

TIMESTAMP WITH TIME ZONE c) #39;17-JUN-03 12.00.00.000000 AM#39;4)INTERVAL DAY TO SECOND d) #39;402-00#39;

Identify the option that correctly matches the data types with the values.

A. 1-d.2-c.3-a.4-b

B. 1-b.2-a.3-c.4-d

C. 1-b.2-a,3-d,4-c

D. 1-d.2-c.3-b.4-a

Answer: A

Question No: 160

Evaluate the following statement:

CREATE TABLE bonuses(employee_id NUMBER, bonus NUMBER DEFAULT 100);

The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table.

The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who have made sales get a bonus of 1 % of their salary and also a salary increase of 1 %. The salary of each employee can be obtained from the EMPLOYEES table.