Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 141:

  Given below is a list of functions and the tasks performed by using these functions, in random order.

  Function Usage 1) LPAD a) Used to truncate a column, expression, or value to n decimal places 2) TRUNC b) Used to remove heading or trailing or both characters from the character string 3) DECODE c) Pads the character value right-justified to a total width of n character positions 4) TRIM d) Used to return the numeric value for position of a named character from the character string 5) INSTR e) Used to translate an expression after comparing it with each search value

  Which option correctly matches the function names with their usage?

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

• Question 142:

  Evaluate the following SQL statement:

  SELECT product_name || 'it's not available for order'

  FROM product_information

  WHERE product_status = 'obsolete';

  You received the following error while executing the above query:

  ERROR:

  ORA-01756: quoted string not properly terminated

  What would you do to execute the query successfully?

  A. Enclose the character literal string in the SELECT clause within the double quotation marks.
  B. Do not enclose the character literal string in the SELECT clause within the single quotation marks.
  C. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.
  D. Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.
  C. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.

• Question 143:

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

  You need to display CUSTOMER_ID for all customers who have placed orders more than three times in the last six months. You issued the following SQL statement:

  SELECT customer_id,COUNT(order_id) FROM orders WHERE COUNT(order_id)>3 AND order_date BETWEEN ADD_MONTHS(SYSDATE,-6) AND SYSDATE GROUP BY customer_id;

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

  A. It would execute successfully and provide the desired result.
  B. It would not execute because the WHERE clause cannot have an aggregate function.
  C. It would not execute because the ORDER_ID column is not included in the GROUP BY clause.
  D. It would not execute because the GROUP BY clause should be placed before the WHERE clause.
  B. It would not execute because the WHERE clause cannot have an aggregate function.

• Question 144:

  View the Exhibit and examine the structure of the ORDER_ITEMS and ORDERS tables. You are asked to retrieve the ORDER_ID, PRODUCT_ID, and total price (UNIT_PRICE multiplied by QUANTITY), where the total price is greater than

  50,000.

  You executed the following SQL statement:

  SELECT order_id, product_id, unit_price*quantity "Total Price" FROM order_items WHERE unit_price*quantity > 50000 NATURAL JOIN orders;

  Which statement is true regarding the execution of the statement?

  

  A. The statement would execute and provide the desired result.
  B. The statement would not execute because the ON keyword is missing in the NATURAL JOIN clause.
  C. The statement would not execute because the WHERE clause is before the NATURAL JOIN clause.
  D. The statement would not execute because the USING keyword is missing in the NATURAL JOIN clause.
  C. The statement would not execute because the WHERE clause is before the NATURAL JOIN clause.

• Question 145:

  Which three statements are true regarding single-row functions? (Choose three.)

  A. They can accept only one argument.
  B. They can be nested up to only two levels.
  C. They can return multiple values of more than one data type.
  D. They can be used in SELECT, WHERE, and ORDER BY clauses.
  E. They can modify the data type of the argument that is referenced.
  F. They can accept a column name, expression, variable name, or a user-supplied constant as arguments.
  D. They can be used in SELECT, WHERE, and ORDER BY clauses.
  E. They can modify the data type of the argument that is referenced.
  F. They can accept a column name, expression, variable name, or a user-supplied constant as arguments.

• Question 146:

  Which statements are true? (Choose all that apply.)

  A. The data dictionary is created and maintained by the database administrator.
  B. The data dictionary views can consist of joins of dictionary base tables and user-defined tables.
  C. The usernames of all the users including the database administrators are stored in the data dictionary.
  D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
  E. Both USER_ODBJECTS and CAT views provide the same information about all the objects that are owned by the user.
  F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary
  C. The usernames of all the users including the database administrators are stored in the data dictionary.
  D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
  F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary

• Question 147:

  Which two statements are true about the GROUPING function? (Choose two.)

  A. It is used to find the groups forming the subtotal in a row.
  B. It is used to identify the NULL value in the aggregate functions.
  C. It is used to form the group sets involved in generating the totals and subtotals.
  D. It can only be used with ROLLUP and CUBE operators specified in the GROUP BY clause.
  A. It is used to find the groups forming the subtotal in a row.
  D. It can only be used with ROLLUP and CUBE operators specified in the GROUP BY clause.

• Question 148:

  View the Exhibit and examine the data in the PRODUCT_INFORMATION table. There are some products listed in the PRODUCT_INFORMATION table that have no value in the LIST_PRICE column. You issued the following SQL statement to find out the PRODUCT_NAME for these products:

  SELECT product_name, list_price

  FROM product_information WHERE list_price = NULL;

  

  The query returns no rows. What changes would you make in the statement to get the desired result?

  A. Change the WHERE clause to WHERE list_price = 0
  B. Change the WHERE clause to WHERE list_price = ''.
  C. Change the WHERE clause to WHERE list_price IS NULL.
  D. In the WHERE clause, enclose NULL within single quotation marks.
  E. In the WHERE clause, enclose NULL within double quotation marks.
  C. Change the WHERE clause to WHERE list_price IS NULL.

• Question 149:

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

  You want to display ORDER_ID, PRODUCT_ID, and TOTAL (UNIT_PRICE multiplied by QUANTITY) for all the orders placed in the last seven days.

  Which query would you execute?

  

  A. SELECT orde_id, product_id, unit_price*quantity "TOTAL" FROM order_items oi JOIN orders oON (o.order_id=oi. order_id) WHERE o.order_date>=SYSDATE-7;
  B. SELECT o.order_id,oi.product_id, oi.unit_price*oi.quantity "TOTAL" FROM order_items oi JOIN orders oUSING (order_id)WHERE o.order_date>=SYSDATE-7;
  C. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL" FROM order_items oi JOIN orders oWHERE o.order_date>=SYSDATE-7 ON (o.order_id=oi. order_id);
  D. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL" FROM orde_items oi JOIN orders oON (o.order_id=oi. order_id) WHERE o. order date>=SYSDATE-7;
  D. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL" FROM orde_items oi JOIN orders oON (o.order_id=oi. order_id) WHERE o. order date>=SYSDATE-7;

• Question 150:

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

  The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint.

  Evaluate the following statement:

  INSERT INTO (SELECT order_id.order_date.customer_id

  FROM ORDERS

  WHERE order_total = 1000

  WITH CHECK OPTION)

  VALUES (13, SYSDATE, 101);

  What would be the outcome of the above INSERT statement?

  

  A. It would execute successfully and the new row would be inserted into a new temporary table created by the subquery.
  B. It would execute successfully and the ORDER_TOTAL column would have the value 1000 inserted automatically in the new row.
  C. It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.
  D. It would not execute successfully because all the columns from the ORDERS table should have been included in the SELECT list and values should have been provided for all the columns.
  C. It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.