Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 121:

  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 122:

  View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables.

  Evaluate the following SQL statement:

  SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount" FROM order_items oi JOIN orders o

  ON oi.order_id = o.order_id

  GROUP BY CUBE (o.customer_id, oi.product_id);

  Which three statements are true regarding the output of this SQL statement? (Choose three.)

  

  A. t would return the subtotals for the Order Amount of every CUSTOMER_ID.
  B. t would return the subtotals for the Order Amount for every PRODUCT_ID.
  C. t would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group.
  D. t would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
  E. t would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
  A. t would return the subtotals for the Order Amount of every CUSTOMER_ID.
  B. t would return the subtotals for the Order Amount for every PRODUCT_ID.
  D. t would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.

• Question 123:

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

  You executed the following command to add a primary key to the EMP table:

  ALTER TABLE emp

  ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)

  USING INDEX emp_id_idx;

  

  Which statement is true regarding the effect of the command?

  A. The PRIMARY KEY is created along with a new index.
  B. The PRIMARY KEY is created and it would use an existing unique index.
  C. The PRIMARY KEY would be created in a disabled state because it is using an existing index.
  D. The statement produces an error because the USING clause is permitted only in the CREATE TABLE command.
  B. The PRIMARY KEY is created and it would use an existing unique index.

• Question 124:

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

  

  Which statement would add a column called PRICE, which cannot contain NULL?

  A. ALTER TABLE productsADD price NUMBER(8,2) NOT NULL;
  B. ALTER TABLE productsADD price NUMBER(8,2) DEFAULT NOT NULL;
  C. ALTER TABLE productsADD price NUMBER(8,2) DEFAULT 0 NOT NULL;
  D. ALTER TABLE productsADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
  C. ALTER TABLE productsADD price NUMBER(8,2) DEFAULT 0 NOT NULL;

• Question 125:

  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_id, TO_YMINTERVAL(warranty_period) FROM product_information;
  C. SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information;
  D. SELECT product_id, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information;
  A. SELECT product_id, SYSDATE + warranty_period FROM product_information;

• Question 126:

  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 127:

  View the Exhibit and examine the data in EMPLOYEES and

  

  DEPARTMENTS tables. In the EMPLOYEES table EMPLOYEE_ID is the PRIMARY KEY and DEPARTMENT_ID is the FOREIGN KEY. In the DEPARTMENTS table DEPARTMENT_ID is the PRIMARY KEY.

  Evaluate the following UPDATE statement:

  UPDATE employees a

  SET department_jd =

  (SELECT department_id

  FROM departments

  WHERE location_id = `2100'),

  (salary, commission_pct) =

  (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)

  FROM employees b

  WHERE a. department_jd = b. department_id)

  WHERE first_name|| '||last_name = 'Amit Banda';

  What would be the outcome of the above statement?

  A. It would execute successfully and update the relevant data.
  B. It would not execute successfully because there is no LOCATION_ID 2100 in the DEPARTMENTS table.
  C. It would not execute successfully because the condition specified with the concatenation operator is not valid.
  D. It would not execute successfully because multiple columns (SALARY,COMMISSION_PCT)cannot be used in an UPDATE statement.
  A. It would execute successfully and update the relevant data.

• Question 128:

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

  

  The query should display the employee IDs of all the employees who have held the job SA_MAN at any time during their tenure.

  Choose the correct SET operator to fill in the blank space and complete the following query.

  SELECT employee_id

  FROM employees

  WHERE job_id = 'SA_MAN'

  SELECT employee_id FROMjob_history WHERE job_id='SA_MAN'

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

• Question 129:

  View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.

  You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.

  Which two SQL statements can accomplish the task? (Choose two.)

  

  A. SELECT product_id, quantity_on_hand , supplier_id FROM product_information NATURAL JOIN inventories AND quantity_on_hand < 5;
  B. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventories i USING (product_id) AND quantity_on_hand < 5;
  C. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventories i ON (pi. product_id=i. product_id) WHERE quantity_on_hand < 5;
  D. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventories i ON (pi. product_id=i. product_id) AND quantity_on_hand < 5;
  C. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventories i ON (pi. product_id=i. product_id) WHERE quantity_on_hand < 5;
  D. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventories i ON (pi. product_id=i. product_id) AND quantity_on_hand < 5;

• Question 130:

  View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

  Which DELETE statement would execute successfully?

  

  A. DELETE order_id FROM ordersWHERE order_total < 1000;
  B. DELETE orders WHERE order_total < 1000;
  C. DELETE FROM ordersWHERE (SELECT order_id FROM order_items);
  D. DELETE orders o, order_items i WHERE o.order id = i.order id;
  B. DELETE orders WHERE order_total < 1000;