This set of SQL Multiple Choice Questions & Answers (MCQs) focuses on “SQL Index”.
1. Which of the following optional structure is used to improve the performance of a query performed on a table in SQL?
a) View
b) Index
c) Constraint
d) Table
View Answer
Explanation: The optional structure that is used to improve the performance of a query performed on a table in SQL is Index. Indexes are used to search the values from the table, when indexing is there you need not use the full table scan. A View is only a read only copy of a table.
2. Which of the following statement can be used to create an INDEX in SQL?
a) INSERT INDEX index_name table_name;
b) INSERT INDEX index_name ON table_name;
c) CREATE INDEX index_name table_name;
d) CREATE INDEX index_name ON table_name;
View Answer
Explanation: The statement that can be used to create an INDEX in SQL is –
CREATE INDEX index_name ON TABLE_NAME;
The CREATE command is used along with the INDEX and ON keyword.
3. Which of the following index is created on only one column of a table.
a) Implicit
b) Unique
c) Single-column
d) Composite
View Answer
Explanation: In SQL, a single-column index is created based on only one table column. Implicit Indexes are the indexes that are automatically created by the database server after an object is created. The unique index is used to create an index with only unique values.
4. Which of the following index is created on more than one column of a table.
a) Implicit
b) Unique
c) Single-column
d) Composite
View Answer
Explanation: In SQL, a Composite index is created on more than one table column. A single-column index is created based on only one table column. The Implicit Indexes are the indexes that are automatically created by the database server after an object is created. The unique index is used to create an index with only unique values.
5. Which of the following statement is used to create a UNIQUE Index in SQL?
a) CREATE INDEX index_name ON table_name (column_name);
b) CREATE UNIQUE INDEX index_name ON table_name (column_name);
c) CREATE INDEX UNIQUE index_name ON table_name (column_name);
d) CREATE UNIQUE INDEX index_name ON (column_name);
View Answer
Explanation: The statement that is used to create a UNIQUE Index in SQL is –
CREATE UNIQUE INDEX index_name ON TABLE_NAME (column_name);
The CREATE command is used with the UNIQUE INDEX and the ON keyword.
6. How many types of indexes are present in SQL?
a) 3
b) 4
c) 2
d) 5
View Answer
Explanation: Indexes is SQL can be classified into 2 categories: clustered index and non-clustered index. A Clustered Index in SQL defines the order in which data physically stored in a table. A Non-Clustered Index in SQL stores the index structure separately from the data that is physically stored in a table.
7. In SQL, Indexes can also be used on the columns containing null values.
a) True
b) False
View Answer
Explanation: In SQL, indexes should not be created on the columns containing null values because it specifies no use.
8. Which of the following statement is used to create a BIT-MAP Index in SQL?
a) CREATE INDEX index_name ON table_name (column_name);
b) CREATE BITMAP INDEX index_name ON (column_name);
c) CREATE INDEX BITMAP index_name ON table_name (column_name);
d) CREATE BITMAP INDEX index_name ON table_name (column_name);
View Answer
Explanation: The statement that is used to create a BIT-MAP Index in SQL is –
CREATE BITMAP INDEX index_name ON TABLE_NAME (column_name);
The CREATE command is used along with the BITMAP INDEX and the ON keyword. The column name specified must have distinct values.
9. Which of the following statement can be used to delete an INDEX in SQL?
a) DROP INDEX table_name;
b) DROP INDEX index_name;
c) DROP INDEX index_name ON table_name;
d) DELETE INDEX index_name;
View Answer
Explanation: The statement that can be used to delete an INDEX in SQL is –
DROP INDEX index_name;
The DROP command is used with the INDEX keyword. The index that has to be deleted is specified.
10. Which of the following pair of indexes can be created when there is a partition in a table?
a) Global and Local
b) Single and Composite
c) Clustered and Non-Clustered
d) Unique and Bitmap
View Answer
Explanation: In SQL, the Global and Local indexes are used when there is a partition in a table. Global index has one to many relationships which allows index partition to map to many table partitions. Local indexes are indexes where there is one to one mapping between index partition and table partition.
Sanfoundry Global Education & Learning Series – SQL.
To practice all areas of SQL, here is complete set of 1000+ Multiple Choice Questions and Answers.
If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]
