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.

Practice

1. What does the SQL function UPPER('Hello World') return?

easy

A. 'Hello World'

B. 'HELLO WORLD'

C. 'hello world'

D. 'HeLLo WoRLD'

Solution

1. Step 1: Understand the UPPER function

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

2. Step 2: Apply UPPER to 'Hello World'

   Converting each letter to uppercase results in 'HELLO WORLD'.

3. Final Answer:

   'HELLO WORLD' -> Option B

4. Quick Check:

   UPPER('Hello World') = 'HELLO WORLD' [OK]

Hint: UPPER makes all letters capital [OK]

Common Mistakes:

• Confusing UPPER with LOWER
• Thinking it changes only first letter
• Assuming it reverses the string

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

easy

A. SELECT LOWER(name) FROM table;

B. SELECT LOWER name FROM table;

C. SELECT name.LOWER() FROM table;

D. SELECT LOWER[name] FROM table;

Solution

1. Step 1: Recall correct function syntax

   The LOWER function is called with parentheses around the column name: LOWER(column_name).

2. Step 2: Check each option

   SELECT LOWER(name) FROM table; uses correct syntax: LOWER(name). Others have syntax errors or invalid notation.

3. Final Answer:

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

4. Quick Check:

   Use LOWER(column) with parentheses [OK]

Hint: Use LOWER(column_name) with parentheses [OK]

Common Mistakes:

• Omitting parentheses
• Using dot notation like name.LOWER()
• Using square brackets inside function

3. Given a table users with a column username containing values: 'Alice', 'BOB', 'charlie', what will the query SELECT LOWER(username) FROM users; return?

medium

A. ['ALICE', 'BOB', 'CHARLIE']

B. ['Alice', 'BOB', 'charlie']

C. ['alice', 'bob', 'charlie']

D. ['aLiCe', 'bOb', 'cHaRlIe']

Solution

1. Step 1: Understand LOWER function effect on each username

   LOWER converts all letters to lowercase regardless of original case.

2. Step 2: Apply LOWER to each username

   'Alice' -> 'alice', 'BOB' -> 'bob', 'charlie' -> 'charlie'.

3. Final Answer:

   ['alice', 'bob', 'charlie'] -> Option C

4. Quick Check:

   LOWER converts all letters to lowercase [OK]

Hint: LOWER makes all letters small [OK]

Common Mistakes:

• Assuming only first letter changes
• Confusing LOWER with UPPER
• Expecting mixed case output

4. Identify the error in this SQL query: SELECT LOWER username FROM users;

medium

A. No error, query is correct

B. LOWER function does not exist

C. Table name is incorrect

D. Missing parentheses around column name

Solution

1. Step 1: Check function syntax

   SQL functions require parentheses around arguments, so LOWER(username) is correct.

2. Step 2: Identify missing parentheses

   The query uses LOWER username without parentheses, causing syntax error.

3. Final Answer:

   Missing parentheses around column name -> Option D

4. Quick Check:

   Functions need parentheses [OK]

Hint: Always use parentheses with functions [OK]

Common Mistakes:

• Omitting parentheses for functions
• Assuming functions work like keywords
• Ignoring syntax errors

5. You want to find all rows in a table products where the category column equals 'Books', ignoring case differences. Which SQL WHERE clause correctly does this?

hard

A. WHERE LOWER(category) = 'books' AND UPPER(category) = 'BOOKS'

B. WHERE category = 'Books'

C. WHERE LOWER(category) = 'Books'

D. WHERE UPPER(category) = 'BOOKS'

Solution

1. Step 1: Understand case-insensitive comparison

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

2. Step 2: Apply LOWER and UPPER to category and compare both

   Using WHERE LOWER(category) = 'books' AND UPPER(category) = 'BOOKS' ensures the category matches 'Books' ignoring case by checking both lowercase and uppercase.

3. Final Answer:

   WHERE LOWER(category) = 'books' AND UPPER(category) = 'BOOKS' -> Option A

4. Quick Check:

   Use both LOWER and UPPER on the column to ensure case-insensitive match [OK]

Hint: Convert both sides to same case for comparison [OK]

Common Mistakes:

• Comparing without case conversion
• Mixing cases in comparison string
• Using only one case function