Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.1 SQL commands (Schema)

Sample Question


Assume the result of 'show search_path' is as follows:

=> show search_path;
  "$user", public

Choose the two most appropriate descriptions for the execution of 'SELECT * FROM'.

  1. It displays the contents of the table 'bar' in the database 'foo'.

  2. It displays the contents of the table 'bar' in the schema 'foo'.

  3. It displays the contents of the table 'bar' owned by the user 'foo'.

  4. Both the USAGE privilege on 'foo' and the SELECT privilege on 'bar' are required for execution.

  5. If executed by the user 'foo', 'SELECT * FROM bar;' will always yield the same result.

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

Answer and Explanation

In PostgreSQL, a database cluster contains multiple databases, each with several schemas. Objects such as tables belong to a specific schema. Therefore, when specifying a table name in a SELECT statement, the syntax allows for the addition of the database name and schema name before the table name, as follows:

SELECT ... FROM database_name.schema_name.table_name ...

However, in practice, access to databases other than the one currently connected to is not permitted. If you specify another database name, an error will occur. Therefore, you would typically specify the table name in the format 'schema_name.table_name', or omit the schema name and specify only the table name.

It's important to note that if you specify a table name in the format 'schema_name.table_name', you might assume that having only SELECT privilege on the table is sufficient. However, USAGE privilege on the schema itself is also required, for instance, to check whether a table with that name exists in the specified schema.

If you omit the schema name, the schema search path determines which schema's table to use. As mentioned at the beginning of this question, you can check the schema search path with 'show search_path'. By default, the priority is the schema with the same name as the user name, followed by the public schema. If the user name is 'foo', 'SELECT * FROM bar' will SELECT from '' if there is a table 'bar' in the schema 'foo'. If there is no 'bar' in 'foo' but there is a table 'bar' in the public schema, it will SELECT from ''. If neither exists, an error will occur. On the other hand, 'SELECT * FROM' will SELECT from '' if it exists, and if it doesn't, an error will occur. This behavior is slightly different, so be careful as it may seem intuitively the same at first glance.

Therefore, the correct answers are B and D.