Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 171:

  Which two statements are true regarding multiple-row subqueries? (Choose two.)

  A. They can contain group functions.
  B. They always contain a subquery within a subquery.
  C. They use the < ALL operator to imply less than the maximum.
  D. They can be used to retrieve multiple rows from a single table only.
  E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.
  A. They can contain group functions.
  E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.

• Question 172:

  Which three possible values can be set for the TIME_ZONE session parameter by using the ALTER SESSION command? (Choose three.)

  A. 'os'
  B. local
  C. -8:00'
  D. dbtimezone Li
  E. 'Australia'
  B. local
  C. -8:00'
  D. dbtimezone Li

• Question 173:

  View the Exhibit and examine the description for EMPLOYEES and DEPARTMENTS tables.

  Evaluate the following SQL statement:

  SELECT e.department_id, e.job_id, d.location_id, sum(e. salary) total FROM employees e JOIN departments d

  ON e.department_id = d.department_id

  GROUP BY CUBE (e.department_id, e.job_id, d.location_id);

  Which two statements are true regarding the output of this command? (Choose two.)

  

  A. The output would display the total salary for all the departments.
  B. The output would display the total salary for all the JOB_IDs in a department.
  C. The output would display only the grand total of the salary for all JOB_IDs in a LOCATION_ID.
  D. The output would display the grand total of the salary for only the groups specified in the GROUP BY clause.
  A. The output would display the total salary for all the departments.
  B. The output would display the total salary for all the JOB_IDs in a department.

• Question 174:

  View the Exhibit and examine the ORDERS table.

  The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?

  

  A. ALTER TABLE ordersADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
  B. ALTER TABLE ordersMODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL;
  C. ALTER TABLE ordersMODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
  D. ALTER TABLE ordersADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;
  B. ALTER TABLE ordersMODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL;

• Question 175:

  Which statement is true regarding the SESSION_PRIVS dictionary view?

  A. It contains the current object privileges available in the user session.
  B. It contains the current system privileges available in the user session.
  C. It contains the object privileges granted to other users by the current user session.
  D. It contains the system privileges granted to other users by the current user session.
  B. It contains the current system privileges available in the user session.

• Question 176:

  View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.

  Evaluate the following SQL statement:

  SELECT oi.order_id, product_id, order_date FROM order_items oi JOIN orders o USING(order_id);

  

  Which statement is true regarding the execution of this SQL statement?

  A. The statement would not execute because table aliases are not allowed in the JOIN clause.
  B. The statement would not execute because the table alias prefix is not used in the USING clause.
  C. The statement would not execute because all the columns in the SELECT clause are not prefixed with table aliases.
  D. The statement would not execute because the column part of the USING clause cannot have a qualifier in the SELECT list.
  D. The statement would not execute because the column part of the USING clause cannot have a qualifier in the SELECT list.

• Question 177:

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

  

  Evaluate the following SQL statement:

  SELECT order_id, customer_id FROM orders WHERE order_date > 'June 30 2001';

  Which statement is true regarding the execution of this SQL statement?

  A. It would not execute because 'June 30 2001' in the WHERE condition is not enclosed within double quotation marks.
  B. It would execute and would return ORDER_ID and CUSTOMER_ID for all records having ORDER_DATE greater than 'June 30 2001'.
  C. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_DATE conversion function for proper execution.
  D. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_CHAR conversion function for proper execution.
  C. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_DATE conversion function for proper execution.

• Question 178:

  View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables. You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order.

  

  Which CREATE VIEW statement would create the view successfully?

  A. CREATE OR REPLACE VIEW ord_vu (order_id,order_date) AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) "NO OF ITEMS"FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date;
  B. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id,o.order_date;
  C. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date;
  D. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)ll' NO OF ITEMS' FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY
  E. order_id,o.order_date WITH CHECK OPTION;
  B. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id,o.order_date;

• Question 179:

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

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

  

  Evaluate the following SQL statements executed in the given order:

  ALTER TABLE cust

  ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED;

  INSERT INTO cust VALUES (1,'RAJ1); --row 1

  INSERT INTO cust VALUES (1,'SAM); --row 2

  COMMIT;

  SET CONSTRAINT cust_id_pk IMMEDIATE;

  INSERT INTO cust VALUES (1,'LATA'); --row 3

  INSERT INTO cust VALUES (2,'KING'); --row 4

  COMMIT;

  Which rows would be made permanent in the CUST table?

  A. row 4 only
  B. rows 2 and 4
  C. rows 3 and 4
  D. rows 1 and 4
  C. rows 3 and 4