IBM Certification SQL Database 3 — Questions and Answers
Question 1: What is the function of a CHECK constraint in SQL?
- Ensures referential integrity between two tables
- Validates that column values satisfy a specified condition (Correct answer)
- Prevents duplicate values in a column
- Forces a column to have a non-NULL value
Correct answer: Validates that column values satisfy a specified condition
A CHECK constraint enforces a Boolean condition on column values, rejecting any row that does not satisfy it.
Question 2: In IBM Db2, which statement is used to permanently save changes made in a transaction?
- SAVE TRANSACTION
- COMMIT (Correct answer)
- FINALIZE
- END TRANSACTION
Correct answer: COMMIT
COMMIT permanently saves all changes made since the last COMMIT or ROLLBACK point in the current transaction.
Question 3: Which SQL clause filters groups produced by GROUP BY before they are returned?
- WHERE
- HAVING (Correct answer)
- FILTER
- QUALIFY
Correct answer: HAVING
HAVING filters aggregated groups after GROUP BY has been applied, whereas WHERE filters individual rows before grouping.
Question 4: What does the SQL UNION operator do?
- Joins two tables on a common column
- Combines the result sets of two SELECT statements, removing duplicates (Correct answer)
- Returns only rows present in both result sets
- Multiplies each row of one table with every row of another
Correct answer: Combines the result sets of two SELECT statements, removing duplicates
UNION combines result sets from two SELECT statements and removes duplicate rows; use UNION ALL to keep duplicates.
Question 5: In SQL, what is a surrogate key?
- A natural key derived from business data
- A system-generated artificial identifier with no business meaning (Correct answer)
- A composite key made of multiple columns
- A foreign key that references itself in the same table
Correct answer: A system-generated artificial identifier with no business meaning
A surrogate key is an artificially generated value (such as an auto-incremented integer) used as a primary key independent of business attributes.
Question 6: Which SQL window function assigns a unique sequential integer to each row within a partition?
- RANK()
- DENSE_RANK()
- ROW_NUMBER() (Correct answer)
- NTILE()
Correct answer: ROW_NUMBER()
ROW_NUMBER() assigns a unique, sequential integer to every row within a partition with no gaps or ties.
Question 7: What is the effect of setting a column as NOT NULL in a table definition?
- The column must contain a unique value
- The column cannot store a NULL value (Correct answer)
- The column automatically gets an index
- The column defaults to zero for numeric types
Correct answer: The column cannot store a NULL value
NOT NULL is a column constraint that prevents NULL from being inserted or updated into that column.
IBM Certification SQL Database 3
What is the function of a CHECK constraint in SQL?