Oracle 1Z0-047 Exam : Oracle Database SQL Expert

View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT employee_id, last_name, job_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR employee_id=manager_id ;
Which two statements are true regarding the output of this command? (Choose two.)

• A. The LEVEL column displays the level in the hierarchy at which the employee is placed under the employee having the EMPLOYEE_ID 101
• B. The output would be in top-down hierarchy starting with EMPLOYEE_ID having value 101.
• C. The output would be in bottom-up hierarchy starting with EMPLOYEE_ID having value 101.
• D. The LEVEL column displays the number of employees in the hierarchy under the employee having the EMPLOYEE_ID 101.

Which statements are true? (Choose all that apply.)

• A. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary
• B. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
• C. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
• D. The usernames of all the users including the database administrators are stored in the data dictionary.
• E. The data dictionary views can consist of joins of dictionary base tables and user-defined tables.
• F. The data dictionary is created and maintained by the database administrator.

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. Conditional ALL INSERT
• B. Unconditional INSERT
• C. Conditional FIRST INSERT
• D. Pivoting INSERT

View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables.
Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them?
SELECT location_id, city
FROM locations
SELECT location_id, city FROM locations JOIN departments USING(location_id);

• A. MINUS
• B. INTERSECT
• C. UNION ALL
• D. UNION

Which statement is true regarding external tables?

• A. The data and metadata for an external table are stored outside the database.
• B. The default REJECT LIMIT for external tables is UNLIMITED.
• C. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.
• D. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.