Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 151:

  View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables.

  

  Which is the best method to load data from the MARKS DETAILStable to the MARKStable?

  A. Pivoting INSERT
  B. Unconditional INSERT
  C. Conditional ALL INSERT
  D. Conditional FIRST INSERT
  A. Pivoting INSERT

• Question 152:

  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,
  B. 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;
  C. 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 =
  D. order_id)GROUP BY o.order_id,o.order_date;
  E. 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
  F. order_id,o.order_date;
  G. 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 i ON (o.order_id =
  H. order_id) GROUP BY o.order_id,o.order_date WITH CHECK OPTION;
  B. 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;

• Question 153:

  View the Exhibit and examine the structure of the PRODUCT INFORMATION table.

  

  Which two queries would work? (Choose two.)

  A. SELECT product_nameFROM product_informationWHERE list_price = (SELECT AVG(list_price) FROM product_information);
  B. SELECT product_statusFROM product_informationGROUP BY product_statusWHERE list_price < (SELECT AVG(list_price)FROM product_information);
  C. SELECT product_statusFROM product_informationGROUP BY product_statusHAVING list_price > (SELECT AVG(list_price)FROM product_information);
  D. SELECT product_name FROM product_jnformation WHERE list_price < ANY(SELECT AVG(list_price) FROM product_jnformation GROUP BY product_status);
  A. SELECT product_nameFROM product_informationWHERE list_price = (SELECT AVG(list_price) FROM product_information);
  D. SELECT product_name FROM product_jnformation WHERE list_price < ANY(SELECT AVG(list_price) FROM product_jnformation GROUP BY product_status);

• Question 154:

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

  The ORDERS table belongs to the user OE. HR is another user in the database.

  Evaluate the commands issued by users OE and HR in the following order:

  Statement 1 by user OE: GRANT SELECT,

  UPDATE(customer_id, order_total)

  ON orders

  TOhr;

  Statement 1 by user HR: SELECT * FROM oe.orders;

  Statement 2 by user HR: UPDATE oe.orders

  SET order_totah 10000;

  Which statement is true regarding the above commands?

  

  A. Statement 1 by user OE would not work because the statement has to be issued by the DBA.
  B. Statement 2 by user HR would not work because the grant is only for SELECT in a subquery of update.
  C. There are no errors in the statements issued by OE and HR; all the statements would execute successfully.
  D. Statement 1 by user HR would not work because SELECT and UPDATE privileges have been granted only on CUSTOMER_ID and ORDER_TOTAL columns.
  C. There are no errors in the statements issued by OE and HR; all the statements would execute successfully.

• Question 155:

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

  

  You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers.

  Which SOL statement would you use to accomplish the task?

  A. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name1'^A-Z '))NOVALIDATE;
  B. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'^[0-9]'))NOVALIDATE;
  C. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:alpha: ]]'))NOVALIDATE;
  D. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:digit: ]]'))NOVALIDATE ;
  C. ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:alpha: ]]'))NOVALIDATE;

• Question 156:

  Given below is the list of meta character syntaxes and their descriptions in random order: Meta character syntax Description 1)^ a) Matches character not in the list 2) [^...] b) Matches character when it occurs at the beginning of a line 3) | c) Treats the subsequent meta character as a literal 4) \ d) Matches one of the characters such as the OR operator

  Identify the option that correctly matches the meta character syntaxes with their descriptions.

  A. 1-b, 2-a, 3-d, 4-c
  B. 1-a, 2-b, 3-d, 4-c
  C. 1-d, 2-b, 3-a, 4-c
  D. 1-b, 2-c, 3-d, 2-a
  A. 1-b, 2-a, 3-d, 4-c

• Question 157:

  View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.

  

  Evaluate the following MERGE statement:

  MERGE INTO orders_master o USING monthly_orders m ON (o.order_id = m.order_id) WHEN MATCHED THEN UPDATE SET o.order_total = m.order_total DELETE WHERE (m.order_total IS NULL) WHEN NOT MATCHED THEN INSERT VALUES (m.order_id, m.order_total); What would be the outcome of the above statement?

  A. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.
  B. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 3.
  C. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 4.
  D. The ORDERS_MASTER table would contain the ORDER IDs 1,2,3 and 4.
  C. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 4.

• Question 158:

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

  Examine the following SQL statement:

  SELECT order_id, product_id, unit_price

  FROM order_jtems

  WHERE unit_price = (SELECT MAX(unit_price)

  FROM order_items

  GROUP BY order_id);

  You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID.

  What correction should be made in the above SQL statement to achieve this?

  

  A. Replace = with the IN operator.
  B. Replace = with the >ANY operator.
  C. Replace = with the >ALL operator.
  D. Remove the GROUP BY clause from the subquery and place it in the main query.
  A. Replace = with the IN operator.

• Question 159:

  Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)

  A. it can be used to concatenate two strings.
  B. it can be used to find out the total length of the string.
  C. it can be used for string manipulation and searching operations.
  D. it can be used to format the output for a column or expression having string data.
  E. it can be used to find and replace operations for a column or expression having string data.
  C. it can be used for string manipulation and searching operations.
  D. it can be used to format the output for a column or expression having string data.
  E. it can be used to find and replace operations for a column or expression having string data.

• Question 160:

  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.