1Z0-071

Free 1Z0-071 practice questions

10 questions from our 1Z0-071 practice tests, each with the correct answer and a written explanation. Read them with the answers shown, or switch to quiz mode and answer first.

Back to the 1Z0-071 practice exams

Question 1 of 10, Retrieving Data Using the SQL SELECT Statement. 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.)

  1. AWrite each apostrophe inside the literal as two consecutive single quotation marks Correct answer
  2. BPrecede each apostrophe with a backslash escape character inside the literal
  3. CDefine an ESCAPE character for the statement before the literal is used
  4. DEnclose the whole literal in double quotation marks instead of single ones
  5. EUse the q quote operator with a delimiter pair, such as q'[...]', around the text Correct answer

Answer and 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, Restricting and Sorting Data

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

  1. AThe query prompts for a value and offers IT_PROG as the suggested default
  2. BThe query uses IT_PROG without prompting until the user runs UNDEFINE job Correct answer
  3. CThe variable is available only in the next statement and then is removed
  4. DThe query compares JOB_ID with the literal text &job, ignoring the DEFINE

Answer and 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, Using Single-Row Functions to Customize Output

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

  1. A0.9677
  2. B1.0333
  3. C1.0968
  4. D1 Correct answer

Answer and 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, Using Conversion Functions and Conditional Expressions

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?

  1. ACompare with TO_CHAR(hire_date) > '01-JAN-20' so both sides use character data
  2. BCompare with TO_DATE('2020-01-01', 'YYYY-MM-DD') or the literal DATE '2020-01-01' Correct answer
  3. CCompare with TO_NUMBER('20200101') because numbers do not depend on NLS settings
  4. DCompare with CAST('01-JAN-20' AS DATE) so that the conversion becomes explicit

Answer and 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, Reporting Aggregated Data Using Group Functions

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

  1. AIt fails, because every column named in GROUP BY must also appear in the select list
  2. BIt is valid and returns a single row with the average salary of all employees
  3. CIt fails, because AVG cannot be combined with a GROUP BY clause on its own
  4. DIt is valid and returns one average per department without showing the department Correct answer

Answer and 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, Displaying Data from Multiple Tables

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(+);

  1. ASELECT e.last_name, d.department_name FROM employees e FULL JOIN departments d ON e.department_id = d.department_id;
  2. BSELECT e.last_name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;
  3. CSELECT e.last_name, d.department_name FROM departments d LEFT JOIN employees e ON e.department_id = d.department_id;
  4. DSELECT e.last_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id; Correct answer

Answer and 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, Using Subqueries to Solve Queries

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

  1. ASELECT * FROM (SELECT last_name, salary FROM employees WHERE ROWNUM <= 3) ORDER BY salary DESC;
  2. BSELECT last_name, salary FROM employees WHERE ROWNUM <= 3 ORDER BY salary DESC;
  3. CSELECT * FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 3; Correct answer
  4. DSELECT last_name, salary FROM employees WHERE ROWNUM BETWEEN 1 AND 3 ORDER BY salary;

Answer and 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, Using SET Operators

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?

  1. ARun them as is, because UNION ALL pads the shorter query with NULLs for missing columns
  2. BAdd TO_DATE(NULL) as a third column to query 2 so that both queries return three columns Correct answer
  3. CReplace UNION ALL with UNION, because UNION accepts queries with different column counts
  4. DAdd ORDER BY 3 to the compound query so that Oracle aligns the third column by position

Answer and 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, Managing Tables Using DML Statements

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

  1. AUPDATE employees SET (job_id, salary) = (SELECT job_id, salary FROM employees WHERE employee_id = 205) WHERE employee_id = 113; Correct answer
  2. BUPDATE employees SET (job_id, salary) = (SELECT job_id, salary FROM employees) WHERE employee_id = 113;
  3. CUPDATE employees SET (job_id, salary) = (SELECT job_id, salary FROM employees WHERE employee_id = 113) WHERE employee_id = 205;
  4. DUPDATE employees SET job_id = (SELECT job_id, salary FROM employees WHERE employee_id = 205) WHERE employee_id = 113;

Answer and 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, Using DDL to Manage Tables and Their Relationships

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?

  1. AALTER TABLE orders ENABLE CONSTRAINT ord_cust_fk;
  2. BALTER TABLE orders ENABLE NOVALIDATE CONSTRAINT ord_cust_fk; Correct answer
  3. CALTER TABLE orders ENABLE VALIDATE CONSTRAINT ord_cust_fk;
  4. DALTER TABLE orders MODIFY CONSTRAINT ord_cust_fk RELY DISABLE;

Answer and 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.