Oracle 1Z0-071 Practice Questions with Explanations

Correct answer: C

MONTHS_BETWEEN(SYSDATE, start_date) returns the number of months between the current date and the START_DATE. When START_DATE is within the last 25 months, this returns a positive value ≤ 25. Option C correctly orders the arguments with SYSDATE first (current date) and start_date second (the date column being evaluated). Options A, B, and D use incorrect syntax or logic that would not properly filter rows within the last 25 months.

Why the other options are wrong:

• A. TO_NUMBER cannot directly convert a date difference to months; syntax is invalid.
• B. MONTHS_BETWEEN(start_date, SYSDATE) returns a negative value for recent dates, requiring <= -25 instead.
• D. ADD_MONTHS(start_date, 25) returns a future date; comparing to SYSDATE filters incorrectly.

Correct answer: C

The correct syntax specifies GRANT UPDATE with column names in parentheses: UPDATE (title, address). The WITH GRANT OPTION clause (not WITH ADMIN OPTION) allows the grantee to pass these permissions to other users. Option C uses the proper syntax: GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION. All other options use incorrect syntax, wrong option keywords, or both.

Why the other options are wrong:

• A. This syntax cannot specify individual columns; it attempts invalid dot notation.
• B. This grants permissions but lacks WITH GRANT OPTION needed to allow ANDREW to grant to others.
• D. WITH ADMIN OPTION applies to roles and system privileges, not object privileges.
• E. The syntax is invalid; column names must be in parentheses after UPDATE, not using dot notation.
• F. WITH ADMIN OPTION is for system and role privileges, not object-level column privileges.

Correct answer: C

MOD(25,3) returns 1. Both ROUND(1,-1) and TRUNC(1,-1) are valid operations on this result. The -1 parameter rounds/truncates to the nearest 10, and since 1 is less than 10, both functions return 0. Both expressions are valid and produce the same result.

Why the other options are wrong:

• A. TRUNC(MOD(25,3),-1) is valid; TRUNC accepts numeric input and a negative scale parameter.
• B. ROUND(MOD(25,3),-1) is valid; ROUND accepts numeric input and a negative scale parameter.
• D. Both functions produce the same result (0) when given the same numeric input and scale parameter.

Correct answer: C

SELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '5' DAY - INTERVAL '120' SECOND, 'DD-MON- YYYY') AS "date" FROM DUAL; Starting from 29-10-2019 (October 29, 2019), adding 2 months gives 29-12-2019, adding 5 days gives 03-01-2020 (January 3, 2020), and subtracting 120 seconds (less than a day) gives 02-01-2020 (January 2, 2020), which displays as 02-JAN-2020 in DD-MON-YYYY format. Other options use incorrect month increments or second values that produce different dates.

Why the other options are wrong:

• A. Adding 4 days instead of 5 results in 01-01-2020 before subtracting seconds.
• B. Adding 3 months and 7 days moves to January 5, 2020, and subtracting 360 seconds still leaves January 5.
• D. Subtracting 86410 seconds removes more than one full day, resulting in January 1, 2020.
• E. Adding 6 days instead of 5 results in 03-01-2020, which minus 120 seconds gives 02- 01-2020 but the month arithmetic is off by one day.

Correct answer: D

Option D is correct because NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') properly combines the functions: ADD_MONTHS adds 6 months to the hire date, and NEXT_DAY finds the next Monday after that date. The string 'MONDAY' is the correct syntax for NEXT_DAY when NLS_TERRITORY is AMERICA.

Why the other options are wrong:

• A. NEXT_DAY requires a date and a day name or number as arguments; passing only a string 'MONDAY' without a date is incorrect syntax.
• B. Using 1 as the second argument to NEXT_DAY represents Sunday (first day of the week in AMERICA), not Monday.
• C. MONTHS_BETWEEN returns a number (difference in months), not a date; NEXT_DAY requires a date as its first argument.

Correct answer: D

The error occurs because the query uses an alias name 'AverageSalary' in the ORDER BY clause, but that alias references an aggregate function AVG(sal). In SQL, when using GROUP BY, you cannot reference column aliases in the GROUP BY clause itself. Additionally, many SQL implementations have restrictions on using aliases in ORDER BY when aggregate functions are involved with GROUP BY. However, the primary issue here is that the alias 'AverageSalary' cannot be used in the GROUP BY clause - the GROUP BY clause must reference the actual column name 'deptno', not the alias. The query attempts to group by the Department alias, which violates SQL syntax rules requiring actual column names or expressions in GROUP BY, not their aliases.

Why the other options are wrong:

• A. Alias names can contain spaces when enclosed in quotes, and this alias doesn't use spaces anyway.
• B. Alias names do not always need to be in quotes; they are quoted here but quotes are optional for valid identifiers without spaces.
• C. Column aliases can be used in ORDER BY clauses in standard SQL; this is a common and valid practice.

Correct answer: B

In Oracle SQL, when a table name is a reserved word or consists of numbers only, it must be enclosed in double quotes. The table name '123' is numeric and therefore invalid without quoting. Double quotes preserve the exact case and allow special naming. Single quotes are for string literals, not identifiers. The TABLE() function is for nested tables, not regular table references.

Why the other options are wrong:

• A. TABLE() is a function for nested table columns, not standard table selection.
• C. Escaped single quotes represent a string literal containing '123', not a table reference.
• D. Unquoted single quotes are for string literals; the query would fail because '123' is treated as a string value, not a table name.

Correct answer: A

The statement contains a syntax error that will cause execution to fail. The first SELECT clause defines two columns (id and first_name with aliases), while the second SELECT clause only defines one column (the literal 1 and string 'John' aliased as 'name'). UNION requires both queries to have the same number of columns. Additionally, the column structure mismatch makes this invalid SQL. The database will return an error rather than executing successfully.

Why the other options are wrong:

• B. The query will not execute successfully to return any rows due to the structural mismatch in the UNION.
• C. An error occurs before any result set is generated, so no rows are returned.
• D. The query fails during parsing/validation, not reaching execution to return a single row.

Correct answer: D

The INTERSECT operator returns only rows that appear in both queries. The first query selects (1, 'John', NULL) and the second query selects (1, 'John', NULL). Since both queries return identical rows, INTERSECT will return that one matching row. Therefore, the result is 1 row.

Why the other options are wrong:

• A. The syntax is valid SQL; no error will occur during execution.
• B. INTERSECT returns only rows common to both queries, not the sum of both result sets; there is only 1 matching row, not 2.
• C. Both queries produce the identical row (1, 'John', NULL), so the intersection is not empty.

Correct answer: A

The output shows that for each channel_type and month combination, there are rows with specific country codes (GB, US) plus rows with NULL country codes that represent subtotals. This is characteristic of ROLLUP behavior, which generates subtotals at different levels of grouping. The ROLLUP(t.month, co.country_code) clause creates subtotals first by month (with country_code as NULL), then an overall total (both month and country_code as NULL), while ch.channel_type remains as the primary grouping dimension without rollup. This matches the pattern in the output where Internet 2000-09 has rows for GB, US, and NULL country codes, followed by similar patterns for other month/channel combinations.

Why the other options are wrong:

• B. This would apply ROLLUP only to country_code, producing subtotals only at the country_code level, not at the month level as shown in the output.
• C. CUBE generates all possible combinations of grouping levels, which would produce far more rows than shown in the output; it's overly comprehensive for this result set.
• D. A simple GROUP BY without ROLLUP would not generate the NULL placeholder rows that represent subtotals, matching only specific country/month/channel combinations without aggregation levels.

Correct answer: C

Option C is correct because it uses IS NOT NULL to properly check for non-null first names (NULL cannot be compared with <> or =) and DISTINCT * returns distinct employee rows. The syntax is valid: SELECT DISTINCT * is proper SQL, and WHERE first_name IS NOT NULL correctly filters rows where first_name has a value. This combination returns all distinct employees that have non-null first names.

Why the other options are wrong:

• A. Uses <> NULL which never evaluates to true in SQL; NULL comparisons require IS NULL or IS NOT NULL, not inequality operators.
• B. Uses incorrect syntax with DISTINCT placed between columns rather than at the start; SELECT first_name, DISTINCT last_name is invalid SQL.
• D. Uses <> NULL which never evaluates to true in SQL; the WHERE clause will filter no rows since NULL <> NULL is unknown, not true.

Correct answer: C

The query performs a CROSS JOIN between the emp and dept tables with a WHERE clause filtering for job = 'MANAGER' and deptno IN (10, 20). A CROSS JOIN produces a Cartesian product of all rows from both tables before the WHERE clause is applied. From the emp table, there are 3 managers: CLARK (deptno 10), JONES (deptno 20), and BLAKE (deptno 30). The WHERE clause filters to only those managers in departments 10 or 20, which gives 2 employees (CLARK and JONES). The dept table has 4 rows total, but the WHERE clause also filters dept.deptno IN (10, 20), resulting in 2 matching department rows. Therefore, 2 employees × 2 departments = 4 rows would be expected from a standard join. However, examining the filtered result more carefully: CLARK (dept 10) matches with deptno 10 department (1 row), JONES (dept 20) matches with deptno 20 department (1 row), and BLAKE is excluded. This produces 3 rows total when the CROSS JOIN is evaluated with both filter conditions applied.

Why the other options are wrong:

• A. 64 would result from a full cross join of all 16 employees with all 4 departments without filtering, which is not what this query performs.
• B. 6 incorrectly assumes either 3 managers times 2 departments or some other calculation that doesn't account for the deptno IN filter properly.
• D. 12 would result from 3 managers times 4 departments, ignoring the deptno IN (10, 20) restriction on the dept table.

Correct answer: B

CURRENT_TIMESTAMP returns the current date and time from the user session with the data type TIMESTAMP WITH TIME ZONE, including time zone information. SYSDATE returns only DATE data type. LOCALTIMESTAMP returns TIMESTAMP WITH LOCAL TIME ZONE (session time zone). CURRENT_DATE returns only the date portion without time or time zone information.

Why the other options are wrong:

• A. SYSDATE returns a DATE data type, not TIMESTAMP WITH TIME ZONE.
• C. LOCALTIMESTAMP returns TIMESTAMP WITH LOCAL TIME ZONE, not TIMESTAMP WITH TIME ZONE.
• D. CURRENT_DATE returns only the date without time or time zone information.

Correct answer: C

INTERVAL DAY TO SECOND is the correct choice because it stores time durations in a format that supports date arithmetic without conversion functions, can represent loan periods spanning multiple years through day calculations, and enables direct calculation of interest based on the number of days. INTERVAL YEAR TO MONTH could store 10 years but cannot calculate interest by days without conversion. TIMESTAMP variants store point-in-time values, not durations, and do not naturally support arithmetic for day-based calculations without conversion functions.

Why the other options are wrong:

• A. INTERVAL YEAR TO MONTH stores duration but cannot directly calculate interest based on specific days.
• B. TIMESTAMP WITH TIMEZONE stores a point in time, not a duration, and requires conversion for date arithmetic.
• D. TIMESTAMP WITH LOCAL TIMEZONE stores a point in time, not a duration, and requires conversion for date arithmetic.
• E. TIMESTAMP stores a point in time, not a duration, and does not support direct date arithmetic for interest calculations.

Correct answer: B

The query executes successfully and returns the first Monday of the next month. LAST_DAY(SYSDATE) returns the last day of the current month. NEXT_DAY(last_day, 'MON') returns the next Monday after the last day of the current month, which is always the first Monday of the next month. TO_CHAR then formats this date as 'dd "Monday for" fmMonth rrrr', displaying the day, the word "Monday for", the full month name, and the four-digit year.

Correct answer: B

In compound queries using SET operators like INTERSECT, the default sorting is by the first column in the first SELECT statement. This is the standard behavior across SQL implementations for maintaining consistent ordering of combined result sets when no explicit ORDER BY is specified.

Why the other options are wrong:

• A. The sort is not limited to VARCHAR2 columns; all data types are sorted based on column position.
• C. The sort is not limited to NUMBER columns; column data type does not determine default sort behavior.
• D. The default sort is based on the first SELECT, not the last SELECT in the compound query.
• E. While the first column is correct, it must be from the first SELECT statement, not the last.

Correct answer: B

The query performs a CROSS JOIN between the bricks table (aliased as b1) and itself (aliased as b2), then filters with WHERE b1.weight < b2.weight. This means for each row in b1, it finds all rows in b2 where b2.weight is greater than b1.weight. The table has 4 rows with weights 5, 10, 15, and 20. For b1.weight=5: matches b2 weights 10,15,20 (3 rows); for b1.weight=10: matches b2 weights 15,20 (2 rows); for b1.weight=15: matches b2 weight 20 (1 row); for b1.weight=20: no matches (0 rows). Total: 3+2+1+0 = 6 rows.

Why the other options are wrong:

• A. Counting only the base table rows rather than the cross join result set.
• C. Incorrectly calculating a full cross join (4×4=16) without applying the WHERE condition.
• D. The WHERE clause does find matching pairs since weights are distinct and allow valid comparisons.
• E. Only one match pair would occur if the condition were much more restrictive, but multiple pairs satisfy weight inequality.
• F. This count doesn't match any logical derivation from the table dimensions and filter condition.

Correct answer: D

The query creates an INTERVAL YEAR TO MONTH value of 100 months. When displayed, Oracle converts this to the standard interval format of years-months: 100 months equals 8 years and 4 months, displayed as +08-04.

Why the other options are wrong:

• A. The syntax is valid; no error occurs.
• B. The interval value is not displayed as a simple +100; it's normalized to year-month format.
• C. The display includes both years and months; it's not just +08.

Correct answer: B

ROUND(156.00, -2) rounds to the nearest hundred, resulting in 200. Then TRUNC(200, -1) truncates to the nearest ten, which is 200 (since 200 already ends in 0).

Why the other options are wrong:

• A. This would be the result if TRUNC alone was applied to 156, but ROUND is executed first.
• C. This incorrectly assumes TRUNC truncates 156 without first rounding.
• D. This results from rounding 156 to 200 then incorrectly truncating.
• E. This is not a valid result of either function on these values.

Correct answer: D

The query uses GROUP BY 1 (grouping by the first column, deptno) and HAVING SUM(sal) > 3000, but critically, the WHERE clause is missing. Without a WHERE clause to filter employees with sal > 3000 before aggregation, the HAVING clause filters groups based on the total salary of all employees in each department. However, the main issue is that GROUP BY 1 refers to the first column in the SELECT list (deptno AS "departments"), but this violates standard SQL syntax-you cannot use positional numbers in GROUP BY that refer to aliases or expressions; GROUP BY 1 would reference the underlying column deptno, not the alias. More fundamentally, in many SQL implementations (particularly Oracle), this query structure with the positional reference combined with the HAVING clause without proper column references causes a syntax error. The query will fail to execute.

Why the other options are wrong:

• A. This would be a valid result interpretation if the query executed successfully, but the query contains a syntax error that prevents execution.
• B. Similar to option A, this describes a plausible result, but the query syntax is invalid and will not execute. Additionally, there is no ORDER BY clause in the query to order results.
• C. This misrepresents what the query does; the WHERE clause does not exist to filter employees before aggregation, and the query will error before producing any result.