Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 311:

  Examine the description of the PRODUCTS table:

  

  Which three queries use valid expressions? (Choose three.)

  A. SELECT product_id, unit_price, S "Discount", unit_price + surcharge - discount FROM products;

  B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;

  C. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;

  D. SELECT product_id, unit_price || 5 "Discount", unit_price + surcharge - discount FROM products;

  E. SELECT product_id, expiry_date * 2 FROM products;

  F. SELECT product_id, unit_price, unit_price + surcharge FROM products;

  Correct Answer: BCF

• Question 312:

  You execute this command:

  ALTER TABLE employees SET UNUSED (department_id);

  Which two are true?

  A. A query can display data from the DEPARTMENT_ID column.

  B. The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.

  C. The DEPARTMENT_ID column is set to null for all tows in the table

  D. A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.

  E. No updates can be made to the data in the DEPARTMENT_ID column.

  F. The DEPARTMENT_ID column can be recovered from the recycle bin

  Correct Answer: DE

• Question 313:

  Which two are true about the NVL, NVL2, and COALESCE functions?

  A. The first expression in NVL2 is never returned.

  B. NVL2 can have any number of expressions in the list.

  C. COALESCE stops evaluating the list of expressions when it finds the first null value.

  D. COALESCE stops evaluating the list of expressions when it finds the first non-null value.

  E. NVL must have expressions of the same data type.

  F. NVL can have any number of expressions in the list.

  Correct Answer: AD

• Question 314:

  Which two are true about the precedence of opertors and condtions

  A. + (addition) has a higher order of precedence than * (mliplpition)

  B. NOT has a higher order of precedence than AND and OR in a condition.

  C. AND and OR have the same order of precedence in a condition

  D. Operators are evaluated before conditions.

  E. || has a higher order of precedence than +(addition)

  Correct Answer: BD

• Question 315:

  You have been asked to create a table for a banking application.

  One of the columns must meet three requirements:

  1: Be stored in a format supporting date arithmetic without using conversion functions

  2: Store a loan period of up to 10 years

  3:

  Be used for calculating interest for the number of days the loan remains unpaid

  A.

  TIMESTAMP WITH TIMEZONE

  B.

  TIMESTAMP

  C.

  TIMESTAMP WITH LOCAL TIMEZONE

  D.

  INTERVAL YEAR TO MONTH

  E.

  INTERVAL DAY TO SECOND

  Which data type should you use?

  Correct Answer: E

• Question 316:

  Which two are SQL features? (Choose two.)

  A. providing graphical capabilities

  B. providing variable definition capabilities

  C. providing database transaction control

  D. processing sets of data

  E. providing update capabilities for data in external files

  Correct Answer: CD

• Question 317:

  Examine these statements Which execute successfully:

  ALTER SESSION SET NLS_DATE_FORMAT = `DD-MON-YYYY HH24 MI: SS'

  ALTER SESSION SET TIME_ ZONE = `-5:00';

  SELECT DBTIMEZONE, SYSDATE FROM DUAL

  Examine the result:

  

  If LOCALTIMESTAMP was selected at the same time what would it return?

  A. 11-JUL-2019 6,00,00,00000000 AM ?05:00

  B. 11-JUL-2019 11,00,00,00000000 AM

  C. 11-JUL-2019 6.00.00.00000000 AM

  D. 11-JUL-2019 11,00,00,000000AM -05:00

  Correct Answer: C

• Question 318:

  Examine this description of the EMP table:

  

  You execute this query:

  SELECT deptno AS "departments", SUM (sal) AS "salary" FROM emp

  GROUP | BY 1

  HAVING SUM (sal)> 3 000;

  What is the result?

  A. only departments where the total salary is greater than 3000, returned in no particular order

  B. all departments and a sum of the salaries of employees with a salary greater than 3000

  C. an error

  D. only departments where the total salary is greater than 3000, ordered by department

  Correct Answer: C

• Question 319:

  Which three are true about dropping columns from a table?

  A. A column must be set as unused before it is dropped from a table.

  B. A primary key column cannot be dropped.

  C. Multiple columns can be dropped simultaneously using the ALTER TABLE command.

  D. A column can be removed only if it contains no data.

  E. A column that is referenced by another column in any other table cannot be dropped.

  F. A column drop is implicitly committed.

  Correct Answer: CEF

• Question 320:

  The ORDERS table has a primary key constraint on the ORDER_ID column.

  The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.

  The constraint is defined with on DELETE CASCADE.

  There are rows in the ORDERS table with an ORDER_TOTAL less than 1000.

  Which three DELETE statements execute successfully?

  A. DELETE order_id FROM orders WHERE order_total < 1000;

  B. DELETE orders WHERE order_total < 1000

  C. DELETE * FROM orders WHERE order_total < 1000;

  D. DELETE FROM orders;

  E. DELETE FROM orders WHERE order_total < 1000;

  Correct Answer: BDE