Why string manipulation is common in MySQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the first 5 characters of the name column.

MySQL

SELECT [1](name, 5) FROM employees;

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

ARIGHT

BCONCAT

CSUBSTRING_INDEX

DLEFT

Attempts:

3 left

💡 Hint

Common Mistakes

Using RIGHT instead of LEFT

Using CONCAT which joins strings

Using SUBSTRING_INDEX incorrectly

The LEFT function extracts the leftmost characters from a string. Here, it gets the first 5 characters of the name.

2fill in blank

medium

Complete the code to find the position of 'a' in the city column.

MySQL

SELECT [1]('a', city) FROM locations;

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

ALOCATE

BFIND

CINSTR

DPOSITION

Attempts:

3 left

💡 Hint

Common Mistakes

Using POSITION which is standard SQL but less common in MySQL

Using FIND which is not a MySQL function

The LOCATE function returns the position of a substring in a string. It returns the first occurrence of 'a' in city.

3fill in blank

hard

Fix the error in the code to convert the email column to lowercase.

MySQL

SELECT [1](email) FROM users;

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

ALOWER

BLOWERCASE

CTOLOWER

DUPPER

Attempts:

3 left

💡 Hint

Common Mistakes

Using UPPER which converts to uppercase

Using LOWERCASE or TOLOWER which are not valid MySQL functions

The correct function to convert text to lowercase in MySQL is LOWER. Other options are invalid or do not exist.

4fill in blank

hard

Fill both blanks to extract the domain from an email address.

MySQL

SELECT SUBSTRING(email, [1], [2]) AS domain FROM users;

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

ALOCATE('@', email) + 1

BLENGTH(email)

CLOCATE('@', email) - 1

DCHAR_LENGTH(email)

Attempts:

3 left

💡 Hint

Common Mistakes

Using LOCATE('@', email) - 1 which points before '@'

Using LENGTH which is similar but may not handle multibyte characters correctly

The domain starts right after '@', so we add 1 to its position for the start. The length is the total length of the email minus the position of '@' to get the rest of the string.

5fill in blank

hard

Fill all three blanks to create a query that replaces 'old' with 'new' in the description column and trims spaces.

MySQL

SELECT TRIM([1](description, [2], [3])) AS updated_desc FROM products;

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

AREPLACE

B'old'

C'new'

DSUBSTRING

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUBSTRING which extracts parts but does not replace

Not trimming spaces after replacement

The REPLACE function replaces all occurrences of 'old' with 'new' in the description. Then TRIM removes extra spaces around the result.

Practice

1. Why is string manipulation commonly used in databases like MySQL?

easy

A. To perform complex mathematical calculations

B. To clean, extract, and format text data efficiently

C. To store large binary files

D. To manage user permissions

Solution

1. Step 1: Understand the role of string manipulation

   String manipulation helps in handling text data by cleaning, extracting parts, and formatting it.

2. Step 2: Identify the correct use case in databases

   Databases often store text data that needs to be processed, so string functions are essential.

3. Final Answer:

   To clean, extract, and format text data efficiently -> Option B

4. Quick Check:

   String manipulation = text handling [OK]

Hint: String functions handle text, not numbers or files [OK]

Common Mistakes:

• Confusing string manipulation with numeric calculations
• Thinking string functions manage files or permissions

2. Which of the following is the correct syntax to convert a string to uppercase in MySQL?

easy

A. UPCASE('hello')

B. TOUPPER('hello')

C. UPPER('hello')

D. MAKEUPPER('hello')

Solution

1. Step 1: Recall MySQL string functions

   MySQL uses UPPER() to convert text to uppercase.

2. Step 2: Check syntax correctness

   UPPER('hello') is valid; other options are not recognized functions.

3. Final Answer:

   UPPER('hello') -> Option C

4. Quick Check:

   UPPER() = uppercase conversion [OK]

Hint: Use UPPER() to change text to uppercase [OK]

Common Mistakes:

• Using non-existent functions like TOUPPER or UPCASE
• Missing parentheses around the string

3. What will be the result of this MySQL query?
SELECT CONCAT('Data', 'Base') AS result;

medium

A. 'Database'

B. 'Data Base'

C. 'Data+Base'

D. 'Data,Base'

Solution

1. Step 1: Understand CONCAT function

   CONCAT joins strings directly without spaces or symbols.

2. Step 2: Apply CONCAT to given strings

   Concatenating 'Data' and 'Base' results in 'Database'.

3. Final Answer:

   'Database' -> Option A

4. Quick Check:

   CONCAT('Data','Base') = 'Database' [OK]

Hint: CONCAT joins strings exactly as given [OK]

Common Mistakes:

• Assuming CONCAT adds spaces or symbols automatically
• Confusing CONCAT with CONCAT_WS which adds separators

4. Identify the error in this MySQL query:
SELECT SUBSTRING('Hello World', 7, 5 FROM dual);

medium

A. Incorrect use of FROM clause in SUBSTRING function

B. Missing closing parenthesis

C. SUBSTRING function requires only two arguments

D. String literal must be in double quotes

Solution

1. Step 1: Review SUBSTRING syntax in MySQL

   SUBSTRING(string, start, length) does not use FROM clause inside function.

2. Step 2: Identify misuse of FROM clause

   FROM dual is used in Oracle, not inside SUBSTRING in MySQL.

3. Final Answer:

   Incorrect use of FROM clause in SUBSTRING function -> Option A

4. Quick Check:

   SUBSTRING syntax = no FROM inside function [OK]

Hint: SUBSTRING() uses commas, no FROM inside parentheses [OK]

Common Mistakes:

• Adding FROM inside SUBSTRING arguments
• Confusing MySQL syntax with Oracle SQL

5. You want to extract the domain name from email addresses stored in a MySQL table column email. Which query correctly extracts the domain part after '@'?

hard

A. SELECT LOWER(email) AS domain FROM users;

B. SELECT SUBSTRING(email, 1, LOCATE('@', email)) AS domain FROM users;

C. SELECT CONCAT('@', email) AS domain FROM users;

D. SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM users;

Solution

1. Step 1: Understand the goal to extract domain after '@'

   The domain starts right after the '@' character in the email string.

2. Step 2: Use LOCATE to find '@' position and SUBSTRING to extract after it

   LOCATE('@', email) finds '@' position; adding 1 moves to domain start; SUBSTRING extracts from there to end.

3. Final Answer:

   SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM users; -> Option D

4. Quick Check:

   Extract domain = SUBSTRING after '@' [OK]

Hint: Use LOCATE('@', email)+1 with SUBSTRING to get domain [OK]

Common Mistakes:

• Extracting before '@' instead of after
• Concatenating '@' instead of extracting domain
• Using LOWER() which only changes case