Oracle 1Z0-071 Database SQL Practice Exam

A retail analytics team needs unique city values from a CUSTOMERS table for members whose signup date is within the last 90 days, sorted alphabetically. Which single SELECT returns the correct result set?

1. SELECT DISTINCT city FROM customers WHERE signup_date >= SYSDATE - 90 ORDER BY city;
2. SELECT UNIQUE city FROM customers WHERE signup_date BETWEEN SYSDATE AND SYSDATE - 90;
3. SELECT city FROM customers WHERE signup_date > SYSDATE - 90 ORDER BY city DISTINCT;
4. SELECT city FROM customers WHERE signup_date >= SYSDATE - 90 GROUP BY city ORDER BY 1 DESC;

An engineer runs an UPDATE in SQL Developer, then closes the session without COMMIT. Another session immediately queries the same rows. What does the second session see, and what happens to the uncommitted changes?

1. The second session sees the new values immediately, because Oracle uses dirty reads by default.
2. The second session sees the old values, and the uncommitted changes are rolled back when the first session ends.
3. The second session sees the new values only if it sets ISOLATION LEVEL READ UNCOMMITTED.
4. The second session is blocked until the first session commits or rolls back.

A data engineer needs every subscriber together with their most recent payment, including subscribers who have never paid (free-trial users). SUBSCRIBERS and PAYMENTS join on subscriber_id. Which join keeps one row per subscriber, even with no matching payment?

1. subscribers s RIGHT OUTER JOIN payments p ON s.subscriber_id = p.subscriber_id
2. subscribers s INNER 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 WHERE s.subscriber_id = p.subscriber_id

An analyst needs the number of distinct, non-NULL provider specialties per hospital. PROVIDERS has hospital_id and specialty. Which expression is correct?

1. SELECT hospital_id, COUNT(*) FROM providers WHERE specialty IS NULL GROUP BY hospital_id;
2. SELECT hospital_id, COUNT(specialty) FROM providers GROUP BY hospital_id;
3. SELECT hospital_id, DISTINCT COUNT(specialty) FROM providers GROUP BY hospital_id;
4. SELECT hospital_id, COUNT(DISTINCT specialty) FROM providers GROUP BY hospital_id;

An app inserts three rows into AUDIT_LOG, then a fourth INSERT raises ORA-02290 (check-constraint violation). The developer wants to revert only the failed statement without losing the earlier inserts in the same transaction. Which approach fits?

1. Use a SAVEPOINT before each INSERT and ROLLBACK TO SAVEPOINT on failure.
2. Issue ROLLBACK immediately after the exception.
3. Run COMMIT after each INSERT so failures cannot roll back earlier rows.
4. Wrap the INSERT in SET TRANSACTION READ ONLY before executing.

An HR analyst needs employees who earn more than the average salary within their own department. EMPLOYEES has employee_id, department_id, and salary. Which query is correct?

1. SELECT employee_id FROM employees GROUP BY department_id HAVING salary > AVG(salary);
2. SELECT employee_id FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);
3. SELECT employee_id FROM employees WHERE salary > AVG(salary);
4. SELECT employee_id FROM employees e1 JOIN employees e2 ON e1.salary > AVG(e2.salary);

A DATE column SHIP_DATE must be output as the exact text string ‘YYYY-MM-DD’ for an external partner. Which expression produces it?

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

A new ORDERS table’s ORDER_STATUS column must always contain one of a fixed list: ‘PENDING’, ‘SHIPPED’, ‘DELIVERED’, or ‘CANCELLED’. Which constraint enforces this at the table level?

1. A PRIMARY KEY constraint on ORDER_STATUS.
2. A UNIQUE constraint on ORDER_STATUS.
3. A FOREIGN KEY constraint referencing ORDER_STATUS in another table.
4. A CHECK constraint listing the four allowed values.

An engineer needs course IDs that exist in LEGACY_CATALOG but not in the current CATALOG. Both have the same course_id type. Which set operator returns the difference?

1. SELECT course_id FROM legacy_catalog MINUS SELECT course_id FROM catalog;
2. SELECT course_id FROM catalog MINUS SELECT course_id FROM legacy_catalog;
3. SELECT course_id FROM legacy_catalog UNION SELECT course_id FROM catalog;
4. SELECT course_id FROM legacy_catalog INTERSECT SELECT course_id FROM catalog;

An analyst wants a SQL*Plus query to prompt the user for a country code at runtime and substitute it into the WHERE clause each time the script runs. Which construct does this?

1. A sequence: WHERE country_code = country_seq.NEXTVAL
2. DBMS_OUTPUT.PUT_LINE to request input.
3. A substitution variable: WHERE country_code = '&country_code'
4. A bind variable: WHERE country_code = :country_code