Sample Exam Questions

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

Sample Question


The following commands were used to create tables x and y. Which columns are automatically indexed? Answer all that apply.

create table x (a integer primary key, b integer unique, c varchar(50) not null);
create table y (d integer primary key, e integer references x(b), f integer check (f > 0));

  1. Column a of table x

  2. Column b of table x

  3. Column c of table x

  4. Column d of table y

  5. Column e of table y

  6. Column f of table y

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

Answer and Explanation

Indexes are typically created to speed up searches, but they can also be created automatically when constraints are applied to tables. Specifically, when a unique constraint is applied to a column of a table, the database needs to verify the uniqueness of the value, so it automatically creates an index to facilitate fast verification. Similarly, a primary key, which also requires uniqueness, is automatically indexed. Other constraints (NOT NULL, CHECK, foreign key) do not result in automatic index creation.

In the table creation of this example, column 'a' of table 'x' is the primary key, column 'b' is the unique key, and column 'd' of table 'y' is the primary key, so these columns are indexed. Columns with NOT NULL constraints (column 'c' in 'x'), foreign key constraints (column 'e' in 'y'), and CHECK constraints (column 'f' in 'y') are not automatically indexed (although they can be manually indexed by executing CREATE INDEX).

Therefore, the correct answers are A, B, and D.