Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 1:

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

  

  You want to display the expiration date of the warranty for a product. Which SQL statement would you execute?

  A. SELECT product_id, SYSDATE + warranty_period FROM product_information;
  B. SELECT product_jd, TO_YMINTERVAL(warranty_period) FROM product_information;
  C. SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information;
  D. SELECT product_jd, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information;
  A. SELECT product_id, SYSDATE + warranty_period FROM product_information;

• Question 2:

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

  You need to display the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.

  Which query would produce the desired output?

  ORDER ITEMS

  

  A. SELECT order_id FROM order_itemsWHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id;
  B. SELECT order_id FROM order_itemsWHERE(unit_price*quantity) = (SELECT MAX (unit_price*quantity) FROM order_items) GROUP BY order_id;
  C. SELECT order_id FROM order_itemsWHERE (unit_price*quantity) = (SELECT MAX (unit_price*quantity) FROM order_items GROUP BY order_id);
  D. SELECT order_id FROM order_items GROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)j FROM order_items GROUP BY order_id);
  D. SELECT order_id FROM order_items GROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)j FROM order_items GROUP BY order_id);

• Question 3:

  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 his or her peers.
  B. 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.
  C. 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.
  D. 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.
  C. 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.

• Question 4:

  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.
  D. It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows.

• Question 5:

  Which two statements are true regarding constraints? (Choose two.)

  A. A foreign key cannot contain NULL values.
  B. A column with the UNIQUE constraint can contain NULL.
  C. A constraint is enforced only for the INSERT operation on a table.
  D. A constraint can be disabled even if the constraint column contains data.
  E. All the constraints can be defined at the column level as well as the table level
  B. A column with the UNIQUE constraint can contain NULL.
  D. A constraint can be disabled even if the constraint column contains data.

• Question 6:

  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.
  A. The outer query stops evaluating the result set of the inner query when the first value is found.
  C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.

• Question 7:

  View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and ORDER_TOTAL have the default values 'direct' and 0 respectively.

  

  Which two INSERT statements are valid? (Choose two.)

  A. INSERT INTO ordersVALUES (1, O9-mar-2007', 'online',",1000);
  B. INSERT INTO orders(order_id ,order_date ,order_mode,customer_id ,order_total)VALUES(1 ,TO_DATE(NULL), 'online', 101, NULL);
  C. INSERT INTO(SELECT order_id ,order_date .customer_idFROM orders)VALUES (1,O9-mar- 2007', 101);
  D. INSERT INTO ordersVALUES (1,09-mar-2007', DEFAULT, 101, DEFAULT);
  E. INSERT INTO orders(order_id ,order_date ,order_mode .order_total) VALUES (1 ,'10-mar- 2007','online',1000);
  C. INSERT INTO(SELECT order_id ,order_date .customer_idFROM orders)VALUES (1,O9-mar- 2007', 101);
  D. INSERT INTO ordersVALUES (1,09-mar-2007', DEFAULT, 101, DEFAULT);

• Question 8:

  Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)

  A. The nested query executes after the outer query returns the row.
  B. The nested query executes first and then the outer query executes.
  C. The outer query executes only once for the result returned by the inner query.
  D. Each row returned by the outer query is evaluated for the results returned by the inner query.
  A. The nested query executes after the outer query returns the row.
  D. Each row returned by the outer query is evaluated for the results returned by the inner query.

• Question 9:

  Which statement is true regarding synonyms?

  A. Synonyms can be created for tables but not views.
  B. Synonyms are used to reference only those tables that are owned by another user.
  C. A public synonym and a private synonym can exist with the same name for the same table.
  D. The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid.
  C. A public synonym and a private synonym can exist with the same name for the same table.

• Question 10:

  Evaluate the following SQL statement:

  ALTER TABLE hr.emp SET UNUSED (mgr_id);

  Which statement is true regarding the effect of the above SQL statement?

  A. Any synonym existing on the EMP table would have to be re-created.
  B. Any constraints defined on the MGR_ID column would be removed by the above command.
  C. Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created.
  D. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed.
  B. Any constraints defined on the MGR_ID column would be removed by the above command.