IBM Certification SQL Database 2 — Questions and Answers
Question 1: Which SQL isolation level prevents dirty reads but still allows non-repeatable reads?
- READ UNCOMMITTED
- READ COMMITTED (Correct answer)
- REPEATABLE READ
- SERIALIZABLE
Correct answer: READ COMMITTED
READ COMMITTED prevents dirty reads by only seeing committed data, but another transaction can modify rows between reads in the same transaction.
Question 2: In IBM Db2, what does the FETCH FIRST n ROWS ONLY clause do?
- Sorts the result set by n columns
- Limits the result set to the first n rows (Correct answer)
- Skips the first n rows in the result
- Returns only distinct rows up to n
Correct answer: Limits the result set to the first n rows
FETCH FIRST n ROWS ONLY is Db2's syntax to restrict result set size, equivalent to LIMIT in other databases.
Question 3: What is a correlated subquery?
- A subquery that runs once and its result is reused
- A subquery that references a column from the outer query (Correct answer)
- A subquery inside a JOIN clause
- A subquery that returns multiple result sets
Correct answer: A subquery that references a column from the outer query
A correlated subquery references columns from the outer query and is re-executed for each row processed by the outer query.
Question 4: Which aggregate function returns the number of rows that have a non-NULL value in a specified column?
- SUM(column)
- COUNT(column) (Correct answer)
- COUNT(*)
- AVG(column)
Correct answer: COUNT(column)
COUNT(column) counts only non-NULL values in the specified column, while COUNT(*) counts all rows including NULLs.
Question 5: What SQL keyword is used to remove duplicate rows from a result set?
- UNIQUE
- DISTINCT (Correct answer)
- FILTER
- EXCLUDE
Correct answer: DISTINCT
The DISTINCT keyword in a SELECT statement eliminates duplicate rows from the result set.
Question 6: In SQL, what is the purpose of the COALESCE function?
- Concatenates multiple strings into one
- Returns the first non-NULL value from a list of expressions (Correct answer)
- Converts data types between compatible types
- Rounds a numeric value to a specified precision
Correct answer: Returns the first non-NULL value from a list of expressions
COALESCE evaluates its arguments in order and returns the first expression that is not NULL.
Question 7: Which type of JOIN returns all rows from the left table and only matching rows from the right table?
- INNER JOIN
- RIGHT OUTER JOIN
- LEFT OUTER JOIN (Correct answer)
- CROSS JOIN
Correct answer: LEFT OUTER JOIN
A LEFT OUTER JOIN returns all rows from the left table; rows with no match in the right table have NULL in the right table's columns.
IBM Certification SQL Database 2
Which SQL isolation level prevents dirty reads but still allows non-repeatable reads?