IBM Certification SQL Database 4 — Questions and Answers
Question 1: Which SQL statement is used to change the structure of an existing table by adding a column?
- MODIFY TABLE
- UPDATE TABLE
- ALTER TABLE (Correct answer)
- CHANGE TABLE
Correct answer: ALTER TABLE
ALTER TABLE ... ADD COLUMN modifies an existing table's schema without dropping and recreating it.
Question 2: In IBM Db2, what is a tablespace?
- A logical grouping of indexes only
- A storage structure that maps database objects to physical storage (Correct answer)
- A schema that contains multiple databases
- A synonym for a database schema
Correct answer: A storage structure that maps database objects to physical storage
A tablespace is a logical storage container that maps database tables and indexes to specific physical storage locations or storage groups.
Question 3: What is the purpose of the SQL EXCEPT operator?
- Returns rows common to both result sets
- Returns rows from the first result set not present in the second (Correct answer)
- Combines all rows from two result sets including duplicates
- Filters rows based on a subquery condition
Correct answer: Returns rows from the first result set not present in the second
EXCEPT returns distinct rows from the first SELECT that do not appear in the second SELECT's result set (called MINUS in some databases).
Question 4: Which normal form eliminates transitive dependencies among non-key attributes?
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF) (Correct answer)
- Boyce-Codd Normal Form (BCNF)
Correct answer: Third Normal Form (3NF)
Third Normal Form (3NF) requires that no non-key attribute is transitively dependent on the primary key through another non-key attribute.
Question 5: In SQL, what does a FULL OUTER JOIN return?
- Only rows matching in both tables
- All rows from the left table with NULLs for unmatched right rows
- All rows from both tables with NULLs where there is no match (Correct answer)
- A Cartesian product of both tables
Correct answer: All rows from both tables with NULLs where there is no match
A FULL OUTER JOIN returns all rows from both tables; unmatched rows from either side have NULL values in the columns of the other table.
Question 6: What does the SQL TRUNCATE TABLE statement do compared to DELETE?
- Removes selected rows based on a WHERE condition
- Removes all rows faster with no WHERE support and typically cannot be rolled back (Correct answer)
- Drops the table and all its indexes permanently
- Removes all rows and resets the table structure to empty
Correct answer: Removes all rows faster with no WHERE support and typically cannot be rolled back
TRUNCATE TABLE removes all rows very quickly by deallocating data pages, does not support WHERE, and in most databases cannot be rolled back.
Question 7: Which concept describes the ability of a database to ensure that only complete transactions are recorded?
- Consistency
- Atomicity (Correct answer)
- Isolation
- Durability
Correct answer: Atomicity
Atomicity (the A in ACID) guarantees that a transaction is treated as a single unit — either all operations succeed or none are applied.
IBM Certification SQL Database 4
Which SQL statement is used to change the structure of an existing table by adding a column?