Oracle Database SQL Certified Associate Practice Test Exam

Oracle Database SQL Certified Associate
Practice Test Exam

1. Which query correctly displays the last name and department
name for all employees, including those without a department?
A) SELECT e.last_name, d.department_name FROM employees
e INNER JOIN departments d ON e.department_id =
d.department_id
B) SELECT e.last_name, d.department_name FROM employees
e LEFT OUTER JOIN departments d ON e.department_id =
d.department_id
C) SELECT e.last_name, d.department_name FROM employees
e RIGHT OUTER JOIN departments d ON e.department_id =
d.department_id
D) SELECT e.last_name, d.department_name FROM employees
e FULL OUTER JOIN departments d ON e.department_id =
d.department_id


Correct Answer: B
Rationale: A LEFT OUTER JOIN preserves all rows from the left
table (employees), even when no matching department exists.
INNER JOIN would exclude employees without departments,

,RIGHT OUTER JOIN would preserve departments, and FULL
OUTER JOIN would include unmatched rows from both sides.


2. What is the result of the following query: SELECT NVL(NULL,
'A') FROM DUAL?
A) NULL
B) 'A'
C) 0
D) Error


Correct Answer: B
Rationale: NVL returns the second argument when the first
argument is NULL. NVL(NULL, 'A') evaluates to 'A' because the
first expression is NULL.


3. Which statement about the WHERE clause is true?
A) It can only filter rows after grouping has occurred
B) It filters rows before any grouping or aggregation takes place
C) It can use group functions to filter results
D) It must always reference a column alias

,Correct Answer: B
Rationale: The WHERE clause filters individual rows before
grouping and aggregation. Group functions like SUM or COUNT
cannot be used in WHERE; those belong in HAVING after
GROUP BY.


4. Which operator is used to search for a pattern in a character
column?
A) =
B) LIKE
C) IN
D) BETWEEN


Correct Answer: B
Rationale: LIKE performs pattern matching using wildcards: %
matches any string of zero or more characters, and _ matches
exactly one character.


5. What does the DISTINCT keyword do in a SELECT statement?
A) Sorts the result set in ascending order

, B) Eliminates duplicate rows from the result set
C) Restricts the number of rows returned
D) Groups rows by a specified column


Correct Answer: B
Rationale: DISTINCT eliminates duplicate rows from the output.
It applies to all columns in the SELECT list, not just the first
column.


6. Which function returns the current date and time from the
database server?
A) TODAY()
B) SYSDATE
C) NOW()
D) GETDATE()


Correct Answer: B
Rationale: SYSDATE returns the current date and time from the
database server. CURRENT_DATE returns the date in the session
time zone.