Oracle 1Z0-071 Database SQL Practice Exam

A payroll report runs SELECT last_name, salary*12 + salary*12*commission_pct AS annual_pay FROM employees; For some employees, annual_pay comes back empty.

Why is annual_pay NULL for those employees?

1. AS annual_pay is a reserved alias, so Oracle suppresses the value.
2. Oracle rounds very small products down to NULL.
3. commission_pct is NULL for them, and any arithmetic expression containing a NULL evaluates to NULL.
4. Arithmetic in a SELECT list requires the concatenation operator.

A manager wants the three highest salaries in the EMPLOYEES table, but if other employees tie with the third-highest salary, they must appear too.

Which clause returns that result?

1. ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY
2. ORDER BY salary DESC FETCH FIRST 3 ROWS WITH TIES
3. FETCH FIRST 3 ROWS WITH TIES with no ORDER BY clause
4. WHERE ROWNUM <= 3 ORDER BY salary DESC

A DATE column SHIP_DATE must be sent to a partner as the exact text YYYY-MM-DD, whatever the session’s NLS_DATE_FORMAT happens to be.

Which expression guarantees that output?

1. FORMAT(ship_date, 'YYYY-MM-DD')
2. CAST(ship_date AS VARCHAR2(10))
3. TO_DATE(ship_date, 'YYYY-MM-DD')
4. TO_CHAR(ship_date, 'YYYY-MM-DD')

An analyst needs each department_id whose average salary is above 8000.

Which query is valid and correct?

1. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 8000;
2. SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id;
3. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id WHERE AVG(salary) > 8000;
4. SELECT department_id, salary FROM employees GROUP BY department_id HAVING AVG(salary) > 8000;

A report must list every subscriber with their payments, including free-trial subscribers who have never paid. SUBSCRIBERS and PAYMENTS join on subscriber_id.

Which FROM clause keeps subscribers with no matching payment?

1. subscribers s INNER JOIN payments p ON s.subscriber_id = p.subscriber_id
2. subscribers s RIGHT OUTER JOIN payments p ON s.subscriber_id = p.subscriber_id
3. subscribers s LEFT OUTER JOIN payments p ON s.subscriber_id = p.subscriber_id
4. subscribers s CROSS JOIN payments p filtered with WHERE s.subscriber_id = p.subscriber_id

To find employees who manage nobody, a developer runs SELECT last_name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees); The CEO’s row has a NULL manager_id.

What does the query return?

1. Every employee who is not a manager, as intended.
2. No rows, because the subquery result contains a NULL.
3. Only the CEO, because NULL matches nothing.
4. An error, because a multiple-row subquery cannot follow NOT IN.

A nightly job combines order rows from ORDERS_2025 and ORDERS_2026 into one list. Duplicate rows must be kept, and the job should avoid unnecessary sorting.

Which set operator fits?

1. UNION
2. INTERSECT
3. MINUS
4. UNION ALL

In one session, a developer runs an UPDATE on PRICES, then CREATE TABLE prices_bkp AS SELECT * FROM prices; and then ROLLBACK.

What happens to the UPDATE?

1. It is rolled back, because ROLLBACK undoes the whole session.
2. It is rolled back, but prices_bkp keeps the updated values.
3. It is permanent, because Oracle issued an implicit COMMIT around the CREATE TABLE.
4. The ROLLBACK fails because a DDL statement is pending.

EMPLOYEES.department_id references DEPARTMENTS. The business rule: when a department row is deleted, its employee rows must be deleted automatically.

Which constraint definition on EMPLOYEES enforces that rule?

1. FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE
2. FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE SET NULL
3. FOREIGN KEY (department_id) REFERENCES departments (department_id) with no ON DELETE clause
4. CHECK (department_id IN (SELECT department_id FROM departments))

A nightly load must synchronize PRICES from STAGE_PRICES: update the price where the product already exists and insert a row where it does not, in a single statement.

Which statement fits?

1. A multi-table INSERT ALL into PRICES
2. MERGE INTO prices p USING stage_prices s ON (p.product_id = s.product_id) WHEN MATCHED THEN UPDATE SET p.price = s.price WHEN NOT MATCHED THEN INSERT (product_id, price) VALUES (s.product_id, s.price)
3. An UPDATE of PRICES with a correlated subquery against STAGE_PRICES
4. CREATE TABLE prices AS SELECT * FROM stage_prices