Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 131:

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

  Evaluate the following SOL statement: SELECT street_address FROM locations WHERE REGEXP_INSTR(street_address,'[^[: alpha:]]') = 1;

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

  

  A. It would display all the street addresses that do not have a substring 'alpha1.
  B. It would display all the street addresses where the first character is a special character.
  C. It would display all the street addresses where the first character is a letter of the alphabet.
  D. It would display all the street addresses where the first character is not a letter of the alphabet.
  D. It would display all the street addresses where the first character is not a letter of the alphabet.

• Question 132:

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

  

  Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME, DEPARTMENT_ID, and the total annual salary:

  SELECT last_name, departmentjd, salary450*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5;

  When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly?

  A. Change the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation".
  B. Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation".
  C. Change the SELECT clause to SELECT last_name, department_id, (salary +50)*12 "Annual Compensation".
  D. Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation".
  C. Change the SELECT clause to SELECT last_name, department_id, (salary +50)*12 "Annual Compensation".

• Question 133:

  Evaluate the following expression using meta character for regular expression: '[^Ale|ax.r$]'

  Which two matches would be returned by this expression? (Choose two.)

  A. Alex
  B. Alax
  C. Alxer
  D. Alaxendar
  E. Alexender
  D. Alaxendar
  E. Alexender

• Question 134:

  Evaluate the following SELECT statement and view the Exhibit to examine its output:

  

  SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS Which two statements are true about the output? (Choose two.)

  A. In the second column, indicates a check constraint.
  B. The STATUS column indicates whether the table is currently in use.
  C. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.
  D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.
  A. In the second column, indicates a check constraint.
  D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

• Question 135:

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

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

  Evaluate the following UPDATE statement:

  UPDATE (SELECT order_date, order_total, customer_id FROM orders) SET order_date = 22-mar-2007' WHERE customer_id = (SELECT customer_id FROM customers WHERE cust_last_name = 'Roberts'AND Credit_limit = 600);

  Which statement is true regarding the execution of the above UPDATE statement?

  

  A. It would not execute because two tables cannot be used in a single UPDATE statement.
  B. It would execute and restrict modifications to only the columns specified in the SELECT statement.
  C. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
  D. It would not execute because the SELECT statement cannot be used in place of the table name.
  B. It would execute and restrict modifications to only the columns specified in the SELECT statement.

• Question 137:

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

  A. A simple view in which column aliases have been used cannot be updated.
  B. A subquery used in a complex view definition cannot contain group functions or joins.
  C. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
  D. Rows added through a view are deleted from the table automatically when the view is dropped.
  E. The OR REPLACE option is used to change the definition of an existing view without dropping and re- creating it.
  F. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
  C. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
  E. The OR REPLACE option is used to change the definition of an existing view without dropping and re- creating it.

• Question 138:

  View the Exhibit and examine the data in ORDERS_MASTER and MONTHLYjDRDERS 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 ORDERJDs 1 and 2.
  B. The ORDERS_MASTER table would contain the ORDERJDs 1,2 and 3.
  C. The ORDERS_MASTER table would contain the ORDERJDs 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 ORDERJDs 1,2 and 4.

• Question 139:

  Evaluate the following query:

  SELECT INTERVAL 300' MONTH,

  INTERVAL 54-2' YEAR TO MONTH,

  INTERVAL '11:12:10.1234567' HOUR TO SECOND

  FROM dual;

  What is the correct output of the above query?

  A. +25-00, +54-02, +00 11:12:10.123457
  B. +00-300, +54-02, +00 11:12:10.123457
  C. +25-00, +00-650, +00 11:12:10.123457
  D. +00-300, +00-650, +00 11:12:10.123457
  A. +25-00, +54-02, +00 11:12:10.123457

• Question 140:

  Which statement best describes the GROUPING function?

  A. It is used to set the order for the groups to be used for calculating the grand totals and subtotals.
  B. It is used to form various groups to calculate total and subtotals created using ROLLUP and CUBE operators.
  C. It is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE.
  D. It is used to specify the concatenated group expressions to be used for calculating the grand totals and subtotals.
  C. It is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE.