CREATE INDEX syntax in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an index named idx_name on the column col1 of the table my_table.

SQL

CREATE INDEX [1] ON my_table (col1);

Drag options to blanks, or click blank then click option'

Atable_index

Bmy_index

Cindex1

Didx_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using the table name instead of the index name.

Omitting the index name.

Using a column name as the index name.

The index name follows the CREATE INDEX keyword. Here, idx_name is the correct index name.

2fill in blank

medium

Complete the code to create a unique index named unique_idx on the column email of the table users.

SQL

CREATE [1] INDEX unique_idx ON users (email);

Drag options to blanks, or click blank then click option'

AUNIQUE

BTEMPORARY

CFULLTEXT

DCLUSTERED

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEMPORARY which is for temporary tables, not indexes.

Using FULLTEXT which is for text search indexes.

Using CLUSTERED which is not standard SQL syntax.

The keyword UNIQUE creates an index that enforces uniqueness on the column values.

3fill in blank

hard

Fix the error in the code to create an index named idx_age on the age column of the table persons.

SQL

CREATE INDEX idx_age [1] persons (age);

Drag options to blanks, or click blank then click option'

AON

BIN

CFOR

DTO

Attempts:

3 left

💡 Hint

Common Mistakes

Using IN or FOR which are not valid here.

Omitting the keyword ON.

The correct syntax uses the keyword ON before the table name.

4fill in blank

hard

Fill both blanks to create a unique index named idx_unique on the columns col1 and col2 of the table data_table.

SQL

CREATE [1] INDEX idx_unique [2] data_table (col1, col2);

Drag options to blanks, or click blank then click option'

AUNIQUE

BTEMPORARY

CON

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEMPORARY instead of UNIQUE.

Using IN instead of ON.

The first blank needs UNIQUE to make the index unique. The second blank needs ON to specify the table.

5fill in blank

hard

Fill all three blanks to create a fulltext index named idx_fulltext on the column description of the table products.

SQL

CREATE [1] INDEX [2] [3] products (description);

Drag options to blanks, or click blank then click option'

AFULLTEXT

Bidx_fulltext

CON

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNIQUE instead of FULLTEXT.

Omitting the ON keyword.

Swapping the order of index name and index type.

The first blank is the index type FULLTEXT. The second blank is the index name idx_fulltext. The third blank is the keyword ON before the table name.

Practice

1. What is the main purpose of using CREATE INDEX in a database?

easy

A. To speed up data retrieval by creating shortcuts on columns

B. To delete rows faster from a table

C. To backup the database automatically

D. To encrypt the data stored in the table

Solution

1. Step 1: Understand what an index does

   An index creates a shortcut to quickly find data in a column without scanning the whole table.

2. Step 2: Identify the main benefit

   This shortcut speeds up data retrieval, especially for searches and sorts.

3. Final Answer:

   To speed up data retrieval by creating shortcuts on columns -> Option A

4. Quick Check:

   CREATE INDEX improves search speed [OK]

Hint: Indexes speed up searches by creating shortcuts [OK]

Common Mistakes:

• Thinking indexes speed up data deletion
• Confusing indexes with backups
• Believing indexes encrypt data

2. Which of the following is the correct syntax to create an index named idx_name on the column last_name of the table employees?

easy

A. CREATE idx_name INDEX ON employees (last_name);

B. CREATE INDEX idx_name ON employees (last_name);

C. INDEX CREATE idx_name ON employees (last_name);

D. CREATE INDEX ON employees idx_name (last_name);

Solution

1. Step 1: Recall the standard CREATE INDEX syntax

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

2. Step 2: Match the syntax with options

   CREATE INDEX idx_name ON employees (last_name); matches the correct order and keywords exactly.

3. Final Answer:

   CREATE INDEX idx_name ON employees (last_name); -> Option B

4. Quick Check:

   CREATE INDEX index_name ON table (column) [OK]

Hint: Remember: CREATE INDEX name ON table (column) [OK]

Common Mistakes:

• Swapping index name and table name positions
• Using wrong keyword order
• Omitting ON keyword

3. Given the table products(id INT, name VARCHAR, price DECIMAL), what will be the result of this SQL command?

CREATE INDEX idx_price ON products(price);

medium

A. The products table is duplicated with the name idx_price

B. The price column is deleted from the products table

C. An index named idx_price is created on the price column to speed up queries filtering by price

D. Syntax error because price is not a primary key

Solution

1. Step 1: Understand what the CREATE INDEX command does here

   The command creates an index named idx_price on the price column of products.

2. Step 2: Identify the effect of the index

   This index helps speed up queries that filter or sort by price without changing the table structure.

3. Final Answer:

   An index named idx_price is created on the price column to speed up queries filtering by price -> Option C

4. Quick Check:

   CREATE INDEX adds search speed, no deletion [OK]

Hint: CREATE INDEX speeds up search, does not delete or duplicate [OK]

Common Mistakes:

• Thinking index creation deletes columns
• Assuming index requires primary key
• Confusing index with table duplication

4. Identify the error in this SQL statement:

CREATE INDEX ON employees (last_name);

medium

A. Cannot create index on last_name column

B. Missing table name after ON

C. Incorrect use of parentheses around column name

D. Missing index name after CREATE INDEX

Solution

1. Step 1: Check the syntax of CREATE INDEX

   The syntax requires an index name after CREATE INDEX before ON.

2. Step 2: Identify the missing part

   The statement lacks the index name, which causes a syntax error.

3. Final Answer:

   Missing index name after CREATE INDEX -> Option D

4. Quick Check:

   CREATE INDEX must have a name [OK]

Hint: Always name your index after CREATE INDEX [OK]

Common Mistakes:

• Omitting the index name
• Confusing table name with index name
• Misplacing parentheses

5. You want to speed up queries that filter by both category and price columns in the items table. Which is the best way to create an index?

hard

A. CREATE INDEX idx_cat_price ON items (category, price);

B. CREATE INDEX idx_cat ON items (category); CREATE INDEX idx_price ON items (price);

C. CREATE INDEX idx_price_cat ON items (price, category);

D. CREATE INDEX idx_items ON items (category);

Solution

1. Step 1: Understand multi-column index usage

   A single index on (category, price) helps queries filtering by both columns efficiently.

2. Step 2: Compare options

   CREATE INDEX idx_cat_price ON items (category, price); creates a composite index on category and price in that order, which is best for combined filters.

3. Final Answer:

   CREATE INDEX idx_cat_price ON items (category, price); -> Option A

4. Quick Check:

   Composite index on both columns is best [OK]

Hint: Use composite index for multiple columns searched together [OK]

Common Mistakes:

• Creating separate indexes instead of composite
• Wrong column order in composite index
• Creating index on only one column when filtering by two