Q1. Which statement is true regarding external tables?
A. The default REJECT LIMIT for external tables is UNLIMITED.
B. The data and metadata for an external table are stored outside the database.
C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.
Answer: D
Q2. Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)
A. The outer query stops evaluating the result set of the inner query when the first value is found.
B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.
Answer: AC
Q3. View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount"
FROM order_items oi JOIN orders o
ON oi.order_id = o.order_id
GROUP BY CUBE (o.customer_id, oi.product_id);
Which three statements are true regarding the output of this SQL statement? (Choose three.)
A. t wouldreturn thesubtotals for theOrder Amount of every CUSTOMER_ID.
B. twould returnthesubtotals fortheOrderAmountfor every PRODUCT_ID.
C. twould return the subtotals fortheOrder Amount of every PRODUCT_IDandCUSTOMER_ID asonegroup.
D. t would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
E. t wouldreturnonly thegrandtotal for theOrderAmount ofeveryCUSTOMER_ID and PRODUCT_ID as onegroup.
Answer: ABD
Q4. Which three possible values can be set for the TIME_ZONE session parameter by using the ALTER SESSION command? (Choose three.)
A. 'os'
B. local
C. -8:00'
D. dbtimezone Li
E. 'Australia'
Answer: BCD
Q5. View the Exhibit and examine the structure of the ORDERS table.
You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101
Which query would give you the desired output?
A. SELECT order id, order_date FROM orders
WHERE order_date > ALL (SELECT MAX(order_date)
FROM orders)AND
Customer_id = 101;
B. SELECT order id, order_date FROM orders
WHERE order_date > ANY (SELECT order_date
FROM orders
WHERE customer_id = 101);
C. SELECT order _id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101);
D. SELECT order id, order_date FROM orders WHERE order_date IN (SELECT order_date FROM orders WHERE customer id = 101);
Answer: C
Q6. Which statement is true regarding Flashback Version Query?
A. It returns versions of rows only within a transaction.
B. It can be used in subqueries contained only in a SELECT statement.
C. It will return an error if the undo retention time is less than the lower bound time or SCN specified.
D. It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows.
Answer: D
Q7. Which CREATE TABLE statement is valid?
A. CREATE TABLE ord_details
(ord_no NUMBER(2) PRIMARY KEY,
item_no NUMBER(3)PRIMARY KEY,
ord_date date NOT NULL);
B. CREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date date DEFAULT SYSDATE NOT NULL);
C. CREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_no NUMBER(3),
ord_date date DEFAULT NOT NULL,
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no));
D. CREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date date DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
Answer: D
Q8. The first DROP operation is performed on PRODUCTS table using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?
A. It recovers only thetablestructure.
B. It recovers thetablestructure,data,andtheindexes.
C. It recovers thetablestructure anddatabutnotthe related indexes.
D. It is not possible to recover the table structure, data, or the related indexes.
Answer: D
Q9. View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables.
Which is the best method to load data from the MARKS DETAILStable to the MARKStable?
A. Pivoting INSERT
B. Unconditional INSERT
C. Conditional ALL INSERT
D. Conditional FIRST INSERT
Answer: A
Q10. 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.manager_id=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.manager_id=100;
Answer: B