Oracle 1Z0-071 Quiz 43 Topic 11 Questions 211-215

Question: 1

View the Exhibit and examine the structure of the CUSTOMERS table.

q1_1Z0-071

Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.

Customers with no credit limit should have ''Not Available'' displayed.

Which SQL statement would produce the required result?

ASELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') ''NEW CREDIT''FROM customers;

BSELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) ''NEW CREDIT''FROM customers;

CSELECT NVL(cust_credit_limit*.15,'Not Available') ''NEW CREDIT''FROM customers;

DSELECT NVL(cust_credit_limit,'Not Available')*.15 ''NEW CREDIT''FROM customers;

Show Answer

Question: 2

Examine these SQL statements that are executed in the given order:

q2_1Z0-071

What will be the status of the foreign key EMP_MGR_FK?

AIt will be enabled and immediate.

BIt will be enabled and deferred.

CIt will remain disabled and can be re-enabled manually.

DIt will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

Show Answer

Question: 3

View the Exhibit and examine the structure in the EMPLOYEES tables.

q3_1Z0-071

Evaluate the following SQL statement:

SELECT employee_id, department_id

FROM employees

WHERE department_id= 50 ORDER BY department_id

UNION

SELECT employee_id, department_id

FROM employees

WHERE department_id= 90

UNION

SELECT employee_id, department_id

FROM employees

WHERE department_id= 10;

What would be the outcome of the above SQL statement?

AThe statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.

BThe statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.

CThe statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.

DThe statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

Show Answer

Question: 4

View the Exhibit and examine the description for the SALES and CHANNELS tables.

q4_1Z0-071

You issued this SQL statement:

q4_1Z0-071

Which statement is true regarding the result? (Choose the best answer.)

AThe statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.

BThe statement will fail because a subquery cannot be used in a VALUES clause.

CThe statement will execute and a new row will be inserted in the SALES table.

DThe statement will fail because the VALUES clause is not required with the subquery.

Show Answer

Question: 5

View the Exhibit and examine the description of the ORDERS table.

q5_1Z0-071

Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

AWHERE order_date_IN ( TO_DATE('OCT 21 2003','MON DD YYYY'), TO_CHAR('NOV 21 2003','MON DD YYYY') )

BWHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')

CWHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'

DWHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')

Show Answer

Oracle 1Z0-071 Quiz:43 Topic:11 Questions:211-215