UPPER and LOWER functions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query using UPPER?

Consider a table Employees with a column first_name containing the value 'Alice'. What will be the result of this query?

SELECT UPPER(first_name) AS upper_name FROM Employees WHERE first_name = 'Alice';

SQL

SELECT UPPER(first_name) AS upper_name FROM Employees WHERE first_name = 'Alice';

A []

B [{"upper_name": "alice"}]

C [{"upper_name": "Alice"}]

D [{"upper_name": "ALICE"}]

💡 Hint

UPPER converts all letters to uppercase.

The UPPER function changes all letters in the string to uppercase. Since the original name is 'Alice', the output is 'ALICE'.

❓ query_result

intermediate

What does LOWER return for mixed case input?

Given a table Products with a column product_code containing 'XyZ123', what is the output of:

SELECT LOWER(product_code) AS lower_code FROM Products WHERE product_code = 'XyZ123';

SQL

SELECT LOWER(product_code) AS lower_code FROM Products WHERE product_code = 'XyZ123';

A []

B [{"lower_code": "xyz123"}]

C [{"lower_code": "XYZ123"}]

D [{"lower_code": "XyZ123"}]

💡 Hint

LOWER converts all letters to lowercase.

LOWER changes all letters to lowercase. The digits remain unchanged. So 'XyZ123' becomes 'xyz123'.

📝 Syntax

advanced

Which query correctly converts names to uppercase?

You want to select all names from the Users table and convert them to uppercase. Which query is syntactically correct?

A SELECT UPPER(name) FROM Users;

B SELECT UPPER(name FROM Users);

C SELECT UPPER name FROM Users;

D SELECT upper(name) FROM Users;

💡 Hint

Function calls require parentheses around arguments.

Option A uses correct syntax with UPPER function and parentheses. Option A misses semicolon but is often accepted; however, standard SQL requires semicolon. Option A misses parentheses. Option A has misplaced parentheses.

❓ optimization

advanced

Which query is more efficient to find names ignoring case?

You want to find all rows where the username is 'admin'. Which query is more efficient on a large table with an index on username?

A SELECT * FROM Users WHERE UPPER(username) = 'ADMIN';

B SELECT * FROM Users WHERE LOWER(username) = 'admin';

C SELECT * FROM Users WHERE username = 'admin';

D SELECT * FROM Users WHERE username ILIKE 'admin';

💡 Hint

Using functions on indexed columns can prevent index use.

Applying LOWER or UPPER on the column disables index use, causing full scan. Direct comparison (option C) uses index if case matches. Option C (ILIKE) is case-insensitive but may not use index depending on DBMS.

🧠 Conceptual

expert

Why might UPPER and LOWER produce unexpected results with accented letters?

In some databases, using UPPER or LOWER on strings with accented characters (like 'é' or 'ß') may not convert as expected. Why does this happen?

A Because the database collation or character set affects how case conversion handles accented letters.

B Because accented letters are always uppercase and cannot be changed.

C Because UPPER and LOWER only work on ASCII letters and ignore Unicode characters.

D Because UPPER and LOWER functions are deprecated and do not support special characters.

💡 Hint

Think about how databases handle language and character encoding.

Case conversion depends on collation and character set settings. Some collations treat accented letters differently, affecting UPPER and LOWER results.