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.