Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 211:

  Examine the description of the ENPLOYES table:

  

  NLS_DATE_FORMAT is set to DD-MON-YY.

  Which query requires explicit data type conversion?

  A. SELECT join_date FROM employees WHERE join_datee > '10-02-2018';

  B. SELECT join_date || ` ` || salary FROM employees;

  C. SELECT salary + '120.50' FROM employees;

  D. SELECT join_date + `20' FROM employees;

  E. SELECT SUBSTR(join_date, 1, 2) -1'

  Correct Answer: A

• Question 212:

  The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE NLS_DATE_FORMAT is set to DD-MON-RR.

  Which two are true about data type conversions involving these columns in query expressions?

  A. invoice_date> '01-02-2019': uses implicit conversion

  B. qty_sold ='05549821 ': requires explicit conversion

  C. CONCAT(qty_sold, invoice_date): requires explicit conversion

  D. qty_sold BETWEEN '101' AND '110': uses implicit conversion

  E. invoice_date = '15-march-2019': uses implicit conversion

  Correct Answer: DE

• Question 213:

  You need to allow user ANDREW to:

  1.

  Modify the TITLE and ADDRESS columns of your CUSTOMERS table.

  2.

  GRANT tha permission to other users.

  Which statement will do this?

  A. GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;

  B. GRANT UPDATE ON customers.title, customers.address TO andrew WITH GRANT OPTION;

  C. GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;

  D. GRANT UPDATE (title, address) ON customers TO andrew;

  E. GRANT UPDATE ON customers. title, customers.address TO andrew;

  F. GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION:

  Correct Answer: B

• Question 214:

  Which two are true? (Choose two.)

  A. ADD_MONTHS adds a number of calendar months to a date.

  B. CEIL requires an argument which is a numeric data type.

  C. CEIL returns the largest integer less than or equal to a specified number.

  D. LAST_DAY returns the date of the last day of the current month only.

  E. LAST_DAY returns the date of the last day of the month for the date argument passed to the function.

  F. LAST_DAY returns the date of the last day of the previous month only.

  Correct Answer: AE

  https //docs.oracle.com/database/121/SQLRF/functions025.htm https //docs.oracle.com/cd/B2835901/server111/b28286/functionsQ04.htm

• Question 215:

  Which two will execute successfully?

  A. SELECT COALESCR('DATE', SYSDATE) FROM DUAL;

  B. SELECT NVL('DATE',SYSDATE) FROM DUAL;

  C. SELECT COALESCE(O,SYSDATE) TRCH DUAL;

  D. SELECT NVL('DATE', 200) FROM (SELECT NULL AS "DATE" FROM DUAL);

  E. SELECT COALESCE('DATE',SYSDATE) FROM (SELECT NULL AS "DATE" FROM DUAL) ;

  Correct Answer: BD

• Question 216:

  Examine the description of EMPLOYEES table:

  

  Which three queries return all rows for which SALARY + COMMISSION is greater than 20000? (Choose three.)

  A. SELECT * FROM employees WHERE NVL2(salary + commission, salary + commission, salary) >= 20000;

  B. SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >= 20000

  C. SELECT * FROM employees WHERE NVL(salary + commission, 0) >= 20000;

  D. SELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000;

  E. SELECT * FROM employees WHERE COALESCE(salary, commission) >= 20000;

  F. SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;

  Correct Answer: ABF

• Question 217:

  No-user-defined locks are used in your database.

  Which three are true about Transaction Control Language (TCL)?

  A. COMMIT erases all the transaction's savepoints and releases its locks.

  B. COMMIT ends the transaction and makes all its changes permanent.

  C. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks.

  D. ROLLBACK to SAVEPOTNT undoes the transaction's changes made since the named savepoint and then ends the transaction.

  E. ROLLBACK without the TO SAVEPOINT clause undoes alt the transaction's changes, releases its locks, and erases all its savepoints.

  F. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.

  Correct Answer: ABE

• Question 218:

  You own table DEPARTMENTS, referenced by views, indexes, and synonyms.

  Examine this command Which executes successfully:

  DROP TABLE departments PURGE;

  Which three statements are true?

  A. Neither can it be rolled back nor can the DEPARTMENTS table be recovered.

  B. It will remove all views that are based on the DEPARTMENTS table.

  C. It will delete all rows from the DEPARTMENTS table, but retain the empty table.

  D. It will remove the DEPARTMENTS table from the database.

  E. It will remove all synonyms for the DEPARTMENTS table.

  F. It will drop all indexes on the DEPARTMENTS table.

  Correct Answer: ADF

  Reference:https //docs.oracle.com/cd/B28359_01/server.111/b28310/tables010.htm#ADMIN01505

• Question 219:

  Which three are key components of an Entity Relationship Model? (Choose three.)

  A. a table

  B. an attribute

  C. a unique identifier

  D. an activity

  E. a relationship

  F. an entity

  Correct Answer: BEF

• Question 220:

  Examine the description of the CUSTONERS table:

  

  CUSTNO is the PRIMARY KEY.

  You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.

  Which two methods can you use to get the required result?

  A. LEFT OUTER JOIN with self join

  B. PULL OUTER JOIN with self join

  C. subquery

  D. RIGHT OUTER JOIN with self join

  E. self Join

  Correct Answer: CE