View the Exhibit and examine the description of the ORDERS table. (Choose two.)

Which two WHERE clause conditions demonstrate the correct usage of conversion
functions?

A.

WHERE Order_date_IN ( TO_DATE('OCT 21 2003', 'MON DD YYYY'), TO_CHAR('NOV
21 2003', 'MON DD YYYY') )

B.

WHERE Order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')

C.

WHERE TO_CHAR(Order_date, 'MON DD YYYY') = 'JAN 20 2003'

D.

WHERE Order_date > ( TO_DATE('JUL 10 2006', 'MON DD YYYY')

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose
three.)

A.

SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL

B.

SELECT TO_CHAR (1890.55, '$9,999V99') FROM DUAL;

C.

SELECT TO_CHAR (1890.55, '$0G000D00') FROM DUAL;

D.

SELECT TO_CHAR (1890.55, '$99,999D99') FROM DUAL;

E.

SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL

View the exhibit and examine the structure of the PROMOTIONS table

You have to generate a report that displays the promo name and start date for all promos
that started after the last promo in the ‘INTERNET’ category.
Which query would give you the required output?

A.

SELECT promo_name, promo_begin_date FROM promotionsWHERE
promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions)
ANDpromo_category= ‘INTERNET’;

B.

SELECT promo_name, promo_begin_date FROM promotionsWHERE
promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE
promo_category= ‘INTERNET’);

C.

SELECT promo_name, promo_begin_date FROM promotionsWHERE
promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE
promo_category = ‘INTERNET’);

D.

SELECT promo_name, promo_begin_date FROM promotionsWHERE
promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE
promo_category= ‘INTERNET’);

Examine the structure of the EMPLOYEES table. (Choose two.)

You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output?

A.

SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date
< SYSDATE-365GROUP BY MIN(salary), MAX(salary);

B.

SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employeesWHERE hire_date < SYSDATE-365)GROUP BY maxsal, minsal;

C.

SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary),
MAX(salary);

D.

SELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROM employeesWHERE
hire_date < SYSDATE-365);

Which three tasks can be performed using SQL functions built into Oracle Database?

A.

displaying a date in a nondefault format

B.

finding the number of characters in an expression

C.

substituting a character string in a text expression with a specified string

D.

combining more than two columns or expressions into a single column in the output

Examine the command:
SQL> ALTER TABLE books_transactions
ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id)
REFERENCES books (book_id) ON DELETE CASCADE;
What does ON DELETE CASCADE imply?

A.

When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.

B.

When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table
are deleted but the table structure is retained.

C.

When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS
table whose BOOK_ID matches that of the deleted row in the BOOKS table are also
deleted.

D.

When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is
updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

View the Exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID.
You want the output in two columns: the first column would have the LAST_NAME of the
managers and the second column would have LAST_NAME of the employees

Which SQL statement would you execute?

A.

SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE m.manager_id=100;

B.

SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.managerjd=100;

C.

SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id
WHERE m.manager_id=100;

D.

SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id AND e.managerjd=100;

Evaluate the following statement.
INSERT ALL
WHEN order_total < 10000 THEN
INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN
INTO medium_orders
WHEN order_total > 200000 THEN
INTO large_orders
SELECT order_id, order_total, customer_id
FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the
INSERT statement?

A.

Each row is evaluated by the first WHEN clause and if the condition is false then the row
would be evaluated by the subsequent when clauses.

B.

All rows are evaluated by all the three WHEN clauses.

C.

Each row is evaluated by the first WHEN clause and if the condition is true, then the row
would be evaluated by the subsequent when clauses.

D.

The INSERT statement will return an error because the ELSE clause is missing.

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

Which two tasks would require subqueries or joins to be executed in a single statement?

A.

finding the number of customers, in each city, whose credit limit is more than the
average credit limit of all the customers

B.

finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'

C.

listing of customers who do not have a credit limit and were born before 1980

D.

 finding the number of customers, in each city, who’s marital status is 'married'.

E.

listing of those customers, whose credit limit is the same as the credit limit of customers
residing in the city 'Tokyo'.

You notice a performance change in your production Oracle 12c database. You want to
know which change caused this performance difference.
Which method or feature should you use?

A.

Compare Period ADDM report.

B.

AWR Compare Period report.

C.

Active Session History (ASH) report.

D.

Taking a new snapshot and comparing it with a preserved snapshot.

Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY
KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY
KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id
NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES
stores(store_id));
You executed the following statement:
SQL> DELETE from stores
WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?

A.

Disable the primary key in the STORES table.

B.

Use CASCADE keyword with DELETE statement.

C.

DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows
from STORES table.

D.

Disable the FOREIGN KEY in SALES table and then delete the rows.

E.

Create the foreign key in the SALES table on SALES_ID column with on DELETE
CASCADE option.

You want to display 5 percent of the rows from the SALES table for products with the
lowest AMOUNT_SOLD and also want to include the rows that have the same
AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.
Which query will provide the required result?

A.

SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH
FIRST 5 PERCENT ROWS WITH TIES;

B.

SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH
FIRST 5 PERCENT ROWS ONLY WITH TIES;

C.

SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH
FIRST 5 PERCENT ROWS WITH TIES ONLY;

D.

SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH
FIRST 5 PERCENT ROWS ONLY;