Most Recent Oracle 1Z0-071 Exam Dumps

Prepare for the Oracle Database SQL exam with our extensive collection of questions and answers. These practice Q&A are updated according to the latest syllabus, providing you with the tools needed to review and test your knowledge.

QA4Exam focus on the latest syllabus and exam objectives, our practice Q&A are designed to help you identify key topics and solidify your understanding. By focusing on the core curriculum, These Questions & Answers helps you cover all the essential topics, ensuring you're well-prepared for every section of the exam. Each question comes with a detailed explanation, offering valuable insights and helping you to learn from your mistakes. Whether you're looking to assess your progress or dive deeper into complex topics, our updated Q&A will provide the support you need to confidently approach the Oracle 1Z0-071 exam and achieve success.

The questions for 1Z0-071 were last updated on Mar 31, 2025.

Viewing page 1 out of 65 pages.
Viewing questions 1-5 out of 326 questions

Get All 326 Questions & Answers

Question No. 1

Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)?

AThere must be an equal number of columns in each SELECT list.

BThe name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.

CEach SELECT statement in the query can have an ORDER BY clause.

DNone of the set operators can be used when selecting CLOB columns.

EThe FOR UPDATE clause cannot be specified.

Show Answer

Correct Answer: A, E

A . True. When using set operators, the number and order of columns must be the same in all SELECT statements involved in the query. The data types of those columns must also be compatible.

E . True. The FOR UPDATE clause cannot be specified in a subquery or a SELECT statement that is combined with another SELECT statement by using a set operator.

B is incorrect because the column names do not need to match; only the number and data types of the columns must be compatible. C is incorrect because only the final SELECT statement in the sequence of UNION/INTERSECT/MINUS operations can have an ORDER BY clause. D is incorrect because, as of Oracle 12c, UNION ALL can be used with CLOB columns, but UNION, INTERSECT, and MINUS cannot.

Question No. 2

You execute this command:

TRUNCATE TABIE depts;

Which two are true?

AA ROLLBACK statement can be used to retrieve the deleted data.

BIt drops any triggers defined on the table.

CIt retains the indexes defined on the table.

DIt retains the integrity constraints defined on the table,

EIt always retains the space used by the removed rows.

FA FLASHBACK TABLE statement can be used to retrieve the deleted data.

Show Answer

Correct Answer: C, D

When the TRUNCATE TABLE command is used:

C . It retains the indexes defined on the table. This is true. The TRUNCATE TABLE operation does not drop the indexes on the table; it only removes all rows from the table.

D . It retains the integrity constraints defined on the table. This is correct. The TRUNCATE TABLE operation does not affect the integrity constraints; it retains them.

Options A, B, E, and F are incorrect:

A is incorrect because the TRUNCATE TABLE operation cannot be rolled back as it is a DDL operation and not a DML operation like DELETE.

B is incorrect because TRUNCATE TABLE does not drop any triggers; they remain in place.

E is incorrect as TRUNCATE TABLE may or may not release the space depending on whether the REUSE STORAGE clause is used.

F is incorrect because FLASHBACK TABLE cannot be used to retrieve data after a TRUNCATE TABLE operation since it does not generate redo logs that FLASHBACK relies upon.

Question No. 3

Which two statements are true about the SET VERIFY ON command?

AIt displays values for variables created by the DEFINE command.

BIt can be used in SQL Developer and SQL*Plus.

CIt can be used only in SQL*plus.

DIt displays values for variables prefixed with &&.

EIt displays values for variables used only in the WHERE clause of a query.

Show Answer

Correct Answer: A, B

The SET VERIFY ON command is related to how SQL*Plus and SQL Developer display information about substitution variables:

A . It displays values for variables created by the DEFINE command: When VERIFY is set to ON, SQL*Plus and SQL Developer will display the old and new values of a substitution variable when it is redefined using the DEFINE command or when a new value is provided for it during the session.

B . It can be used in SQL Developer and SQL*Plus: While traditionally associated with SQL*Plus, the SET VERIFY command is also supported in SQL Developer, allowing you to control the display of substitution variable values in both environments.

Oracle SQL*Plus User's Guide and Reference, especially the section on the SET command and substitution variables.

Question No. 4

Examine the description of the transactions table:

Which two SQL statements execute successfully?

ASELECT customer_id AS 'CUSTOMER-ID', transaction_date AS DATE, amount+100 'DUES' from transactions;

BSELECT customer_id AS 'CUSTOMER-ID',transaction_date AS DATE, amount+100 'DUES' from transactions;

CSELECT customer_id CUSTID, transaction_date TRANS_DATEamount+100 DUES FROM transactions;

DSELECT customer_id AS 'CUSTOMER-ID', transaction_date AS 'DATE', amount+100 DUES FROM transactions;

ESELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 'DUES AMOUNT' FROM transactions;

Show Answer

Correct Answer: A, C

A . True, this statement will execute successfully. In Oracle SQL, column aliases can be given in double quotes which allows the use of special characters like a hyphen. Additionally, you can perform arithmetic operations such as amount+100 directly in the SELECT clause to return increased values of amount.

C . True, this statement is also valid. It does not use any special characters or reserved keywords as aliases that would require double quotes. The arithmetic operation is the same as in option A, which is permissible.

For B, D, and E, the reasons for failure are: B. In Oracle SQL, single quotes are used for string literals, not for column aliases. Therefore, using single quotes around 'CUSTOMER-ID' and 'DUES' will result in an error. D. The word 'DATE' is a reserved keyword in Oracle SQL. When used as a column alias without double quotes, it will lead to an error. In this statement, although 'DATE' is in double quotes, it's a best practice to avoid using reserved keywords as aliases. E. There are syntax errors in the statement. 'customer id' should be 'customer_id', and the alias 'CUSTOMER-ID' should be enclosed in double quotes if special characters are used. Also, 'DUES AMOUNT' as an alias should be in double quotes to be valid because it contains a space.

Oracle documentation on column aliases: Oracle Database SQL Language Reference

Oracle reserved words: Oracle Database SQL Language Reserved Words

Question No. 5

Examine the data in the NEW_EMPLOYEES table:

Examine the data in the EMPLOYEES table:

You want to:

1. Update existing employee details in the EMPLOYEES table with data from the NEW EMPLOYEES

table.

2. Add new employee detail from the NEW_ EMPLOYEES able to the EMPLOYEES table.

Which statement will do this:

AMERGE INTO employees e
USING new employees ne
WHERE e.employee_id = ne.employee_ id
WHEN MATCHED THEN
UPDATE SET e.name = ne.name, e.job_id = ne.job_id,e.salary =ne. salary
WHEN NOT MATCHED THEN
INSERT VALUES (ne. employee_id,ne.name, ne.job_id,ne.salary) ;

BMERGE INTO employees e
USING new_employees n
ON (e.employee_id = ne.employee_id)
WHEN MATCHED THEN
UPDATE SET e.name = ne.name, e.job id = ne.job_id,e.salary =ne. salary
WHEN NOT MATCHED THEN
INSERT VALUES (ne. employee_id,ne.name,ne.job_id,ne.salary);

CMERGE INTO employees e
USING new employees ne
ON (e.employee_id = ne.employee_id)
WHEN FOUND THEN
UPDATE SET e.name =ne.name, e.job_id=ne.job_id, e.salary =ne.salary
WHEN NOT FOUND THEN
INSERT VALUES (ne.employee_id,ne.name,ne.job_id,ne.salary) ;

DMERGE INTO employees e
USING new_employees n
WHERE e.employee_id = ne.employee_id
WHEN FOUND THEN
UPDATE SET e.name=ne.name,e.job_id =ne.job_id, e.salary=ne.salary
WHEN NOT FOUND THEN
INSERT VALUES (ne.employee_ id,ne.name,ne.job id,ne.salary) ;

Show Answer

Correct Answer: B

The correct answer to perform the specified update and insert operations is to use the MERGE statement, which is designed to facilitate such 'upsert' operations (update or insert).

A . The syntax is incorrect; the WHERE clause is not used with MERGE.

B . This is the correct syntax and use of the MERGE statement. It uses the ON clause to specify the join condition, and it provides actions for WHEN MATCHED and WHEN NOT MATCHED situations.

C . The keywords FOUND and NOT FOUND are not valid in the context of the MERGE statement in Oracle SQL.

D . Similar to option A, the syntax is incorrect because MERGE uses an ON clause, not a WHERE clause, and the keywords FOUND and NOT FOUND are incorrect.

The correct syntax of a MERGE statement includes defining a condition using the ON clause to determine how rows from the source and target tables are matched, then specifying the actions for when rows match (WHEN MATCHED) and when they do not match (WHEN NOT MATCHED).

Oracle Documentation on MERGE: https://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606

Unlock All Questions for Oracle 1Z0-071 Exam

Full Exam Access, Actual Exam Questions, Validated Answers, Anytime Anywhere, No Download Limits, No Practice Limits

Get All 326 Questions & Answers