UPPER and LOWER in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of the UPPER function?

Given the table Employees with a column name containing the value 'Alice', what will be the result of this query?

SELECT UPPER(name) FROM Employees WHERE name = 'Alice';

MySQL

SELECT UPPER(name) FROM Employees WHERE name = 'Alice';

A alice

B Error: function UPPER not found

C ALICE

D Alice

💡 Hint

UPPER converts all letters to uppercase.

The UPPER function changes all letters in the string to uppercase, so 'Alice' becomes 'ALICE'.

❓ query_result

intermediate

What does LOWER do to mixed case text?

Consider a table Products with a column category containing the value 'EleCtrOnics'. What is the output of this query?

SELECT LOWER(category) FROM Products WHERE category = 'EleCtrOnics';

MySQL

SELECT LOWER(category) FROM Products WHERE category = 'EleCtrOnics';

A EleCtrOnics

B electronics

C ELECtrONICS

D Error: function LOWER not found

💡 Hint

LOWER converts all letters to lowercase.

The LOWER function converts all letters in the string to lowercase, so 'EleCtrOnics' becomes 'electronics'.

📝 Syntax

advanced

Which query correctly converts a column to uppercase?

You want to select the city column from the Customers table and convert all city names to uppercase. Which query is syntactically correct?

A SELECT UPPER(city) FROM Customers;

B SELECT city.UPPER() FROM Customers;

C SELECT UPPER city FROM Customers;

D SELECT city TO UPPER FROM Customers;

💡 Hint

UPPER is a function that takes the column name as an argument inside parentheses.

Option A uses the correct syntax: UPPER(column_name). Other options have syntax errors.

❓ optimization

advanced

Optimizing case-insensitive search using LOWER

You want to find all rows in the Users table where the email column matches '[e-mail]' regardless of case. Which query is the most efficient and correct?

A SELECT * FROM Users WHERE UPPER(email) = '[e-mail]';

B SELECT * FROM Users WHERE email = '[e-mail]';

C SELECT * FROM Users WHERE email LIKE '[e-mail]%';

D SELECT * FROM Users WHERE LOWER(email) = LOWER('[e-mail]');

💡 Hint

Use LOWER on both sides to ensure case-insensitive comparison.

Option D converts both the column and the string to lowercase, ensuring case-insensitive match efficiently. Option D works but only compares to uppercase string, which is less clear. Option D is case-sensitive. Option D uses LIKE with wildcards, which is less efficient.

🧠 Conceptual

expert

Why use UPPER or LOWER in database queries?

Which of the following best explains why you might use UPPER or LOWER functions in SQL queries?

A To ensure case-insensitive comparisons and standardize text data for searching or sorting.

B To encrypt sensitive data before storing it in the database.

C To convert numeric data into string format for calculations.

D To automatically fix spelling mistakes in text columns.

💡 Hint

Think about how text case affects searching and sorting.

UPPER and LOWER are used to standardize text case so that comparisons and sorting are consistent regardless of original letter case. They do not encrypt data, convert numbers, or fix spelling.