Oracle Database SQL (1Z0-071):
Practice Questions with Answers &
Rationales
Topic 1: Retrieving Data with SELECT Statements
Question 1
Which statement about the SELECT clause is true?
A) It is mandatory to include a WHERE clause
B) The DISTINCT keyword eliminates duplicate rows from the result set
C) The asterisk (*) selects all columns in a specific order defined by the user
D) Column aliases cannot contain spaces
Answer: B
,Rationale: The DISTINCT keyword removes duplicate rows from the query results . Column
aliases can contain spaces if enclosed in double quotes, and the WHERE clause is optional. The
asterisk selects all columns in the order they are defined in the table, not user-defined order.
Question 2
What does the following query return? `SELECT 'Hello' || ' ' || 'World' FROM dual;`
A) Hello World
B) Hello World (with a syntax error)
C) HelloWorld
D) CONCAT error
Answer: A
Rationale: The concatenation operator (||) joins strings together . The query returns "Hello
World" with a space between the words. The DUAL table is a dummy table used for selecting
pseudo-columns or functions.
Question 3
Which query correctly displays employee last names with the first letter capitalized and the rest
lowercase?
A) SELECT INITCAP(last_name) FROM employees;
B) SELECT UPPER(last_name) FROM employees;
,C) SELECT LOWER(last_name) FROM employees;
D) SELECT CAPITALIZE(last_name) FROM employees;
Answer: A
Rationale: The INITCAP function capitalizes the first letter of each word and makes the
remaining letters lowercase . UPPER converts all to uppercase, and LOWER converts all to
lowercase. CAPITALIZE is not an Oracle SQL function.
Question 4
What is the purpose of the NVL function?
A) Converts a value to NULL
B) Replaces a NULL value with a specified value
C) Compares two values and returns NULL if they are equal
D) Returns the first non-NULL value in a list
Answer: B
Rationale: NVL(expr1, expr2) returns expr2 if expr1 is NULL; otherwise, it returns expr1. This is
commonly used to substitute a default value for NULLs . COALESCE is similar but handles
multiple expressions.
Question 5
, Which query would return the current system date?
A) SELECT CURRENT_TIMESTAMP FROM dual;
B) SELECT SYSDATE FROM dual;
C) SELECT NOW() FROM dual;
D) Both A and B
Answer: D
Rationale: SYSDATE returns the current date and time from the database server .
CURRENT_TIMESTAMP returns the current date and time in the session time zone . Both are
valid in Oracle SQL.
Question 6
What is the result of `SELECT ROUND(15.678, 1) FROM dual;`?
A) 15.7
B) 15.6
C) 16
D) 15.68
Answer: A
Rationale: ROUND(number, decimal_places) rounds the number to the specified number of
decimal places . 15.678 rounded to 1 decimal place is 15.7 (since the second decimal is 7, it
rounds up).
Practice Questions with Answers &
Rationales
Topic 1: Retrieving Data with SELECT Statements
Question 1
Which statement about the SELECT clause is true?
A) It is mandatory to include a WHERE clause
B) The DISTINCT keyword eliminates duplicate rows from the result set
C) The asterisk (*) selects all columns in a specific order defined by the user
D) Column aliases cannot contain spaces
Answer: B
,Rationale: The DISTINCT keyword removes duplicate rows from the query results . Column
aliases can contain spaces if enclosed in double quotes, and the WHERE clause is optional. The
asterisk selects all columns in the order they are defined in the table, not user-defined order.
Question 2
What does the following query return? `SELECT 'Hello' || ' ' || 'World' FROM dual;`
A) Hello World
B) Hello World (with a syntax error)
C) HelloWorld
D) CONCAT error
Answer: A
Rationale: The concatenation operator (||) joins strings together . The query returns "Hello
World" with a space between the words. The DUAL table is a dummy table used for selecting
pseudo-columns or functions.
Question 3
Which query correctly displays employee last names with the first letter capitalized and the rest
lowercase?
A) SELECT INITCAP(last_name) FROM employees;
B) SELECT UPPER(last_name) FROM employees;
,C) SELECT LOWER(last_name) FROM employees;
D) SELECT CAPITALIZE(last_name) FROM employees;
Answer: A
Rationale: The INITCAP function capitalizes the first letter of each word and makes the
remaining letters lowercase . UPPER converts all to uppercase, and LOWER converts all to
lowercase. CAPITALIZE is not an Oracle SQL function.
Question 4
What is the purpose of the NVL function?
A) Converts a value to NULL
B) Replaces a NULL value with a specified value
C) Compares two values and returns NULL if they are equal
D) Returns the first non-NULL value in a list
Answer: B
Rationale: NVL(expr1, expr2) returns expr2 if expr1 is NULL; otherwise, it returns expr1. This is
commonly used to substitute a default value for NULLs . COALESCE is similar but handles
multiple expressions.
Question 5
, Which query would return the current system date?
A) SELECT CURRENT_TIMESTAMP FROM dual;
B) SELECT SYSDATE FROM dual;
C) SELECT NOW() FROM dual;
D) Both A and B
Answer: D
Rationale: SYSDATE returns the current date and time from the database server .
CURRENT_TIMESTAMP returns the current date and time in the session time zone . Both are
valid in Oracle SQL.
Question 6
What is the result of `SELECT ROUND(15.678, 1) FROM dual;`?
A) 15.7
B) 15.6
C) 16
D) 15.68
Answer: A
Rationale: ROUND(number, decimal_places) rounds the number to the specified number of
decimal places . 15.678 rounded to 1 decimal place is 15.7 (since the second decimal is 7, it
rounds up).
