IBM Certification SQL Database 5 — Questions and Answers
Question 1: In IBM Db2, which catalog view can you query to list all tables in the current database?
- INFORMATION_SCHEMA.COLUMNS
- SYSCAT.TABLES (Correct answer)
- SYS.ALL_TABLES
- DBA_TABLES
Correct answer: SYSCAT.TABLES
SYSCAT.TABLES is the Db2 system catalog view that contains metadata about every table in the database.
Question 2: What is the purpose of a database index?
- Enforce referential integrity between related tables
- Speed up data retrieval by creating a sorted pointer structure on columns (Correct answer)
- Store backup copies of table data for disaster recovery
- Restrict which users can query specific columns
Correct answer: Speed up data retrieval by creating a sorted pointer structure on columns
An index creates a sorted data structure on one or more columns so the database can find rows faster without scanning the entire table.
Question 3: Which SQL function returns the current date and time from the database server?
- NOW() / CURRENT TIMESTAMP (Correct answer)
- SYSDATE()
- GETDATE()
- CURRENT_TIME()
Correct answer: NOW() / CURRENT TIMESTAMP
In IBM Db2, CURRENT TIMESTAMP (ANSI standard) and NOW() return the current date and time from the server.
Question 4: What is a materialized view (also called a Materialized Query Table in Db2)?
- A virtual table that executes its query each time it is accessed
- A precomputed, physically stored result of a query that can be refreshed (Correct answer)
- An encrypted view restricted to privileged users only
- A temporary table automatically dropped after a session ends
Correct answer: A precomputed, physically stored result of a query that can be refreshed
A Materialized Query Table (MQT) in Db2 stores the query result physically on disk, improving query performance at the cost of storage and refresh overhead.
Question 5: Which SQL statement grants a user the privilege to read data from a table?
- ALLOW SELECT ON table TO user
- PERMIT user TO SELECT table
- GRANT SELECT ON table TO user (Correct answer)
- ASSIGN READ ON table TO user
Correct answer: GRANT SELECT ON table TO user
The GRANT statement is used to give database privileges; GRANT SELECT ON tablename TO username provides read access.
Question 6: What is the difference between a CHAR and VARCHAR data type in SQL?
- CHAR stores Unicode while VARCHAR stores ASCII only
- CHAR is fixed-length while VARCHAR stores variable-length strings up to a maximum (Correct answer)
- VARCHAR has better performance than CHAR for all workloads
- CHAR allows NULL values while VARCHAR does not
Correct answer: CHAR is fixed-length while VARCHAR stores variable-length strings up to a maximum
CHAR(n) always stores exactly n characters (padding with spaces if needed), while VARCHAR(n) stores only the actual string length up to n characters.
Question 7: In SQL, what does the EXISTS predicate do in a WHERE clause?
- Checks whether a column has a non-NULL value
- Returns TRUE if a subquery produces at least one row (Correct answer)
- Verifies that a table exists in the database schema
- Counts the number of rows returned by a subquery
Correct answer: Returns TRUE if a subquery produces at least one row
EXISTS evaluates a subquery and returns TRUE if the subquery returns one or more rows, making it useful for semi-join patterns.
IBM Certification SQL Database 5
In IBM Db2, which catalog view can you query to list all tables in the current database?