Free Oracle 1Z0-071 practice questions

10 free Oracle 1Z0-071 practice questions with the correct answer and a full explanation for each, taken from the CertStash pack of 272 questions. Work through them, then open each answer to check your reasoning.

Get all 272 questions (US$39) · Download these 10 as a PDF

Question 1

Examine the description of the PROMOTIONS table:

You want to display the unique promotion costs in each promotion category.

Which two queries can be used? (Choose two.)

Exhibit for question 1

  1. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
  2. SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
  3. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
  4. SELECT promo_category DISTINCT promo_cost, FROM promotions ORDER BY 2;
  5. SELECT promo_cost, promo_category FROM promotions ORDER BY 1;
Show answer and explanation

Correct answer: A, C

A. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1; C. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1; Option A uses DISTINCT correctly at the beginning of the SELECT clause to eliminate duplicate combinations of promo_category and promo_cost, concatenating them with string literals for display. This properly shows unique promotion costs within each category. Option C also uses DISTINCT correctly to select and display both promo_category and promo_cost as separate columns, showing the unique combinations of these two fields. Both queries return the distinct relationships between promotion categories and their costs, which answers the requirement to display unique promotion costs in each category.

Why the other options are wrong

  • B. The DISTINCT keyword cannot be used in the middle of a concatenation expression; it must appear immediately after SELECT and before all column names.
  • D. The syntax is invalid; DISTINCT is placed incorrectly after the column name rather than after SELECT, and the comma placement is wrong.
  • E. This query lacks the DISTINCT keyword, so it will return all rows without eliminating duplicates, failing to show only the unique combinations of promotion costs and categories.

Question 2

Examine the description of the PRODUCTS table:

Which three queries use valid expressions? (Choose three.)

Exhibit for question 2

  1. SELECT product_id, unit_price, S "Discount", unit_price + surcharge – discount FROM products;
  2. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
  3. SELECT product_id, (expiry_date – delivery_date) * 2 FROM products;
  4. SELECT product_id, unit_price || 5 "Discount", unit_price + surcharge – discount FROM products;
  5. SELECT product_id, expiry_date * 2 FROM products;
  6. SELECT product_id, unit_price, unit_price + surcharge FROM products; ✅Correct Answer: B, C, F
  7. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
  8. SELECT product_id, (expiry_date – delivery_date) * 2 FROM products;
  9. SELECT product_id, unit_price, unit_price + surcharge FROM products; Valid expressions must reference real columns and use operators the data types allow. B performs multiplication and division on numeric values, which is allowed. C subtracts one DATE from another to get a number of days and multiplies that result by 2, which is allowed. F adds UNIT_PRICE to SURCHARGE; SURCHARGE is VARCHAR2, but Oracle implicitly converts a numeric string to a number, so the expression is valid. The other three fail on column and data type rules: DISCOUNT is not a column of PRODUCTS, a column alias such as "Discount" cannot be reused elsewhere in the same SELECT list, S is not a column either, and a DATE value cannot be an operand of multiplication.
Show answer and explanation

The answer and explanation for this question are in the free sample PDF.

Question 3

What is true about non-equijoin statement performance? (Choose two.)

  1. The BETWEEN condition always performs less well than using the >= and <= conditions.
  2. The BETWEEN condition always performs better than using the >= and <= conditions.
  3. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
  4. Table aliases can improve performance.
  5. The join syntax used makes no difference to performance.
Show answer and explanation

Correct answer: D, E

D. Table aliases can improve performance. E. The join syntax used makes no difference to performance. Table aliases can improve performance by reducing parsing overhead and making queries more efficient. The join syntax used (Oracle vs ANSI) makes no difference to performance; the Oracle optimizer produces equivalent execution plans regardless of syntax style. BETWEEN and >= <= conditions perform equivalently when properly optimized.

Why the other options are wrong

  • A. BETWEEN and >= <= conditions perform identically when optimized by the Oracle engine.
  • B. BETWEEN does not inherently perform better than separate >= and <= conditions.
  • C. Oracle syntax and ANSI SQL:1999 syntax produce identical performance after optimization.

Question 4

Which two are true? (Choose two.)

  1. ADD_MONTHS adds a number of calendar months to a date.
  2. CEIL requires an argument which is a numeric data type.
  3. CEIL returns the largest integer less than or equal to a specified number.
  4. LAST_DAY returns the date of the last day of the current month only.
  5. LAST_DAY returns the date of the last day of the month for the date argument passed to the function.
  6. LAST_DAY returns the date of the last day of the previous month only.
Show answer and explanation

Correct answer: A, E

A. ADD_MONTHS adds a number of calendar months to a date. E. LAST_DAY returns the date of the last day of the month for the date argument passed to the function. ADD_MONTHS adds a number of calendar months to a date value. LAST_DAY returns the last day of the month for whatever date argument is passed to it, so it works for any date, not only the current month. CEIL returns the smallest integer greater than or equal to the value supplied, and it accepts any value Oracle can implicitly convert to a number, so a numeric data type is not strictly required.

Why the other options are wrong

  • B. CEIL also accepts values implicitly converted to a number, such as '7.3', so a numeric type is not required.
  • C. CEIL returns the smallest integer greater than or equal to the number; that describes FLOOR.
  • D. LAST_DAY uses the date supplied to it, so it is not limited to the current month.
  • F. LAST_DAY returns the last day of the month of the date passed, never the previous month.

Question 5

Which three statements are true about Oracle synonyms? (Choose three.)

  1. A synonym cannot be created for a PL/SQL package.
  2. A synonym can be available to all users.
  3. A SEQUENCE can have a synonym.
  4. Any user can drop a PUBLIC synonym.
  5. A synonym created by one user can refer to an object belonging to another user.
Show answer and explanation

Correct answer: B, C, E

B. A synonym can be available to all users. C. A SEQUENCE can have a synonym. E. A synonym created by one user can refer to an object belonging to another user. A PUBLIC synonym is available to all users in the database. A synonym can be created for a sequence, giving it a simpler name, and can point to an object in another schema, which simplifies cross-schema access. Synonyms can also be created for PL/SQL packages, procedures and functions. Dropping a public synonym requires the DROP PUBLIC SYNONYM privilege, so an ordinary user without it cannot drop one.

Why the other options are wrong

  • A. Synonyms can also be created for PL/SQL packages, procedures and functions.
  • D. Dropping one needs the DROP PUBLIC SYNONYM privilege, which most users lack.

Question 6

Which two are true? (Choose two.)

  1. CONCAT joins two character strings together.
  2. CONCAT joins two or more character strings together.
  3. FLOOR returns the largest positive integer less than or equal to a specified number.
  4. INSTR finds the offset within a character string, starting from position 0.
  5. INSTR finds the offset within a string of a single character only.
  6. FLOOR returns the largest integer less than or equal to a specified number.
Show answer and explanation

Correct answer: A, F

A. CONCAT joins two character strings together. F. FLOOR returns the largest integer less than or equal to a specified number. CONCAT joins exactly two character strings together (not more than two). FLOOR returns the largest integer less than or equal to a specified number, working with both positive and negative values. INSTR finds the offset within a character string, starting from position 1 (not 0), and it can find any substring, not just single characters.

Why the other options are wrong

  • B. CONCAT in Oracle joins exactly two strings, not two or more.
  • C. FLOOR is not limited to positive integers; it returns the largest integer ≤ the number for any value.
  • D. INSTR starts counting from position 1, not position 0.
  • E. INSTR can find any substring, not just single characters.

Question 7

Examine these SQL statements which execute successfully:

Which two statements are true after execution? (Choose two.)

Exhibit for question 7

  1. The primary key constraint will be enabled and IMMEDIATE.
  2. The foreign key constraint will be enabled and DEFERRED.
  3. The primary key constraint will be enabled and DEFERRED.
  4. The foreign key constraint will be disabled.
  5. The foreign key constraint will be enabled and IMMEDIATE.
Show answer and explanation

Correct answer: A, D

A. The primary key constraint will be enabled and IMMEDIATE. D. The foreign key constraint will be disabled. After the CREATE TABLE statement, the primary key constraint (emp_emp_no_pk) is created and enabled by default as IMMEDIATE. The subsequent ALTER TABLE statements first add a foreign key constraint (emp_mgr_fk) which is enabled and IMMEDIATE by default, then disable the primary key constraint with DISABLE CONSTRAINT, and finally enable it again with ENABLE CONSTRAINT. However, when a constraint is re-enabled without specifying DEFERRED, it defaults to IMMEDIATE. The foreign key constraint remains disabled after the second ALTER TABLE statement disables the primary key constraint CASCADE (which disables dependent constraints). Therefore, the primary key constraint is enabled and IMMEDIATE (A is true), and the foreign key constraint is disabled (D is true).

Why the other options are wrong

  • B. The foreign key constraint is disabled, not enabled and deferred.
  • C. The primary key constraint is enabled as IMMEDIATE, not DEFERRED, since no DEFERRED clause was specified in the ENABLE statement.
  • E. The foreign key constraint is disabled by the CASCADE option in the DISABLE CONSTRAINT statement, not enabled.

Question 8

Examine this SQL statement:

Which two are true? (Choose two.)

Exhibit for question 8

  1. All existing rows in the ORDERS table are updated.
  2. The subquery is executed before the UPDATE statement is executed.
  3. The subquery is not a correlated subquery.
  4. The subquery is executed for every updated row in the ORDERS table.
  5. The UPDATE statement executes successfully even if the subquery selects multiple rows. ✅Correct Answer: A, D
  6. All existing rows in the ORDERS table are updated.
  7. The subquery is executed for every updated row in the ORDERS table. The UPDATE statement has no WHERE clause, so every existing row in the ORDERS table is updated with the value returned by the subquery. The subquery references o.customer_id from the outer statement, which makes it a correlated subquery: Oracle must evaluate it once for every row being updated, using that row's customer_id to fetch the matching customer's last name. Because the value depends on the current outer row, the subquery cannot be evaluated ahead of the UPDATE, and it must return at most one row for each row updated.
Show answer and explanation

The answer and explanation for this question are in the free sample PDF.

Question 9

Which two statements are true about TRUNCATE and DELETE? (Choose two.)

  1. DELETE can use a WHERE clause to determine which row(s) should be removed.
  2. TRUNCATE can use a WHERE clause to determine which row(s) should be removed.
  3. TRUNCATE leaves any indexes on the table in an UNUSABLE state.
  4. The result of a TRUNCATE can be undone by issuing a ROLLBACK.
  5. The result of a DELETE can be undone by issuing a ROLLBACK.
Show answer and explanation

Correct answer: A, E

A. DELETE can use a WHERE clause to determine which row(s) should be removed. E. The result of a DELETE can be undone by issuing a ROLLBACK. DELETE can use a WHERE clause to selectively remove specific rows based on conditions. The result of a DELETE statement can be undone by issuing a ROLLBACK because DELETE is a DML operation that generates undo information. TRUNCATE does not support a WHERE clause; it removes all rows. TRUNCATE generates a DDL operation that cannot be rolled back and does not leave indexes in an UNUSABLE state.

Why the other options are wrong

  • B. TRUNCATE cannot use a WHERE clause; it removes all rows unconditionally.
  • C. TRUNCATE does not leave indexes in an UNUSABLE state.
  • D. TRUNCATE is a DDL operation and cannot be undone with ROLLBACK.

Question 10

The STORES table has a column START_DATE of data type DATE, containing the date the row was inserted.

You only want to display details of rows where START_DATE is within the last 25 months.

Which WHERE clause can be used?

  1. WHERE TO_NUMBER(start_date – SYSDATE) <= 25
  2. WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25
  3. WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25
  4. WHERE ADD_MONTHS(start_date, 25) <= SYSDATE ✅Correct Answer: C, WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25 MONTHS_BETWEEN(SYSDATE, start_date) returns the number of months from START_DATE up to the current date, which is a positive value for rows inserted in the past. Restricting that result to 25 or less keeps only the rows whose START_DATE falls within the last 25 months, so the argument order shown, with SYSDATE first and the date column second, produces exactly the filter the requirement describes.
Show answer and explanation

Answer and explanation for question 10

That was 10 of 272.

The full Oracle 1Z0-071 pack has all 272 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.

Get the full pack