# Free 1Z0-071 practice questions

> 10 free 1Z0-071 practice questions with the correct answer and an explanation.

Page: https://devopscodelab.com/exams/oracle-1z0-071/free-practice-questions/
Practice exams: https://devopscodelab.com/exams/oracle-1z0-071/

### Question 1 of 10 (choose 2)

A character literal in a SELECT statement must contain apostrophes, as in the text O'Neil's account. Which TWO techniques produce such a literal in Oracle SQL? (Choose two.)

- A. Write each apostrophe inside the literal as two consecutive single quotation marks
- B. Precede each apostrophe with a backslash escape character inside the literal
- C. Define an ESCAPE character for the statement before the literal is used
- D. Enclose the whole literal in double quotation marks instead of single ones
- E. Use the q quote operator with a delimiter pair, such as q'[...]', around the text

**Answer:** A, E

**Explanation:** The alternative quoting mechanism q'[...]' (or another delimiter pair) lets apostrophes appear freely in the text, and a doubled single quotation mark inside a normal literal is read as one apostrophe. Oracle does not treat backslash as an escape character in literals. Double quotation marks delimit identifiers rather than strings, and the ESCAPE clause applies only to wildcard characters in a LIKE condition.

### Question 2 of 10

A SQL*Plus user runs DEFINE job = 'IT_PROG' and then executes a query containing WHERE job_id = '&job'. Which statement is true?

- A. The query prompts for a value and offers IT_PROG as the suggested default
- B. The query uses IT_PROG without prompting until the user runs UNDEFINE job
- C. The variable is available only in the next statement and then is removed
- D. The query compares JOB_ID with the literal text &job, ignoring the DEFINE

**Answer:** B

**Explanation:** DEFINE creates a substitution variable with a value, so references to &job are replaced with IT_PROG without a prompt for as long as the variable stays defined, which is until UNDEFINE or the end of the session. SQL*Plus does not offer defaults in prompts. Substitution happens before the statement is sent, so &job is not treated as literal text, and the variable is not limited to one statement.

### Question 3 of 10

What does MONTHS_BETWEEN(DATE '2026-03-31', DATE '2026-02-28') return?

- A. 0.9677
- B. 1.0333
- C. 1.0968
- D. 1

**Answer:** D

**Explanation:** When both dates are the last days of their months, MONTHS_BETWEEN returns a whole number, so the result is exactly 1. The fractional values come from treating the day difference as a fraction of a 31-day month, which Oracle does not do in this case. The value 1.0333 would add the three-day gap between the 28th and the 31st as a fraction, which the last-day rule also prevents.

### Question 4 of 10

A query compares a DATE column with WHERE hire_date > '01-JAN-20'. It works on one client but fails or returns wrong rows on another. What is the most reliable fix?

- A. Compare with TO_CHAR(hire_date) > '01-JAN-20' so both sides use character data
- B. Compare with TO_DATE('2020-01-01', 'YYYY-MM-DD') or the literal DATE '2020-01-01'
- C. Compare with TO_NUMBER('20200101') because numbers do not depend on NLS settings
- D. Compare with CAST('01-JAN-20' AS DATE) so that the conversion becomes explicit

**Answer:** B

**Explanation:** An implicit or format-less conversion depends on the session's NLS_DATE_FORMAT, so an explicit format model or an ANSI date literal makes the comparison independent of client settings. Comparing strings compares characters, not dates, and gives wrong results. CAST without a format still relies on NLS_DATE_FORMAT, and comparing a date with a number raises an inconsistent data type error.

### Question 5 of 10

Which statement about the following query is true?
SELECT AVG(salary) FROM employees GROUP BY department_id;

- A. It fails, because every column named in GROUP BY must also appear in the select list
- B. It is valid and returns a single row with the average salary of all employees
- C. It fails, because AVG cannot be combined with a GROUP BY clause on its own
- D. It is valid and returns one average per department without showing the department

**Answer:** D

**Explanation:** Columns in the GROUP BY clause do not have to be selected, so the query returns one row per department, although the output is hard to interpret without DEPARTMENT_ID. The rule applies in the other direction: non-aggregated selected columns must be in GROUP BY. A single overall average requires removing GROUP BY, and AVG is commonly used with GROUP BY.

### Question 6 of 10

Which ANSI join returns the same rows as this Oracle-syntax query?
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

- A. SELECT e.last_name, d.department_name FROM employees e FULL JOIN departments d ON e.department_id = d.department_id;
- B. SELECT e.last_name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;
- C. SELECT e.last_name, d.department_name FROM departments d LEFT JOIN employees e ON e.department_id = d.department_id;
- D. SELECT e.last_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;

**Answer:** D

**Explanation:** The (+) operator is placed on the side that may be missing, so d.department_id(+) means DEPARTMENTS is the deficient table and all EMPLOYEES rows are kept, which is a left join from EMPLOYEES. Both the right join from EMPLOYEES and the left join starting from DEPARTMENTS keep unmatched departments instead. A full join keeps unmatched rows from both tables, which the original query does not.

### Question 7 of 10

A report must show the three highest-paid employees. Which statement returns the correct rows in Oracle Database?

- A. SELECT * FROM (SELECT last_name, salary FROM employees WHERE ROWNUM <= 3) ORDER BY salary DESC;
- B. SELECT last_name, salary FROM employees WHERE ROWNUM <= 3 ORDER BY salary DESC;
- C. SELECT * FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 3;
- D. SELECT last_name, salary FROM employees WHERE ROWNUM BETWEEN 1 AND 3 ORDER BY salary;

**Answer:** C

**Explanation:** ROWNUM is assigned as rows are fetched, before ORDER BY is applied, so the sort must happen in an inline view and ROWNUM is applied to the sorted result. Filtering on ROWNUM in the same query block takes three arbitrary rows and then sorts them, and putting ROWNUM inside the inline view has the same problem. Sorting ascending would return the lowest salaries even if the rows were chosen correctly.

### Question 8 of 10

Query 1 selects EMPLOYEE_ID, LAST_NAME and HIRE_DATE from CURRENT_STAFF. Query 2 selects only EMPLOYEE_ID and LAST_NAME from CONTRACTORS. How can the two results be combined with UNION ALL?

- A. Run them as is, because UNION ALL pads the shorter query with NULLs for missing columns
- B. Add TO_DATE(NULL) as a third column to query 2 so that both queries return three columns
- C. Replace UNION ALL with UNION, because UNION accepts queries with different column counts
- D. Add ORDER BY 3 to the compound query so that Oracle aligns the third column by position

**Answer:** B

**Explanation:** Every query in a compound query must return the same number of columns with compatible types, so a placeholder such as TO_DATE(NULL), or plain NULL, is added to the shorter query. No set operator pads missing columns automatically, so the original statement fails with ORA-01789. ORDER BY does not change the column count, and UNION has the same requirement as UNION ALL.

### Question 9 of 10

Employee 113 must receive the same JOB_ID and SALARY as employee 205. Which statement does this in a single UPDATE?

- A. UPDATE employees SET (job_id, salary) = (SELECT job_id, salary FROM employees WHERE employee_id = 205) WHERE employee_id = 113;
- B. UPDATE employees SET (job_id, salary) = (SELECT job_id, salary FROM employees) WHERE employee_id = 113;
- C. UPDATE employees SET (job_id, salary) = (SELECT job_id, salary FROM employees WHERE employee_id = 113) WHERE employee_id = 205;
- D. UPDATE employees SET job_id = (SELECT job_id, salary FROM employees WHERE employee_id = 205) WHERE employee_id = 113;

**Answer:** A

**Explanation:** A multiple-column subquery in the SET clause assigns both columns at once when the column list is in parentheses and the subquery returns one row. Assigning a two-column subquery to the single column JOB_ID fails with a too-many-values error. Swapping the employee IDs copies the values in the wrong direction, and a subquery without a filter returns many rows and raises a single-row subquery error.

### Question 10 of 10

A foreign key on ORDERS was disabled for a bulk load, and some loaded rows now violate it. The team wants the constraint enforced for all future changes without fixing the existing rows yet. Which statement achieves this?

- A. ALTER TABLE orders ENABLE CONSTRAINT ord_cust_fk;
- B. ALTER TABLE orders ENABLE NOVALIDATE CONSTRAINT ord_cust_fk;
- C. ALTER TABLE orders ENABLE VALIDATE CONSTRAINT ord_cust_fk;
- D. ALTER TABLE orders MODIFY CONSTRAINT ord_cust_fk RELY DISABLE;

**Answer:** B

**Explanation:** ENABLE NOVALIDATE checks every new or changed row while skipping the check of existing data, so the violating rows remain for now. ENABLE alone defaults to VALIDATE, so both of those statements check existing rows and fail. RELY DISABLE leaves the constraint disabled and only informs the optimizer, so new violations are still possible.

Ready for a timed run? Take the free 15-question 1Z0-071 mini exam: https://devopscodelab.com/app/exams/oracle-1z0-071/mini/

DevOpsCode Lab is not affiliated with Oracle. 1Z0-071 and related names are trademarks of their owners. We write every question ourselves; these are not real exam questions.
