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.

Practice

1. What does the MySQL function UPPER('hello') return?

easy

A. 'heLLo'

B. 'hello'

C. 'Hello'

D. 'HELLO'

Solution

1. Step 1: Understand the UPPER function

   The UPPER function converts all letters in the input text to uppercase letters.

2. Step 2: Apply UPPER to 'hello'

   Applying UPPER to 'hello' changes all letters to uppercase, resulting in 'HELLO'.

3. Final Answer:

   'HELLO' -> Option D

4. Quick Check:

   UPPER('hello') = 'HELLO' [OK]

Hint: UPPER makes all letters capital [OK]

Common Mistakes:

• Thinking UPPER only capitalizes first letter
• Confusing UPPER with LOWER
• Expecting mixed case output

2. Which of the following is the correct syntax to convert the column name to lowercase in a SELECT query?

easy

A. SELECT LOWER(name) FROM users;

B. SELECT LOWER name FROM users;

C. SELECT name.LOWER() FROM users;

D. SELECT LOWER[name] FROM users;

Solution

1. Step 1: Recall correct function syntax

   MySQL functions use the format FUNCTION_NAME(argument), so LOWER(name) is correct.

2. Step 2: Check each option

   SELECT LOWER(name) FROM users; uses correct syntax: LOWER(name). Options A, B, and D use invalid syntax forms.

3. Final Answer:

   SELECT LOWER(name) FROM users; -> Option A

4. Quick Check:

   Correct function call syntax = SELECT LOWER(name) FROM users; [OK]

Hint: Use function_name(column) syntax for MySQL functions [OK]

Common Mistakes:

• Omitting parentheses around column
• Using dot or bracket notation incorrectly
• Writing function name in lowercase without parentheses

3. Given a table products with a column category containing values 'Books', 'electronics', 'TOYS', what will the query SELECT DISTINCT UPPER(category) FROM products; return?

medium

A. ['Books', 'electronics', 'TOYS']

B. ['BOOKS', 'ELECTRONICS', 'TOYS']

C. ['books', 'electronics', 'toys']

D. ['BOOKS', 'electronics', 'TOYS']

Solution

1. Step 1: Understand UPPER and DISTINCT

   UPPER converts all text to uppercase. DISTINCT removes duplicates from the result set.

2. Step 2: Apply UPPER to each category value

   'Books' -> 'BOOKS', 'electronics' -> 'ELECTRONICS', 'TOYS' -> 'TOYS'. All become uppercase.

3. Step 3: Collect distinct uppercase values

   All three are unique after conversion, so the result is ['BOOKS', 'ELECTRONICS', 'TOYS'].

4. Final Answer:

   ['BOOKS', 'ELECTRONICS', 'TOYS'] -> Option B

5. Quick Check:

   UPPER + DISTINCT = uppercase unique values [OK]

Hint: UPPER changes all letters; DISTINCT removes duplicates [OK]

Common Mistakes:

• Expecting original case values
• Ignoring DISTINCT effect
• Mixing uppercase and lowercase in output

4. Consider the query: SELECT LOWER('MySQL') FROM dual. It returns an error. What is the likely cause?

medium

A. The table 'dual' does not exist in MySQL by default

B. LOWER function is not supported in MySQL

C. The string 'MySQL' must be in uppercase already

D. Missing semicolon at the end of the query

Solution

1. Step 1: Understand the use of 'dual'

   'dual' is a special table in Oracle but not present by default in MySQL.

2. Step 2: Check MySQL behavior

   MySQL does not require 'FROM dual' for selecting expressions; using it causes an error if 'dual' table is missing.

3. Final Answer:

   The table 'dual' does not exist in MySQL by default -> Option A

4. Quick Check:

   MySQL lacks default 'dual' table [OK]

Hint: MySQL doesn't need 'FROM dual' for expressions [OK]

Common Mistakes:

• Assuming 'dual' exists like in Oracle
• Thinking LOWER is unsupported
• Blaming missing semicolon

5. You want to find all users in the users table whose email ends with '@example.com', ignoring case differences. Which query correctly uses UPPER or LOWER to achieve this?

hard

A. SELECT * FROM users WHERE LOWER(email) LIKE '[e-mail]';

B. SELECT * FROM users WHERE LOWER(email) LIKE '[e-mail]';

C. SELECT * FROM users WHERE UPPER(email) LIKE '[e-mail]';

D. SELECT * FROM users WHERE email LIKE '[e-mail]';

Solution

1. Step 1: Understand case-insensitive matching

   To ignore case, convert both sides to the same case before comparing.

2. Step 2: Apply UPPER to email and pattern

   Using UPPER(email) and matching with uppercase pattern '[e-mail]' ensures case-insensitive match.

3. Step 3: Check options

   SELECT * FROM users WHERE UPPER(email) LIKE '[e-mail]'; correctly uses UPPER on email and uppercase pattern. SELECT * FROM users WHERE LOWER(email) LIKE '[e-mail]'; mismatches case in pattern. SELECT * FROM users WHERE email LIKE '[e-mail]'; is case-sensitive. SELECT * FROM users WHERE LOWER(email) LIKE '[e-mail]'; mismatches case in pattern.

4. Final Answer:

   SELECT * FROM users WHERE UPPER(email) LIKE '[e-mail]'; -> Option C

5. Quick Check:

   Match cases on both sides using UPPER or LOWER [OK]

Hint: Match cases on both sides with UPPER or LOWER [OK]

Common Mistakes:

• Not matching case of pattern and column
• Assuming LIKE is case-insensitive by default
• Mixing LOWER with uppercase pattern