Oracle Oracle Database 1Z0-071 Questions & Answers

• Question 31:

  Which statement executes successfully?

  A. SELECT TO_DATE(TO_NUMBER(INTERVATL '800' SECOND)) FROM DUAL;

  B. SELECT TO_NUMBER(INTERVAL'800' SECOND, 'HH24:MM') FROM DUAL;

  C. SELECT TO_DATE(INTERVAL '800' SECOND,'HH24:MM') FROM DUAL;

  D. SELECT TO_NUWBER(TO_DATE(INTERVAL '800' SECOND)) FROM DUAL;

  E. SELECT TO_CHAR(INTERVAL '800' SECOND, 'HH24:MM') FROM DUAL;

  Correct Answer: E

• Question 32:

  Examine the description of the transactions table: Which two SQL statements execute successfully?

  

  A. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from transactions;

  B. SELECT customer_id AS 'CUSTOMER-ID',transaction_date AS DATE, amount+100 'DUES' from transactions;

  C. SELECT customer_id CUSTID, transaction_date TRANS_DATEamount+100 DUES FROM transactions;

  D. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM transactions;

  E. SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES AMOUNT" FROM transactions;

  Correct Answer: CD

• Question 33:

  Evaluate the following SQL statement SQL>SELECT promo_id, prom _category FROM promotions WHERE promo_category='Internet' ORDER BY promo_id UNION SELECT promo_id, promo_category FROM Pomotions WHERE promo_category = `TV' UNION SELECT promoid, promocategory FROM promotions WHERE promo category='Radio' Which statement is true regarding the outcome of the above query?

  A. It executes successfully and displays rows in the descend ignore of PROMO CATEGORY.

  B. It produces an error because positional, notation cannot be used in the ORDER BY clause with SBT operators.

  C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.

  D. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.

  Correct Answer: D

• Question 34:

  Which two statements are true about selecting related rows from two tables based on entity relationship diagram (ERD)?

  A. Relating data from a table with data from the same table is implemented with a self join.

  B. An inner join relates rows within the same table.

  C. Rows from unrelated tables cannot be joined.

  D. Implementing a relationship between two tables might require joining additional tables.

  E. Every relationship between the two tables must be implemented in a Join condition.

  Correct Answer: AD

• Question 35:

  Examine the description or the CUSTOMERS table:

  

  For Customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.

  Which query should be used?

  A. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND cust_credit_level != NULL;

  B. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers WHERE cust_income_level <> NULL AND due_amount <> NULL;

  C. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;

  D. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL;

  E. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers WHERE cust_income_level != NULL AND due_amount != NULL;

  Correct Answer: C

• Question 36:

  Which two statements are true about substitution variables?

  A. A substitution variable used to prompt for a column name must be endorsed in single quotation marks.

  B. A substitution variable used to prompt for a column name must be endorsed in double quotation marks.

  C. A substitution variable prefixed with and always prompts only once for a value in a session.

  D. A substitution variable can be used with any clause in a SELECT statement.

  E. A substitution variable can be used only in a SELECT statement.

  F. A substitution variable prefixed with 6 prompts only once for a value in a session unless is set to undefined in the session.

  Correct Answer: DF

• Question 37:

  Which three statements are true about an ORDER BY clause?

  A. An ORDER BY clause always sorts NULL values last.

  B. An ORDER BY clause can perform a binary sort

  C. An ORDER BY clause can perform a linguistic sort

  D. By default an ORDERBY clause sorts rows in ascending order

  E. An ORDR BY clause will always precede a HAVI NG clause if both are used in the same top-level

  Correct Answer: BCD

• Question 38:

  Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)?

  A. There must be an equal number of columns in each SELECT list.

  B. The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.

  C. Each SELECT statement in the query can have an ORDER BY clause.

  D. None of the set operators can be used when selecting CLOB columns.

  E. The FOR UPDATE clause cannot be specified.

  Correct Answer: AE

  Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

• Question 39:

  In your session, the NLS._DAE_FORMAT is DD- MM- YYYY.There are 86400 seconds in a day.Examine this result:

  1.

  DATE

  2.

  02-JAN-2020

  Which statement returns this?

  A. SELECT TO_ CHAR(TO_ DATE(`29-10-2019') +INTERVAL `2'; MONTH + INTERVAL `5'; DAY INTERVAL `86410' SECOND, ` DD-MON-YYYY') AS "date" FROM DUAL;

  B. SELECT TO_ CHAR(TO_ DATE(`29-10-2019') + INTERVAL `3' MONTH + INTERVAL `7' DAY INTERVAL `360' SECOND, ` DD-MON-YYYY') AS "date" FROM DUAL;

  C. SELECT To CHAR(TO _DATE(`29-10-2019') + INTERVAL `2' NONTH + INTERVAL `5' DAY INEERVAL `120' SECOND, ` DD-MON-YYY) AS "date" FROM DUAL;

  D. SELECT-TO_CHAR(TO _DATE(`29-10-2019'+ INTERVAL `2' MONTH+INTERVAL `6' DAYINTERVAL `120' SECOND, `DD-MON-YY') AS "daTe" FROM DUAL;

  E. SELECT-TO_CHAR(TO _DATE(`29-10-2019'+ INTERVAL `2' MONTH+INTERVAL `4' DAYINTERVAL `120' SECOND, `DD-MON-YY') AS "daTe" FROM DUAL;

  Correct Answer: C

• Question 40:

  Which two are true about multiple table INSERT statements?

  A. They always use subqueries.

  B. They can transform a row from a source table into multiple rows in a target table.

  C. The conditional INSERT FIRST statement always inserts a row into a single table.

  D. The conditional INSERT ALL statement inserts rows into a single table by aggregating source rows.

  E. The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables.

  Correct Answer: AB