Oracle 1Z0-071 Quiz 20 Topic 16 Questions 96-100

Question: 1

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

q1_1Z0-071

You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:

q1_1Z0-071

Which two queries give the required output?

ASELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') ''CUST NAME''FROM customersWHERE INSTR(cust_name, ' ',1,2)<>0;

BSELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') ''CUST NAME''FROM customersWHERE INSTR(cust_name, ' ',-1,2)<>0;

CSELECT LPAD(SUBSTR(cust_name, INSTR (cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, ' '), '*') ''CUST NAME''FROM customersWHERE INSTR(cust_name, ' ',1,-2)<>0;

DSELECT LPAD(SUBSTR(cust_name, INSTR (cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, ' '), '*') ''CUST NAME''FROM customersWHERE INSTR(cust_name, ' ',1,2)<>0;

Show Answer

Question: 2

View the Exhibit and examine the structure of the BOOKS table.

q2_1Z0-071

The BOOKS table contains details of 100 books.

Examine the commands executed and their outcome:

q2_1Z0-071

Which statement is true?

ABoth ROLLBACK commands restore the 101 rows that were deleted.

BBoth ROLLBACK commands restore the 100 rows that were deleted.

CThe first rollback restores the 101 rows that were deleted and the second rollback causes the row was inserted to be deleted and commits the changes.

DThe first rollback restores the 100 rows that were deleted and the second rollback commits only the changes.

Show Answer

Question: 3

Which statement is true about the Oracle SQL, DELETE and TRUNCATE statements?

ADELTE and TRUNCATE statements can have a rollback done to restore data into a table.

BDELETE and TRUNCATE statements remove all indexes for the tables on which they are performed.

CDELETE but not TRUNCATE statement can be used to remove data from selective columns and rows of a table.

DDELETE but not TRUNCATE statement can be used to selectively remove rows from a table.

Show Answer

Question: 4

View the Exhibit and examine the description of the EMPLOYEES table.

q4_1Z0-071

Evaluate the following SQL statement:

SELECT first_name, employee_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) ''Review'' FROM employees;

The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the firsts Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session.

Which statement is true regarding this query?

AThe query would execute to give the desired output.

BThe query would not execute because date functions cannot be nested.

CThe query would execute but the output would give review dates that are Sundays.

DThe query would not execute because the NEXT_DAY function accepts a string as argument.

Show Answer

Question: 5

View the Exhibit and examine the structure of the CUSTOMERS table.

q5_1Z0-071

CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table.

CUSTOMERS need to be updated to reflect the latest information about the customers.

What is the error in the following MERGE statement?

q5_1Z0-071

AThe CUSTOMER_ID column cannot be updated.

BThe INTO clause is misplaced in the command.

CThe WHERE clause cannot be used with INSERT.

DCUSTOMER_VU cannot be used as a data source.

Show Answer

Oracle 1Z0-071 Quiz:20 Topic:16 Questions:96-100