CREATE INDEX syntax in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the CREATE INDEX statement in SQL?

It creates an index on one or more columns of a table to speed up data retrieval.

beginner

Write the basic syntax of CREATE INDEX.

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

intermediate

Can you create an index on multiple columns? How?

Yes, by listing multiple columns separated by commas inside parentheses after the table name.

intermediate

What is a unique index and how do you create it?

A unique index ensures all values in the indexed column(s) are distinct. Use CREATE UNIQUE INDEX instead of just CREATE INDEX.

intermediate

Does creating an index affect data insertion speed? Why or why not?

Yes, because the database must update the index every time data is inserted, which adds overhead.

What does the CREATE INDEX statement do?

A Creates a new table

B Deletes data from a table

C Speeds up data retrieval by creating an index

D Updates existing data

CREATE INDEX creates an index to speed up searching data in a table.

Which of the following is the correct syntax to create an index on the column name in the table users?

A CREATE INDEX users ON name;

B CREATE INDEX idx_name ON users (name);

C CREATE INDEX name ON users;

D CREATE INDEX users_name (name);

The correct syntax is CREATE INDEX index_name ON table_name (column_name);

How do you create a unique index?

A CREATE UNIQUE INDEX index_name ON table_name (column);

B CREATE INDEX UNIQUE index_name ON table_name (column);

C CREATE INDEX index_name UNIQUE ON table_name (column);

D CREATE INDEX index_name ON table_name UNIQUE (column);

The correct syntax places UNIQUE right after CREATE: CREATE UNIQUE INDEX.

Can you create an index on more than one column?

A No, indexes only work on single columns

B Yes, by listing columns separated by commas inside parentheses

C Yes, but only if columns are of the same data type

D No, you must create separate indexes for each column

You can create a multi-column index by listing columns inside parentheses separated by commas.

What is a downside of creating many indexes on a table?

A It slows down data retrieval

B It uses less disk space

C It slows down data insertion and updates

D It deletes data accidentally

Indexes must be updated on data changes, so many indexes slow down insert and update operations.

Explain the syntax and purpose of the CREATE INDEX statement.

Think about how you tell the database to make searching faster.

Describe the trade-offs of using indexes in a database.

Consider both benefits and costs of indexes.