Sample Exam Questions

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

Sample Question


The schema search path for user foo is set to “$user”, public by default.
The public schema has a table called bar, and all users (public) have been GRANTed privileges such as SELECT.
The schema foo exists, but there are no tables in it.
At this time, select all the correct statements from the following descriptions.

  1. SELECT * FROM bar; will result in an error as it fails to find the table
  2. CREATE TABLE bar(...); will result in an error because the table already exists.

    At this point, you execute CREATE TABLE; to create two tables, and
  3. When you execute SELECT * FROM bar; it returns the data retrieved from both tables, and
  4. Executing DROP TABLE bar; will only remove the table
  5. Executing DROP TABLE bar; will only delete the table

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

Answer and Explanation

A database schema is like a non-hierarchical directory structure. By creating separate schemas for each user or application and storing objects such as tables, views, and indexes in each schema, management becomes easier and naming conflicts can be avoided (multiple objects with the same name can be created).
In the default installation, only the public schema exists, which is accessible to all users, but schemas can be added by executing CREATE SCHEMA.

When specifying an object such as a table name in an SQL statement like SELECT, the conventional way is to connect the schema name and object name by a dot, like, but the schema name can be omitted. In that case, the question becomes which object is considered specified, and in PostgreSQL, this is determined by the schema search path. By default, $user, or the schema with the same name as the user, is given the highest priority, followed by public. When identifying an existing object, such as a table specified by SELECT, the procedure is to use the object with that name in the highest priority schema if it exists, and if not, to check if it exists in the second schema. For SQL that creates an object, like CREATE TABLE, the procedure is to create it in the highest priority schema if it exists, and in the second schema if it does not. It is also possible to specify more than two schemas in the schema search path, and in this case, the third schema is used if there is no object with the specified name in the second schema (or if the second schema does not exist).

Now, let's look at the choices for this question. For SELECT * FROM bar in the option A, it first tries to SELECT from the table if it exists, and if not, it SELECTs from In this case, there is no, so it SELECTs from For CREATE TABLE bar in the option B, it creates a table named because there is a schema named foo. If you do not have permission to create objects in the schema foo, this will result in an error, but it does not matter whether the table exists or not.
When executing SELECT * FROM bar in the option C, both and exist, but only the highest priority is targeted. The DROP table bar in D and E also only targets the highest priority and does not drop

Therefore, the correct answer is D.