SQL Database Flashcards

In IBM Db2, which catalog view can you query to list all tables in the current database?

Answer: SYSCAT.TABLES

SYSCAT.TABLES is the Db2 system catalog view that contains metadata about every table in the database.

What is the purpose of a database index?

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.

Which SQL function returns the current date and time from the database server?

Answer: NOW() / CURRENT TIMESTAMP

In IBM Db2, CURRENT TIMESTAMP (ANSI standard) and NOW() return the current date and time from the server.

What is a materialized view (also called a Materialized Query Table in Db2)?

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.

Which SQL statement grants a user the privilege to read data from a table?

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.

What is the difference between a CHAR and VARCHAR data type in SQL?

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.

In SQL, what does the EXISTS predicate do in a WHERE clause?

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.