Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 351:

  Examine the description of the ENPLYEES table:

  

  Which two queries return all rows for employees whose salary is greater than the average salary in their department?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: BE

• Question 352:

  Examine the data in the ENPLOYEES table:

  

  Which statement will compute the total annual compensation tor each employee

  A. SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) AS annual_comp FROM employees;

  B. SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * monthly_commission_pct) AS annual_comp FROM employees;

  C. SELECT last_name, (monthly_salary + monthly_commission_pct) * 12 AS annual_comp FROM employees;

  D. SELECT last_name, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp FROM employees;

  Correct Answer: A

• Question 353:

  Which two are true about scalar subquery expressions?

  A. You cannot correlate them with a table in the parent statement

  B. You can use them as a default value for a column.

  C. You must enclose them in parentheses.

  D. They can return at most one row.

  E. They can return two columns.

  Correct Answer: CD

• Question 354:

  Examine the description of the PRODUCT_STATUS table:

  

  The STATUS column contains the values 'IN STOCK' or 'OUT OF STOCK' for each row.

  Which two queries will execute successfully?

  A. SELECT prod_id "CURRENT AVAILABILITY" || q'('s not available)' FROM product_status WHERE status = 'OUT OF STOCK';

  B. SELECT prod_id || q's not available'' FROM product_status WHERE status='OUT OF STOCK';

  C. SELECT prod_id || q'('s not available)' "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';

  D. SELECT prod_id || q'('s not available)' FROM product_status WHERE status = 'OUT OF STOCK';

  E. SELECT prod_id || q'('s not available)' 'CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK';

  F. SELECT prod_id || q"'s not available" FROM product_status WHERE status = 'OUT OF STOCK';

  Correct Answer: CD

• Question 355:

  Which two statements are true? (Choose two.)

  A. The USER SYNONYMS view can provide information about private synonyms.

  B. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.

  C. All the dynamic performance views prefixed with V$ are accessible to all the database users.

  D. The USER OBJECTS view can provide information about the tables and views created by the user only.

  E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

  Correct Answer: AE

• Question 356:

  Examine this statement which executes successfully:

  

  Which is true?

  A. Only if the salary is 20000 or less and the employee id is 125 or higher, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.

  B. Regardless of salary and employee id, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.

  C. Regardless of salary, only if the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.

  D. Only if the salary is 20000 or less and the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.

  Correct Answer: D

• Question 357:

  Examine the data in the CUST_NAME column of the CUSTOMERS table:

  

  You want to display the CUST_NAME values where the last name starts with Mc or MC.

  Which two WHERE clauses give the required result?

  A. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'Mc%'

  B. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1) LIKE 'Mc%'

  C. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1 ) LIKE 'Mc%' OR 'MC%'

  D. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE UPPER('MC%')

  E. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) IN ('MC%', 'Mc%)

  Correct Answer: AD

• Question 358:

  You execute this command:

  TRUNCATE TABIE depts;

  Which two are true?

  A. A ROLLBACK statement can be used to retrieve the deleted data.

  B. It drops any triggers defined on the table.

  C. It retains the indexes defined on the table.

  D. It retains the integrity constraints defined on the table,

  E. It always retains the space used by the removed rows.

  F. A FLASHBACK TABLE statement can be used to retrieve the deleted data.

  Correct Answer: CD

• Question 359:

  A session's NLS_DATE_FORMAT is set to DD Mon YYYY.

  Which two queries return the value 1 Jan 2019? (Choose two.)

  A. SELECT TO_DATE('2019-01-01', 'YYYY-MM-DD') FROM DUAL;

  B. SELECT DATE '2019-01-01' FROM DUAL ;

  C. SELECT TO_CHAR('2019-01-01') FROM DUAL; 2019-01-01

  D. SELECT '2019-01-01' FROM DUAL ; 2019-01-01

  E. SELECT TO_ DATE('2019-01-01') FROM DUAL;

  Correct Answer: AB

• Question 360:

  Which two statements are true about single row functions?

  A. CONCAT: can be used to combine any number of values

  B. MOD: returns the quotient of a division operation

  C. CEIL: can be used for positive and negative numbers

  D. FLOOR: returns the smallest integer greater than or equal to a specified number

  E. TRUNC: can be used with NUMBER and DATE values

  Correct Answer: CE