Sample Exam Questions

From the objective of OSS-DB Exam Silver
S2.5 Basic server administration (Autovacuum concepts and behavior)

Sample Question


Pick one incorrect description of the information schema.

  1. It is a collection of views stored in the schema named information_schema.

  2. It stores various information about the database, which can be retrieved using a SELECT statement.

  3. Table information can be checked by referencing 'tables', and column information can be checked by referencing 'columns'.

  4. As it is specified in the SQL standard, all RDBMSs, including PostgreSQL, support information schemas.

  5. As it adheres to the SQL standard, information about PostgreSQL's proprietary extensions cannot be obtained from the information schema.

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

PostgreSQL provides the information schema and system catalogs as means to retrieve information about objects within a database.
The information schema is implemented as views stored in a schema named 'information_schema'. Its specifications are defined by the SQL standard, hence the names of views and columns, as well as the information they provide, are common across many RDBMSs. Therefore, SQL scripts that retrieve information from the information schema are likely to be usable as-is in other RDBMSs. However, not all RDBMSs support information schemas (for example, Oracle does not provide one), and information about PostgreSQL's proprietary extensions is not included in the information schema.
System catalogs are implemented as tables or views that have names starting with 'pg_'. As the name suggests, they are not compatible with other RDBMSs, but they do include information about PostgreSQL's proprietary extensions.
Information about tables and columns is stored in 'tables' and 'columns' in the information schema, and in 'pg_tables' and 'pg_attribute' in the system catalogs, respectively. Using SELECT statements, users can retrieve information within the scope of their privileges. Connect to the database with psql and check what columns are available by executing '\d information_schema.tables' or '\d pg_tables', and also try executing SELECT statements.

This is a question where you need to select the incorrect option, so the correct answer is D.