Question

Create an index on the name column of the student table. In addition, explain in one paragraph or less one of the advantages of creating an index and one of the disadvantages. Submit the statement used to create the index and the explanation.

Answer 1

Syntax for creating Index on Column

CREATE INDEX index_name
ON table_name (column1, column2, ...);

You can create index on one column or more than one column.

Here your Table name is student and the only column you want to hav an index is name

So the query will be

CREATE INDEX index_studentname
ON student (name);

Advantages of INDEX:

      Using the index on columns retrieves the data very quickly. The index is not visible to user, But the DB keeps track on the index of the column. Index helps the queries to look inside of a range, as well as for a specific value in a where clause.

Let us take an example for the advantages of INDEX

SELECT * from student where id between 1 and 100;

In this query if the column id is indexed, the DB would fetch results much faster than the table that is w/o indexed.

Disadvantages:

Though the INDEX is not visible to the user, it is saved internally on the server. Index may use up the memory space and results in the DB performance.