ORACLE 1Z0-071 Exam | Practice Questions And Answers 2026/2027 | Complete Questions And Answers (Verified Answers) | Exam Prep | Comprehensive Exam Guide

ORACLE 1Z0-071 Exam | Practice Questions And
Answers 2026/2027 | Complete Questions And Answers
(Verified Answers) | Exam Prep | Comprehensive Exam
Guide
1. Which SQL statement correctly retrieves all columns from the EMPLOYEES table for
employees whose salary is greater than 5000?

A. SELECT * FROM employees WHERE salary > 5000;
B. SELECT employees FROM * WHERE salary > 5000;
C. SELECT * FROM employees HAVING salary > 5000;
D. SELECT ALL employees WHERE salary > 5000;

Answer: A

The WHERE clause filters individual rows before the result set is returned. Therefore, salary >
5000 correctly restricts the EMPLOYEES rows while SELECT * returns all columns.

2. A developer needs to display employee last names in uppercase without changing the
stored data. Which expression should be used?

A. UPPERCASE(last_name)
B. CAPITALIZE(last_name)
C. UPPER(last_name)
D. TOUPPER(last_name)

Answer: C

UPPER converts character data to uppercase in the query result without modifying the
underlying column value.

3. Which clause is used to remove duplicate rows from a query result?

A. UNIQUE
B. DISTINCT
C. DIFFERENT
D. REMOVE DUPLICATES

Answer: B

DISTINCT eliminates duplicate combinations of selected column values from the query result.

4. Which statement correctly retrieves employees hired during 2025?

,A. SELECT * FROM employees WHERE hire_date BETWEEN '01-JAN-25' AND '31-DEC-
25';
B. SELECT * FROM employees WHERE hire_date IN 2025;
C. SELECT * FROM employees WHERE hire_date = 2025;
D. SELECT * FROM employees WHERE YEAR(hire_date) = 2025;

Answer: A

BETWEEN can define an inclusive date range. In Oracle SQL, date literals should preferably be
represented explicitly with DATE literals or appropriate conversion functions in production
code.

5. An analyst wants to display employees ordered from the highest salary to the lowest.
Which clause should be used?

A. GROUP BY salary DESC
B. SORT BY salary DESC
C. ORDER BY salary DESC
D. ORDER salary HIGH

Answer: C

ORDER BY controls the presentation order of query results, and DESC sorts numeric values
from highest to lowest.

6. Which Oracle function returns the first non-null expression from its arguments?

A. NVL
B. COALESCE
C. NULLIF
D. DECODE

Answer: B

COALESCE evaluates its expressions from left to right and returns the first non-null value. It
can also evaluate more than two expressions, unlike NVL.

7. An employee table contains a COMMISSION_PCT column that may be NULL. Which
expression displays 0 whenever COMMISSION_PCT is NULL?

A. NULLIF(commission_pct, 0)
B. COALESCE(commission_pct, 0)
C. DECODE(commission_pct, NULL, NULL)
D. commission_pct + NULL

Answer: B

,COALESCE returns the first non-null expression, so COALESCE(commission_pct, 0) returns the
commission percentage when present and 0 otherwise.

8. Which statement correctly describes a NULL value in Oracle Database?

A. NULL is always equal to zero.
B. NULL is always equal to an empty string.
C. NULL represents an unknown or missing value.
D. NULL is stored as the number -1.

Answer: C

NULL represents the absence of a value or an unknown value. It does not mean zero, and
comparisons with NULL require IS NULL or IS NOT NULL.

9. Which condition correctly identifies employees whose COMMISSION_PCT is NULL?

A. commission_pct = NULL
B. commission_pct <> NULL
C. commission_pct IS NULL
D. commission_pct LIKE NULL

Answer: C

Oracle uses IS NULL to test whether an expression contains a NULL value. Equality comparison
with NULL does not return TRUE.

10. A query must return employees whose salary is between 4000 and 7000, including both
boundaries. Which condition is appropriate?

A. salary BETWEEN 4000 AND 7000
B. salary RANGE 4000 TO 7000
C. salary > 4000 AND < 7000
D. salary IN 4000 THROUGH 7000

Answer: A

BETWEEN is inclusive of both endpoints, so salaries of exactly 4000 and 7000 are included.

11. Which operator is commonly used to search for a character pattern?

A. BETWEEN
B. LIKE
C. RANGE
D. MATCHES

, Answer: B

LIKE performs pattern matching. The % wildcard represents zero or more characters, while _
represents exactly one character.

12. A user needs all employee last names beginning with the letters 'Sm'. Which condition
should be used?

A. last_name LIKE '%Sm'
B. last_name LIKE 'Sm%'
C. last_name = 'Sm%'
D. last_name LIKE '_Sm'

Answer: B

The percent wildcard after Sm allows any number of characters to follow the specified prefix.

13. Which statement about the underscore wildcard in a LIKE condition is correct?

A. It represents zero or more characters.
B. It represents one or more words.
C. It represents exactly one character.
D. It represents only numeric characters.

Answer: C

The underscore wildcard represents exactly one character in an Oracle LIKE pattern.

14. Which operator tests whether a value matches one of several specified values?

A. IN
B. BETWEEN
C. EXISTS ONLY
D. MATCH

Answer: A

IN allows a value to be compared against a list or subquery result containing multiple possible
values.

15. Which query correctly retrieves employees working in departments 10, 20, or 30?

A. SELECT * FROM employees WHERE department_id IN (10,20,30);
B. SELECT * FROM employees WHERE department_id BETWEEN (10,20,30);
C. SELECT * FROM employees WHERE department_id = (10,20,30);
D. SELECT * FROM employees WHERE department_id ANY (10,20,30);