Online Access Free 1Z1-047 Practice Test

Which mandatory clause has to be added to the following statement to successfully create an
external table called EMPDET?
CREATE TABLE empdet
(empno CHAR(2), ename CHAR(5), deptno NUMBER(4))
ORGANIZATION EXTERNAL
(LOCATION ('emp.daf));

• A.DEFAULT DIRECTORY
• B.ACCESS PARAMETERS
• C.TYPE
• D.REJECT LIMIT

The following are the steps for a correlated subquery, listed in random order:
1) The WHERE clause of the outer query is evaluated.
2) The candidate row is fetched from the table specified in the outer query.
3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed.
4) Rows are returned by the inner query, after being evaluated with the value from the candidate
row in the outer query. Identify the option that contains the steps in the correct sequence in which
the Oracle server evaluates a correlated subquery.

• A.4,1,2,3
• B.4,2,1,3
• C.2,1,4,3
• D.2,4,1,3

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
Evaluate the following UPDATE statement:
UPDATE (SELECT order_date, order_total, customer_id FROM orders) SET order_date = 22-mar-2007' WHERE customer_id = (SELECT customer_id FROM customers WHERE cust_last_name = 'Roberts'AND Credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?

• A.It would not execute because two tables cannot be used in a single UPDATE statement.
• B.It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
• C.It would not execute because the SELECT statement cannot be used in place of the table name.
• D.It would execute and restrict modifications to only the columns specified in the SELECT statement.

View the Exhibit and examine the structure of the EMPLOYEES table. Evaluate the following SQL statement: SELECT employee_id, last_name, job_id, manager_id
FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id=manager_id;
Which statement is true regarding the output for this command?

• A.It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by the employee to whom he or she reports.
• B.It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by employees below him or her in the hierarchy.
• C.It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is101, followed by employees up to one level below him or her in the hierarchy.
• D.It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by his or her peers.

View the Exhibit and examine the structure of the ORDERS table. Which task would require subqueries?

• A.displaying the number of orders that have order mode online and order date in 1999
• B.displaying the number of orders whose order total is more than the average order total for all online orders
• C.displaying the order total for sales representative 161 in the year 1999
• D.displaying the total order value for sales representatives 161 and 163