Expression indexes in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an expression index on the lower case of the column 'name'.

PostgreSQL

CREATE INDEX idx_lower_name ON users ([1]);

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

Aname

BUPPER(name)

CLOWER(name)

Dlength(name)

Attempts:

3 left

💡 Hint

Common Mistakes

Using the column name directly without the LOWER function.

Using UPPER instead of LOWER.

The expression index uses LOWER(name) to index the lowercase version of the 'name' column.

2fill in blank

medium

Complete the code to create an expression index on the first 10 characters of the 'email' column.

PostgreSQL

CREATE INDEX idx_email_prefix ON users ([1]);

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

ASUBSTRING(email FROM 1 FOR 10)

BLEFT(email, 5)

CRIGHT(email, 10)

DLENGTH(email)

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEFT(email, 5) which only gets 5 characters.

Using RIGHT(email, 10) which gets the last 10 characters.

The expression index uses SUBSTRING(email FROM 1 FOR 10) to index the first 10 characters of the 'email' column.

3fill in blank

hard

Fix the error in the expression index creation by completing the code correctly.

PostgreSQL

CREATE INDEX idx_trim_name ON users ([1]);

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

Aname TRIM()

BTRIM name

CTRIM(name, ' ')

DTRIM(name)

Attempts:

3 left

💡 Hint

Common Mistakes

Writing TRIM without parentheses.

Trying to pass two arguments to TRIM.

The correct syntax for trimming spaces is TRIM(name). Options A, B, and D are invalid syntax. Option D is invalid because TRIM in PostgreSQL does not take two arguments like that.

4fill in blank

hard

Fill both blanks to create an expression index on the lower case of the first 5 characters of 'username'.

PostgreSQL

CREATE INDEX idx_lower_prefix ON users ([1]([2](username, 1, 5)));

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

ALOWER

BSUBSTRING

Cusername

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Using email instead of username.

Reversing the order of functions.

The expression index applies LOWER to the SUBSTRING of 'username'. The correct order is LOWER(SUBSTRING(username, 1, 5)).

5fill in blank

hard

Fill all three blanks to create an expression index on the trimmed, lowercased 'city' column.

PostgreSQL

CREATE INDEX idx_trim_lower_city ON locations ([1]([2]([3])));

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

ALOWER

BTRIM

Ccity

Dstate

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'state' instead of 'city'.

Reversing the order of LOWER and TRIM.

The expression index applies LOWER to TRIM applied to the 'city' column: LOWER(TRIM(city)).