Index impact on INSERT and UPDATE in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an index on the 'email' column of the 'users' table.

SQL

CREATE INDEX [1] ON users(email);

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

Aemail_index

Bidx_email

Cusers_email_idx

Dindex_email

Attempts:

3 left

💡 Hint

Common Mistakes

Using a name that is too generic or unclear.

Leaving the index name blank.

The index name 'users_email_idx' follows a common naming convention for indexes on the 'users' table and 'email' column.

2fill in blank

medium

Complete the code to insert a new user into the 'users' table.

SQL

INSERT INTO users (id, name, email) VALUES ([1], 'Alice', '[e-mail]');

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

ANULL

B'1'

C'Alice'

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the number 1 inside quotes, making it a string.

Using NULL which would cause an error if the column is NOT NULL.

The 'id' column is usually an integer, so the value should be 1 without quotes.

3fill in blank

hard

Fix the error in the UPDATE statement to change the email of user with id 1.

SQL

UPDATE users SET email = [1] WHERE id = 1;

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

A'[e-mail]'

[e-mail]

C1

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around the new email address.

Using NULL which would remove the email.

String values in SQL must be enclosed in single quotes.

4fill in blank

hard

Fill both blanks to create a unique index on the 'username' column of the 'accounts' table.

SQL

CREATE [1] INDEX [2] ON accounts(username);

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

AUNIQUE

BPRIMARY

Cidx_username

Dunique_username_idx

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY which is for primary keys, not indexes.

Using generic index names without 'unique'.

The keyword UNIQUE creates a unique index. The name 'unique_username_idx' clearly describes the index.

5fill in blank

hard

Fill all three blanks to update the 'last_login' column to current timestamp for user with id 5.

SQL

UPDATE [1] SET [2] = [3] WHERE id = 5;

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

Ausers

Blast_login

CCURRENT_TIMESTAMP

Dlogin_time

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong table or column names.

Not using CURRENT_TIMESTAMP for the current time.

The table is 'users', the column to update is 'last_login', and the value is the SQL function CURRENT_TIMESTAMP.