CREATE INDEX syntax in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the effect of this CREATE INDEX statement?

Consider the SQL statement:

CREATE INDEX idx_customer_name ON customers (last_name, first_name);

What does this statement do?

A Creates an index named idx_customer_name on the customers table using the last_name and first_name columns to speed up queries filtering or sorting by these columns.

B Deletes the customers table and replaces it with an index named idx_customer_name.

C Creates a new table named idx_customer_name with columns last_name and first_name.

D Updates all rows in customers to have unique last_name and first_name values.

💡 Hint

Think about what an index does in a database.

The CREATE INDEX statement creates a data structure that helps the database find rows faster when searching or sorting by the specified columns. It does not create a new table or delete data.

📝 Syntax

intermediate

Which CREATE INDEX statement is syntactically correct?

Choose the correct syntax to create an index named idx_order_date on the orders table for the order_date column.

A CREATE INDEX idx_order_date orders (order_date);

B CREATE INDEX idx_order_date ON orders (order_date);

C CREATE INDEX ON orders idx_order_date (order_date);

D CREATE INDEX idx_order_date ON orders order_date;

💡 Hint

Remember the order: CREATE INDEX index_name ON table_name (column_name);

The correct syntax requires the index name immediately after CREATE INDEX, then ON, then the table name, and finally the column list in parentheses.

❓ optimization

advanced

Which index will best optimize queries filtering by both city and state?

You have a customers table with columns city and state. You want to speed up queries that filter by both city and state.
Which CREATE INDEX statement is best?

A CREATE INDEX idx_state ON customers (state);

B CREATE INDEX idx_state_city ON customers (state, city);

C CREATE INDEX idx_city ON customers (city);

D CREATE INDEX idx_city_state ON customers (city, state);

💡 Hint

Think about the order of columns in a multi-column index.

An index on (city, state) is best for queries filtering by city and state in that order. It can also be used for queries filtering only by city. The order matters for optimization.

🔧 Debug

advanced

What error does this CREATE INDEX statement produce?

Consider this SQL statement:

CREATE INDEX idx_price ON products price;

What error will it cause?

A Syntax error due to missing parentheses around the column name.

B Runtime error because the products table does not exist.

C No error; index created successfully.

D Error because idx_price is a reserved keyword.

💡 Hint

Check the syntax for specifying columns in CREATE INDEX.

The column list must be enclosed in parentheses. Omitting them causes a syntax error.

🧠 Conceptual

expert

What is a key limitation of creating too many indexes on a table?

Which of the following is a main drawback of having many indexes on a database table?

A The table schema becomes invalid and cannot be queried.

B Queries become slower because indexes confuse the query planner.

C Inserts, updates, and deletes become slower because all indexes must be updated.

D Indexes automatically delete duplicate rows causing data loss.

💡 Hint

Think about what happens when data changes in a table with many indexes.

Each time data changes, all indexes must be updated, which slows down write operations. Queries usually become faster, not slower.

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