Oracle 1Z0-071 Exam Questions and Answers 2021

NEW QUESTION 1
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:

You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table. You execute the SQL statement:

What is the outcome?

• A. It fails because the join type used is incorrect.
• B. It executes successfully and displays the required list.
• C. It executes successfully but displays an incorrect list.
• D. It fails because the ON clause condition is not valid.

Answer: B

NEW QUESTION 2
Examine the structure of the BOOKS_TRANSACTIONS table:

Examine the SQL statement:

Which statement is true about the outcome?

• A. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
• B. It displays details for members who have borrowed before today’s date with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102.
• C. It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE.
• D. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.

Answer: D

NEW QUESTION 3
Examine the structure of the CUSTOMERS table: (Choose two.)

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. Subquery
• B. Self-join
• C. Full outer-join with self-join
• D. Left outer-join with self-join
• E. Right outer-join with self-join

Answer: AB

NEW QUESTION 4
Which two statements best describe the benefits of using the WITH clause? (Choose two.)

• A. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
• B. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
• C. It enables sessions to store a query block permanently in memory and use it to create complex queries.
• D. It enables sessions to store the results of a query permanently.

Answer: AB

NEW QUESTION 5
Examine the structure of the EMPLOYEES table. (Choose the best answer.)

You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.

• A. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALL SELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INETRSECTSELECT last_name, hire_date, salaryFROM employees WHERE hire_date > SYSDATE- 180;
• B. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);
• C. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;
• D. (SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180;

Answer: C

NEW QUESTION 6
Which two statements are true regarding roles? (Choose two.)

• A. A role can be granted to itself.
• B. A role can be granted to PUBLIC.
• C. A user can be granted only one role at any point of time.
• D. The REVOKE command can be used to remove privileges but not roles from other users.
• E. Roles are named groups of related privileges that can be granted to users or other roles.

Answer: BE

Explanation: References:
http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm#autoId28

NEW QUESTION 7
Evaluate the following statement. INSERT ALL
WHEN order_total < 10000 THEN INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders
WHEN order_total > 200000 AND order_total < 20000 THEN INTO large_orders
SELECT order_id, order_total, customer_id FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

• A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
• B. They are evaluated by the first WHEN claus
• C. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
• D. They are evaluated by the first WHEN claus
• E. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
• F. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.

Answer: A

Explanation: References:
http://psoug.org/definition/WHEN.htm

NEW QUESTION 8
Which three statements are true about the ALTER TABLE....DROP COLUMN.... command?

• A. A column can be dropped only if it does not contain any data.
• B. A column can be dropped only if another column exists in the table.
• C. A dropped column can be rolled back.
• D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
• E. A parent key column in the table cannot be dropped.

Answer: BDE

NEW QUESTION 9
Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

• A. The outer query stops evaluating the result set of the inner query when the first value is found.
• B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
• C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
• D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Answer: AC

Explanation: References:
http://www.techonthenet.com/oracle/exists.php

NEW QUESTION 10
You must display details of all users whose username contains the string 'ch_'. (Choose the best answer.) Which query generates the required output?

• A. SELECT * FROM users Where user_name LIKE '%ch_';
• B. SELECT * FROM usersWhere user_name LIKE '%ch_%'ESCAPE'%';
• C. SELECT * FROM users Where user_name LIKE 'ch_%' ESCAPE '_';
• D. SELECT * FROM users Where user_name LIKE '%ch_%' ESCAPE '';

Answer: B

NEW QUESTION 11
Evaluate the following SQL statement:
SELECT product_name || 'it's not available for order' FROM product_information
WHERE product_status = 'obsolete';
You received the following error while executing the above query: ERROR
ORA-01756: quoted string not properly terminated What would you do to execute the query successfully?

• A. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.
• B. Enclose the literal character string in the SELECT clause within the double quotation marks.
• C. Do not enclose the character literal string in the SELECT clause within the single quotation marks.
• D. Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.

Answer: A

Explanation: References:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm

NEW QUESTION 12
Examine the structure of the MARKS table:

Which two statements would execute successfully? (Choose two.)

• A. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)FROM marksWHERE subject1 > subject2;
• B. SELECT student_name subject1FROM marksWHERE subject1 > AVG(subject1);
• C. SELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;
• D. SELECT student_name,SUM(subject1)FROM marksWHERE student_name LIKE ‘R%’;

Answer: AC

NEW QUESTION 13
You must create a SALES table with these column specifications and data types: (Choose the best answer.) SALESID: Number
STOREID: Number ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified
PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified Which statement would create the table?

• A. CREATE TABLE Sales(SALESID NUMBER (4),STOREID NUMBER (4),ITEMID NUMBER (4),QTY NUMBER DEFAULT = 1,SLSDATE DATE DEFAULT SYSDATE,PAYMENT VARCHAR2(30) DEFAULT = "CASH");
• B. CREATE TABLE Sales(SALESID NUMBER (4),STOREID NUMBER (4),ITEMID NUMBER (4),QTY NUMBER DEFAULT = 1,SLSDATE DATE DEFAULT 'SYSDATE',PAYMENT VARCHAR2(30) DEFAULT CASH);
• C. CREATE TABLE Sales(SALESID NUMBER (4),STOREID NUMBER (4),ITEMID NUMBER (4),qty NUMBER DEFAULT = 1,SLSDATE DATE DEFAULT SYSDATE,PAYMENT VARCHAR2(30) DEFAULT = "CASH");
• D. Create Table sales(salesid NUMBER (4),Storeid NUMBER (4),Itemid NUMBER (4),QTY NUMBER DEFAULT 1,Slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT 'CASH');

Answer: D

NEW QUESTION 14
Which two statements are true regarding savepoints? (Choose two.)

• A. Savepoints may be used to ROLLBACK.
• B. Savepoints can be used for only DML statements.
• C. Savepoints are effective only for COMMIT.
• D. Savepoints are effective for both COMMIT and ROLLBACK.
• E. Savepoints can be used for both DML and DDL statements.

Answer: AB

NEW QUESTION 15
Which statement is true about an inner join specified in the WHERE clause of a query?

• A. It must have primary-key and foreign-key constraints defined on the columns used in the join condition.
• B. It requires the column names to be the same in all tables used for the join conditions.
• C. It is applicable for equijoin and nonequijoin conditions.
• D. It is applicable for only equijoin conditions.

Answer: C

NEW QUESTION 16
Examine the structure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute?

• A. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS
• B. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS
• C. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
• D. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS

Answer: B

NEW QUESTION 17
In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than 10 years.
Evaluate the following SQL statement:

What would be the result?

• A. It executes successfully but no rows updated.
• B. It executes successfully and updates the records of those employees who have been working in the company for more than 600 days.
• C. It gives an error because multiple NVL functions are used in an expression.
• D. It gives an error because NVL function cannot be used with UPDATE.

Answer: B

NEW QUESTION 18
Examine the structure of the ORDERS table: (Choose the best answer.)

You want to find the total value of all the orders for each year and issue this command:
SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders GROUP BY TO_CHAR(order_date, 'yyyy');
Which statement is true regarding the result?

• A. It executes successfully but does not give the correct output.
• B. It executes successfully but gives the correct output.
• C. It returns an error because the TO_CHAR function is not valid.
• D. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Answer: D