Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 81:

  View the Exhibit and examine the details of the EMPLOYEES table.

  You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100.

  

  Which SQL clauses would you require to accomplish the task? (Choose all that apply.)

  A. WHERE
  B. HAVING
  C. GROUP BY
  D. START WITH
  E. CONNECT BY
  A. WHERE
  D. START WITH
  E. CONNECT BY

• Question 82:

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

  CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table.

  CUSTOMERS needs to be updated to reflect the latest information about the customers. What is the error in the following MERGE statement?

  MERGE INTO customers c USING customer_vu cv ON (c.customer_id = cv.customer_id) WHEN MATCHED THEN UPDATE SET

  A. customer_id = cv.customer_id,
  B. cust_name = cv.cust_name,
  C. cust_email = cv.cust_email,
  D. income_level = cv. Income_level WHEN NOT MATCHED THEN INSERT VALUESfcv.customer_id.cv.cus_name.cv.cus_email.cv.income_level) WHERE cv. Income_level >100000;
  E. The CUSTOMER_ID column cannot be updated.
  F. The INTO clause is misplaced in the command.
  G. The WHERE clause cannot be used with INSERT.
  H. CUSTOMER VU cannot be used as a data source.
  A. customer_id = cv.customer_id,

• Question 83:

  Evaluate the following SQL statements in the given order:

  DROP TABLE dept;

  CREATE TABLE dept

  (deptno NUMBER(3) PRIMARY KEY,

  deptname VARCHAR2(10));

  DROP TABLE dept;

  FLASHBACK TABLE dept TO BEFORE DROP;

  Which statement is true regarding the above FLASHBACK operation?

  A. It recovers only the first DEPT table.
  B. It recovers only the second DEPT table.
  C. It does not recover any of the tables because FLASHBACK is not possible in this case.
  D. It recovers both the tables but the names would be changed to the ones assigned in the RECYCLEBIN.
  B. It recovers only the second DEPT table.

• Question 84:

  View the Exhibit and examine the details of the EMPLOYEES table.

  Evaluate the following SQL statements:

  Statement 1:

  SELECT employee_id, last_name, job_id, manager_id FROM employees

  START WITH employee_id = 101 CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108 ; Statement 2: SELECT employee_id, last_name, job_id, manager_id FROM employees WHERE manager_id != 108 START WITH employee_id = 101 CONNECT BY PRIOR employee_id = manager_id;

  Which two statements are true regarding the above SQL statements? (Choose two.)

  A. Statement 2 would not execute because the WHERE clause condition is not allowed in a statement that has the START WITH clause.
  B. The output for statement 1 would display the employee with MANAGER_ID 108 and all the employees below him or her in the hierarchy.
  C. The output of statement 1 would neither display the employee with MANAGER_ID 108 nor any employee below him or her in the hierarchy.
  D. The output for statement 2 would not display the employee with MANAGER_ID 108 but it would display all the employees below him or her in the hierarchy.
  C. The output of statement 1 would neither display the employee with MANAGER_ID 108 nor any employee below him or her in the hierarchy.
  D. The output for statement 2 would not display the employee with MANAGER_ID 108 but it would display all the employees below him or her in the hierarchy.

• Question 85:

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

• Question 86:

  Evaluate the following SQL statements in the given order:

  DROP TABLE dept;

  CREATE TABLE dept

  (deptno NUMBER(3) PRIMARY KEY,

  deptname VARCHAR2(10));

  DROP TABLE dept;

  FLASHBACK TABLE dept TO BEFORE DROP;

  Which statement is true regarding the above FLASHBACK operation?

  A. It recovers only the first DEPT table.
  B. It recovers only the second DEPT table.
  C. It does not recover any of the tables because FLASHBACK is not possible in this case.
  D. It recovers both the tables but the names would be changed to the ones assigned in the RECYCLEBIN.
  B. It recovers only the second DEPT table.

• Question 87:

  View the Exhibit and examine the data in the CUST_DET table.

  

  You executed the following multitable INSERT statement:

  INSERT FIRST WHEN credit_limit >= 5000 THEN

  INTO cust_1 VALUES(cust_id, credit_limit, grade, gender) WHEN grade = THE INTO cust_2 VALUES(cust_id, credit_limit, grade, gender) WHEN gender = THE INTO cust_3 VALUES(cust_id, credit_limit, grade, gender) INTO cust_4 VALUES(cust_id, credit_limit, grade, gender) ELSE INTO cust_5 VALUES(cust_id, credit_limit, grade, gender) SELECT * FROM cust_det;

  The row will be inserted in________.

  A. CUST_1 table only because CREDIT_LIMIT condition is satisfied
  B. CUST_1 and CUST_2 tables because CREDIT_LIMIT and GRADE conditions are satisfied
  C. CUST_1 ,CUST_2 and CUST_5 tables because CREDIT_LIMIT and GRADE conditions are satisfied but GENDER condition is not satisfied
  D. CUST 1, CUST 2 and CUST 4 tables because CREDIT LIMIT and GRADE conditions are satisfied for CUST 1 and CUST 2, and CUST 4 has no condition on it
  A. CUST_1 table only because CREDIT_LIMIT condition is satisfied

• Question 88:

  Evaluate the SQL statements:

  CREATE TABLE new_order

  (orderno NUMBER(4),

  booking_date TIMESTAMP WITH LOCAL TIME ZONE);

  The database is located in San Francisco where the time zone is -8:00.

  The user is located in New York where the time zone is -5:00.

  A New York user inserts the following record:

  INSERT INTO new_order

  VALUES(1, TIMESTAMP ?007-05-10 6:00:00 -5:00?);

  Which statement is true?

  A. When the New York user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000'
  B. When the New York user selects the row, booking_date is displayed as 2007-05-10 6.00.00.000000 -5:00'.
  C. When the San Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000'
  D. When the San Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000 -8:00'
  C. When the San Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000'

• Question 89:

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

  Evaluate the following SQL statement:

  SELECTfirst_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees;

  The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees.

  The review date is the first Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session.

  Which statement is true regarding this query?

  

  A. The query would execute to give the desired output.
  B. The query would not execute because date functions cannot be nested.
  C. The query would execute but the output would give review dates that are Sundays.
  D. The query would not execute because the NEXT_DAY function accepts a string as argument.
  C. The query would execute but the output would give review dates that are Sundays.

• Question 90:

  Evaluate the following ALTER TABLE statement:

  ALTER TABLE orders SET UNUSED order_date; Which statement is true?

  A. The DESCRIBE command would still display the ORDER_DATE column.
  B. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
  C. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.
  D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
  D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.