Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 281:

  Examine this query:

  SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL;

  What is the result?

  A. 16

  B. 160

  C. 150

  D. 200

  E. 100

  Correct Answer: D

• Question 282:

  The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.

  The table has two rows whose COST_LAST_MANE values are Anderson and Ausson.

  Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the second?

  A. SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;

  B. SELECT REPLACE (TRIM(TRALING'son' FROM cust_last_name),'An','O') FROM customers;

  C. SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;

  D. SELECT REPLACE (SUBSTR(cust_last_name,-3),'An','O') FROM customers;

  Correct Answer: A

• Question 283:

  Examine these statements executed in a single Oracle session:

  

  Which three statements are true? (Choose three.)

  A. There is no row containing pencil.

  B. The code for pen is 10.

  C. There is no row containing fountain pen.

  D. The code for pen is 1.

  E. There is no row containing pen.

  F. The code for fountain pen is 3.

  Correct Answer: ABF

• Question 284:

  BOOK_SEQ is an existing sequence in your schema.

  Which two CREATE TABLE commands are valid?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: AD

• Question 285:

  The PROD_ID column is the foreign key in the SALES table.Which references the PRODUCTS table.

  Similarly,the CUST_ID and TIME_ID columns are Also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

  Evaluate the following CREATE TABLE command:

  CREATE TABLE new_sales(prod_id, I cust_id, order_date DEFAULT SYSDATE)

  AS SELECT I prod_id,cust_id,time_id FROM sales.

  Which statement is true regarding the above command?

  A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.

  B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.

  C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause I do not match.

  D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table

  Correct Answer: B

• Question 286:

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

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

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

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

  Examine thee statements Which execute successfully:

  

  Which two are true? (Choose two.)

  A. User FIN_CLERK can grant SELECT on SCOTT.EMP to user FIN_MANAGER.

  B. Dropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERK.

  C. User FINANCE can grant CREATE SESSION to user FIN_MANAGER.

  D. Revoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK.

  E. User FINANCE is unable to grant all on SCOTT.EMP to FIN_MANAGER.

  Correct Answer: DE