Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 71:

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

  

  You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:

  SELECT product_name, list_price FROM product_information WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;

  Which statement is true regarding the execution of the query?

  A. It would execute but the output would return no rows.
  B. It would execute and the output would display the desired result.
  C. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
  D. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.
  A. It would execute but the output would return no rows.

• Question 72:

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

  You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree?

  

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

• Question 73:

  View the Exhibit and examine the description of the ORDER_ITEMS and PRODUCT_INFORMATION tables.

  The ORDER_ITEM table has records pertaining to details for each product in an order. The PRODUCT_INFORMATION table has records for all the products available for ordering.

  Evaluate the following SOL statement:

  SELECT oi.order_id, pi.product_id

  FROM orderjtems oi RIGHT OUTER JOIN product_information pi ON (oi. product_id=pi. Product_id);

  Which statement is true regarding the output of this SOL statement?

  

  A. The query would return the ORDER_ID and PRODUCT_ID for only those products that are ordered.
  B. The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered as well as for the products that have never been ordered.
  C. The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered but not listed in the PRODUCT_INFORMATION table.
  D. The query would return the ORDER_ID and PRODUCT_ID for those products that are ordered as well as for the products that have never been ordered, and for the products that are not listed in the PRODUCT INFORMATION table.
  B. The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered as well as for the products that have never been ordered.

• Question 74:

  ORD is a private synonym for the OE.ORDERS table.

  The user OE issues the following command:

  DROP SYNONYM ord;

  Which statement is true regarding the above SOL statement?

  A. Only the synonym would be dropped.
  B. The synonym would be dropped and the corresponding table would become invalid.
  C. The synonym would be dropped and the packages referring to the synonym would be dropped.
  D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.
  A. Only the synonym would be dropped.

• Question 75:

  View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables.

  

  Which SET operator would you use in the blank space in the following SQL statement to list the departments where all the employees have managers?

  SELECT department_id FROM departments

  SELECT department_id FROM employees WHERE manager_id IS NULL;

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

• Question 76:

  EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?

  A. UPDATE empdet SET ename = 'Amit' WHERE empno = 1234;
  B. DELETE FROM empdet WHERE ename LIKE 'J%';
  C. CREATE VIEW empvu ASSELECT* FROM empdept;
  D. CREATE INDEX empdet_dx ON empdet(empno);
  C. CREATE VIEW empvu ASSELECT* FROM empdept;

• Question 77:

  The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR. Which statement would create a synonym ORD so that HR can execute the following query successfully? SELECT'FROM ord;

  A. CREATE SYNONYM ord FOR orders; This command is issued by OE.
  B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
  C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
  D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
  D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

• Question 78:

  Evaluate the following statement:

  INSERT ALL WHEN order_total < 10000 THEN INTO small_orders WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders WHEN order_total > 2000000 THEN INTO large_orders SELECT order_id, order_total, customer_id FROM orders;

  Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

  A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
  B. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
  C. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
  D. The INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true.
  A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

• Question 79:

  View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables. In the ORDERS table, ORDER_ID is the PRIMARY KEY and ORDER_DATE has the DEFAULT value as SYSDATE.

  

  Evaluate the following statement: UPDATE orders SET order_date=DEFAULT WHERE order_id IN (SELECT order_id FROM order_items WHERE qty IS NULL);

  What would be the outcome of the above statement?

  A. The UPDATE statement would not work because the main query and the subquery use different tables.
  B. The UPDATE statement would not work because the DEFAULT value can be used only in INSERT statements.
  C. The UPDATE statement would change all ORDER_DATE values to SYSDATE provided the current ORDER_DATE is NOT NULL and QTY is NULL
  D. The UPDATE statement would change all the ORDER_DATE values to SYSDATE irrespective of what the current ORDER_DATE value is for all orders where QTY is NULL
  D. The UPDATE statement would change all the ORDER_DATE values to SYSDATE irrespective of what the current ORDER_DATE value is for all orders where QTY is NULL

• Question 80:

  View the Exhibit and examine the data in EMP and DEPT tables.

  

  In the DEPT table, DEPTNO is the PRIMARY KEY.

  In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the DEPTNO column in the DEPT table.

  What would be the outcome of the following statements executed in the given sequence?

  DROP TABLE emp;

  FLASHBACK TABLE emp TO BEFORE DROP;

  INSERT INTO emp VALUES (2,COTT 10);

  INSERT INTO emp VALUES (3,ING 55);

  A. Both the INSERT statements would fail because all constraints are automatically retrieved when the table is flashed back.
  B. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.
  C. Only the first INSERT statement would succeed because all the constraints except the primary key constraint are automatically retrieved after a table is flashed back.
  D. Only the second INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.
  D. Only the second INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.