Sign in
Home/Oracle/Oracle Database SQL (1Z0-071)/Free questions

Oracle Database SQL (1Z0-071) — Free Practice Questions

10 free sample questions from a bank of 364, with the correct answers and explanations. No signup required — start practising right now.

1Examine 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.)
Oracle Database SQL (1Z0-071) question 1
  • SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
  • SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
  • SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
  • SELECT promo_category DISTINCT promo_cost, FROM promotions ORDER BY 2;
  • SELECT promo_cost, promo_category FROM promotions ORDER BY 1;
Answer: A, C

The short version

AC — AC shows distinct category-cost combinations correctly. DISTINCT applies once after SELECT to the whole row expression. Option A concatenates promo_category, a literal separator, and promo_cost into one string, then DISTINCT collapses duplicate combined values, so each category-cost pair appears once even though the literal appears in every row.

Key concepts in this question

  • DISTINCT scope: DISTINCT operates on the entire select list that follows it, not on a single column, so A distinct on an expression and C distinct on two columns both yield unique combinations.
  • Expression distinctness: Concatenating columns with a constant still yields distinct rows when the underlying column values differ, because the whole concatenated string is compared.
  • ORDER BY position: ORDER BY 1 refers to the first select item and does not change the distinctness logic, it only sorts the already deduplicated result.

Why AC is correct

Option A concatenates promo_category, a literal separator, and promo_cost into one string, then DISTINCT collapses duplicate combined values, so each category-cost pair appears once even though the literal appears in every row.

Why the others are wrong

  • B. uses the DISTINCT keyword twice in one SELECT list, which is a syntax error because DISTINCT may appear only once immediately after SELECT.
  • D. misplaces DISTINCT after a column name and leaves a trailing comma, so the statement fails to parse and never executes.
  • E. omits DISTINCT entirely, so duplicate category-cost pairs from PROMOTIONS would repeat and the requirement for unique values is not met.

1z0-071 exam tip

Remember for the exam that DISTINCT appears once right after SELECT and covers every column or expression that follows it.

2The 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?
  • WHERE TO_NUMBER(start_date - SYSDATE) <= 25
  • WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25
  • WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25
  • WHERE ADD_MONTHS(start_date, 25) <= SYSDATE
Answer: C

The short version

C — C correctly measures rows within the last 25 months. MONTHS_BETWEEN with SYSDATE first returns positive elapsed months. MONTHS_BETWEEN takes the later date first and returns a fractional month count equal to first minus second, so MONTHS_BETWEEN with SYSDATE followed by start_date yields a positive age in months that can be compared to 25.

Key concepts in this question

  • MONTHS_BETWEEN order: MONTHS_BETWEEN of SYSDATE and start_date is positive when start_date is in the past, while the reversed order would be negative and would invert the filter.
  • DATE subtraction: Subtracting two DATE values returns days, not months, so comparing that difference to 25 would test days and miss the intended month window.
  • ADD_MONTHS logic: ADD_MONTHS of start_date plus 25 compared to SYSDATE with less-or-equal selects old rows, not recent ones, because recent dates plus 25 months lie in the future.

Why C is correct

MONTHS_BETWEEN takes the later date first and returns a fractional month count equal to first minus second, so MONTHS_BETWEEN with SYSDATE followed by start_date yields a positive age in months that can be compared to 25.

Why the others are wrong

  • A. subtracts dates to get days and converts with TO_NUMBER, so the comparison to 25 tests days rather than months and returns the wrong set.
  • B. reverses the MONTHS_BETWEEN arguments to start_date then SYSDATE, producing negative month values that satisfy less-or-equal for all old rows including those far older than 25 months.
  • D. adds 25 months to start_date then keeps rows where that future date is still before now, which selects rows older than 25 months instead of rows within the last 25 months.

1z0-071 exam tip

For date windows always put SYSDATE first in MONTHS_BETWEEN when you want age in months, then compare with less-or-equal.

3Examine the BRICKS table: You write this query: How many rows will the query return?
Oracle Database SQL (1Z0-071) question 3Oracle Database SQL (1Z0-071) question 3
  • 4
  • 6
  • 16
  • 0
  • 1
  • 10
Answer: B

The short version

B — B counts 6 ordered brick pairs with increasing weight. self CROSS JOIN of 4 ordered weights filtered by less-than yields 6 pairs. The query self joins the 4-row BRICKS table, so the CROSS JOIN first produces 4 times 4 equals 16 candidate pairs including self pairs and both orderings.

Key concepts in this question

  • CROSS JOIN cardinality: A CROSS JOIN without an ON clause pairs every row of the first copy with every row of the second copy, so 4 rows self joined gives 16 intermediate rows.
  • Strict inequality filter: Keeping only rows where b1 weight is less than b2 weight removes self matches and mirrored duplicates, leaving only strictly increasing pairs.
  • Combination count: For n distinct ordered values the count of increasing pairs is n choose 2, which for n equal 4 is 6, a quick check that confirms the result without enumerating.

Why B is correct

The query self joins the 4-row BRICKS table, so the CROSS JOIN first produces 4 times 4 equals 16 candidate pairs including self pairs and both orderings.

Why the others are wrong

  • A. reports 4, which is just the base table size and ignores that a self join multiplies rows before filtering.
  • C. reports 16, which is the unfiltered CROSS JOIN size and ignores the WHERE clause that removes equal and descending pairs.
  • D. reports 0, which would require no increasing pairs, contradicting the presence of distinct increasing weights.
  • E. reports 1, which would imply only one increasing pair, far below the six combinations of four distinct weights.
  • F. reports 10, which has no combinatorial basis for 4 rows under a strict less-than filter.

1z0-071 exam tip

When you see self CROSS JOIN plus less-than, immediately compute n choose 2 for the row count.

4Examine this query: SELECT INTERVAL ‘100’ MONTH DURATION FROM DUAL; What will be the output?
  • an error
  • DURATION - ---------- +100
  • DURATION - ---------- +08
  • DURATION - ---------- +08-04
Answer: D

The short version

D — D shows the year-month interval normalization correctly. 100 months normalizes to 8 years 4 months displayed as plus 08-04. The literal INTERVAL with 100 and MONTH creates a YEAR TO MONTH interval of 100 months, and Oracle normalizes year-month intervals so that months beyond 11 roll into years.

Key concepts in this question

  • INTERVAL MONTH literal: INTERVAL with a numeric string and MONTH unit builds a year-month duration, distinct from day-second intervals that use days and time.
  • Normalization: Oracle stores year-month intervals canonically with months 0 to 11, carrying every 12 months into the year component, so 100 becomes 8 dash 04.
  • Display format: Year-month intervals display as plus YY-MM with leading zeros, which explains the plus sign and dash in the expected output.

Why D is correct

The literal INTERVAL with 100 and MONTH creates a YEAR TO MONTH interval of 100 months, and Oracle normalizes year-month intervals so that months beyond 11 roll into years. Dividing 100 by 12 gives 8 full years with remainder 4 months, formatted as plus 08-04 with two-digit year and month parts. The column alias DURATION only labels the output, it does not change the value, so the settled key follows the documented interval normalization and display format.

Why the others are wrong

  • A. claims an error, but the syntax with quoted number, MONTH keyword, and alias is valid and executes on DUAL.
  • B. shows plus 100 as if months were never normalized to years, which contradicts year-month normalization.
  • C. shows plus 08 only, dropping the 4-month remainder and misrepresenting 100 as exactly 96 months.

1z0-071 exam tip

Divide month intervals by 12 in your head to get years plus remainder for any INTERVAL MONTH question.

5Examine this query: SELECT TRUNC(ROUND(156.00,-2),-1) FROM DUAL; What is the result?
  • 150
  • 200
  • 160
  • 100
  • 16
Answer: B

The short version

B — B traces nested ROUND then TRUNC correctly. ROUND to hundreds gives 200 then TRUNC to tens keeps 200. The inner ROUND with 156 and minus 2 rounds to the nearest hundred, and 156 is closer to 200 than to 100, so the inner result is 200.

Key concepts in this question

  • ROUND negative precision: Negative second argument rounds left of the decimal point, so minus 2 means hundreds and minus 1 means tens.
  • TRUNC behaviour: TRUNC cuts off digits without rounding up, so TRUNC of an already round number like 200 leaves it unchanged.
  • Nesting order: Inner functions evaluate first, so ROUND determines the value before TRUNC can preserve or cut it, making order decisive.

Why B is correct

The inner ROUND with 156 and minus 2 rounds to the nearest hundred, and 156 is closer to 200 than to 100, so the inner result is 200. The outer TRUNC with minus 1 truncates toward zero at the tens place without rounding, and 200 truncated to tens remains 200. Nesting therefore collapses to 200, so the settled key follows place-value rounding then truncation order.

Why the others are wrong

  • A. gives 150, which confuses tens rounding with hundreds rounding and ignores that ROUND to hundreds of 156 must jump to 200.
  • C. gives 160, which is ROUND to tens only and skips the required hundreds step plus the outer truncation.
  • D. gives 100, which rounds 156 down as if by floor rather than nearest-hundred logic.
  • E. gives 16, which drops a zero and misplaces the magnitude by a factor of ten.

1z0-071 exam tip

Work nested numeric functions inside-out and write the intermediate value down before applying the outer call.

6Examine this SQL statement: Which two are true? (Choose two.)
Oracle Database SQL (1Z0-071) question 6
  • The DELETE statement executes successfully even if the subquery selects multiple rows.
  • The subquery is executed before the DELETE statement is executed.
  • The subquery is not a correlated subquery.
  • All existing rows in the EMPLOYEES table are deleted.
  • The subquery is executed for every row in the EMPLOYEES table.
Answer: A, E

The short version

AE — DELETE with EXISTS allows many rows and checks each employee row. An EXISTS correlated DELETE filters EMPLOYEES by whether a matching inner row exists for that outer row. EXISTS tests presence rather than equality, so many inner rows never raise a single-row error. The inner query references the outer employee key, forcing per-row evaluation.

Key concepts in this question

  • EXISTS logic: Returns true when at least one inner row exists, so two or many rows still yield true without error, unlike scalar equality.
  • Correlation: Referencing the outer employee identifier makes the subquery correlated, so it re-evaluates for every candidate outer row.
  • DELETE filtering: Only outer rows whose EXISTS check is true are deleted, so the statement removes a subset unless all rows match.
  • Timing: The outer row is read first, then the correlated check runs with that row key, not once before the statement.

Why AE is correct

EXISTS never fails for returning many rows because it asks only whether at least one row exists, and the optimizer may use a semi-join with identical semantics. The tested subquery correlates on the outer employee key, so by definition it executes once per outer row with that key value. Those two facts entail the settled pair and rule out once-only or uncorrelated readings.

Why the others are wrong

  • B. claims one execution before DELETE, which describes an uncorrelated scalar subquery, not per-row correlated EXISTS.
  • C. claims uncorrelated, contradicting the outer-column reference that defines correlation.
  • D. claims all rows deleted, which requires EXISTS true for every row, a result the filter does not guarantee.

1z0-071 exam tip

With EXISTS in 1z0-071, remember multi-row is fine and correlation means per-row evaluation.

7Which three statements are true about indexes and their administration in an Oracle database? (Choose three.)
  • The same table column can be part of a unique and non-unique index.
  • A descending index is a type of function-based index.
  • An INVINSIBLE index is not maintained when DML is performed on its underlying table.
  • If a query filters on an indexed column then it will always be used during execution of the query.
  • An index can be created as part of a CREATE TABLE statement.
  • An UNUSABLE index is maintained when DML is performed on its underlying table.
Answer: A, B, E

The short version

ABE — ABE states three documented index administration truths. coexisting unique indexes plus descending function index plus CREATE TABLE index. Oracle allows the same column to participate in both a unique and a non-unique index because they are separate segments with separate constraints, so A holds.

Key concepts in this question

  • Index independence: Unique and non-unique indexes are separate objects that can share columns, the unique one enforces a constraint while the other merely speeds access.
  • Descending implementation: Descending sort order is stored as a function transformation, which is why DBA views flag descending indexes alongside function-based ones.
  • Table creation side effects: Constraint clauses at table creation time implicitly build indexes, so administration must account for indexes that were never issued via CREATE INDEX.

Why ABE is correct

Oracle allows the same column to participate in both a unique and a non-unique index because they are separate segments with separate constraints, so A holds. A descending index is implemented internally as a function-based index on the reversed sort key, so B holds under Oracle data dictionary rules. CREATE TABLE can include PRIMARY KEY, UNIQUE, or index-organizing clauses that create supporting indexes automatically, so E holds, and together they form the settled triple.

Why the others are wrong

  • C. claims an invisible index is not maintained, but invisible indexes are maintained on DML and only skipped by the optimizer unless made visible.
  • D. claims the optimizer always uses an index when a filter exists, but cost, selectivity, and statistics often make a full scan cheaper.
  • F. claims an unusable index is maintained, but unusable segments skip DML maintenance and require a rebuild before use.

1z0-071 exam tip

Link invisible with maintained but ignored and unusable with not maintained for fast index questions.

8Examine this description of the EMP table: You execute this query: What is the result?
Oracle Database SQL (1Z0-071) question 8Oracle Database SQL (1Z0-071) question 8
  • only departments where the total salary is greater than 3000, returned in no particular order
  • only departments where the total salary is greater than 3000, ordered by department
  • all departments and a sum of the salaries of employees with a salary greater than 3000
  • an error
Answer: D

The short version

D — D identifies the grouping syntax error correctly. GROUP BY with positional numbers in this context raises an error. The examined EMP query groups by select-list positions rather than by column names or valid expressions, which the tested Oracle grouping rule rejects in this statement shape, so execution fails instead of returning grouped sums.

Key concepts in this question

  • GROUP BY validation: GROUP BY must reference grouping columns or expressions the engine can resolve, and positional misuse in the tested form is rejected.
  • Aggregation dependency: SUM over salary depends on successful grouping, so a grouping error suppresses all result rows rather than returning partial totals.
  • Error versus empty set: A syntax or validation failure returns an error, which is distinct from a query that runs but filters everything out.

Why D is correct

The examined EMP query groups by select-list positions rather than by column names or valid expressions, which the tested Oracle grouping rule rejects in this statement shape, so execution fails instead of returning grouped sums. Because grouping never succeeds, no ordered or unordered department totals are produced, and the only accurate description is an error. The settled key therefore follows the strict GROUP BY validation behaviour cited in the vendor note.

Why the others are wrong

  • A. describes filtered department totals with no order, which would require successful grouping plus a HAVING clause, neither of which survives the error.
  • B. adds ordering by department, which cannot occur when the statement fails before the sort phase.
  • C. describes ungrouped totals with a salary filter, which confuses WHERE row filtering with HAVING group filtering and ignores the failure.

1z0-071 exam tip

If GROUP BY looks positional or mismatched in 1Z0-071, lean error before imagining result rows.

9Which two are true about virtual columns? (Choose two.)
  • They can be indexed.
  • They can be referenced in the column expression of another virtual column.
  • They cannot have a data type explicitly specified.
  • They can be referenced in the set clause of an update statement as the name of the column to be updated.
  • They can be referenced in the where clause of an update or delete statement.
Answer: A, E

The short version

AE — AE captures indexable and filterable virtual columns. virtual columns support indexes and WHERE predicates but not direct updates. Virtual columns are derived expressions stored only as metadata, and Oracle permits B-tree and function-based indexes built on that expression, so A holds.

Key concepts in this question

  • Virtual derivation: A virtual column value is computed from other columns at query time, so it behaves like a stored column for reads without occupying segment space.
  • Index support: Indexing the defining expression materializes access paths, letting the optimizer seek on virtual values just like regular columns.
  • Write restriction: Because no bytes are stored for the virtual value, SET clause assignment is rejected while WHERE filtering remains legal.

Why AE is correct

Virtual columns are derived expressions stored only as metadata, and Oracle permits B-tree and function-based indexes built on that expression, so A holds. The same derived value can be evaluated during row filtering, so predicates in WHERE of UPDATE or DELETE can reference it, making E hold. Direct assignment in SET is forbidden because there is no stored value to overwrite, and chaining virtual expressions has restrictions, which is why only A and E form the settled pair.

Why the others are wrong

  • B. allows a virtual expression to reference another virtual column, which Oracle restricts to avoid chained derivation complexity in the tested scope.
  • C. claims no explicit data type is allowed, but Oracle does allow an explicit type consistent with the expression result.
  • D. allows naming the virtual column in SET, which fails because derived values cannot be directly assigned.

1z0-071 exam tip

Memorize virtual as readable and indexable but not writable, like a view column inside a table.

10A session's NLS_DATE_FORMAT is set to DD Mon YYYY. Which two queries return the value 1 Jan 2019? (Choose two.)
  • SELECT TO_DATE('2019-01-01') FROM DUAL;
  • SELECT DATE '2019-01-01' FROM DUAL;
  • SELECT '2019-01-01' FROM DUAL;
  • SELECT TO_DATE('2019-01-01', 'YYYY-MM-DD') FROM DUAL;
  • SELECT TO_CHAR('2019-01-01') FROM DUAL;
Answer: B, D

The short version

BD — BD returns 1 Jan 2019 regardless of NLS settings. DATE literal plus explicit TO_DATE mask are NLS independent. The DATE literal with DATE followed by ISO year-month-day is interpreted by the engine without consulting NLS_DATE_FORMAT, so B reliably builds 1 Jan 2019.

Key concepts in this question

  • DATE literal: The ANSI DATE literal is parsed fixed as year-month-day, giving portable date construction across sessions with different NLS settings.
  • Explicit format mask: Supplying the matching mask to TO_DATE overrides NLS inference, so year-month-day strings convert correctly every time.
  • NLS dependence: Omitting the mask forces Oracle to parse with NLS_DATE_FORMAT, which here expects day month year and would misinterpret or reject year-first strings.

Why BD is correct

The DATE literal with DATE followed by ISO year-month-day is interpreted by the engine without consulting NLS_DATE_FORMAT, so B reliably builds 1 Jan 2019. TO_DATE with an explicit format mask of year-month-day likewise parses the string deterministically no matter what NLS_DATE_FORMAT the session uses, so D also returns the same date. Display then follows the session format of day month year, showing 1 Jan 2019, which makes B and D the settled pair.

Why the others are wrong

  • A. calls TO_DATE without a mask on a year-first string, so parsing follows DD Mon YYYY and fails or misreads instead of yielding 1 Jan 2019.
  • C. selects a plain string literal without any date conversion, so the result is character data, not a DATE displayed as 1 Jan 2019.
  • E. applies TO_CHAR to a string rather than a date, producing text handling, not a DATE value for 1 Jan 2019.

1z0-071 exam tip

For date safety in Oracle pick DATE literal or TO_DATE with an explicit mask, never bare string reliance.

Want the full bank of 364 questions for Oracle Database SQL (1Z0-071)? See all practice exams.