UPPER and LOWER functions in SQL - Step-by-Step Execution

Concept Flow - UPPER and LOWER functions

Input String

↓

Apply UPPER or LOWER

↓

Return Modified String

↓

Use in Query Result

The input string is taken, converted to all uppercase or lowercase letters, then returned for use in query results.

Execution Sample

SQL

SELECT UPPER('Hello World') AS upper_text,
       LOWER('Hello World') AS lower_text;

This query converts the string 'Hello World' to uppercase and lowercase.

Execution Table

Step	Function	Input	Output
1	UPPER	'Hello World'	'HELLO WORLD'
2	LOWER	'Hello World'	'hello world'
3	Query Result	N/A	upper_text = 'HELLO WORLD', lower_text = 'hello world'

💡 Both functions processed the input string and returned the converted strings.

Variable Tracker

Variable	Start	After UPPER	After LOWER	Final
input_string	'Hello World'	'Hello World'	'Hello World'	'Hello World'
upper_text	NULL	'HELLO WORLD'	'HELLO WORLD'	'HELLO WORLD'
lower_text	NULL	NULL	'hello world'	'hello world'

Key Moments - 2 Insights

Why does UPPER('Hello World') return 'HELLO WORLD' and not change numbers or symbols?

UPPER only changes alphabetic letters to uppercase. Numbers and symbols stay the same, as shown in execution_table step 1.

Does LOWER('Hello World') affect spaces or punctuation?

No, LOWER only changes letters to lowercase. Spaces and punctuation remain unchanged, as seen in execution_table step 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output of UPPER('Hello World') at step 1?

A 'Hello World'

B 'hello world'

C 'HELLO WORLD'

D 'HELLO world'

💡 Hint

Check the Output column in execution_table row with Step 1.

At which step does LOWER function produce 'hello world'?

A Step 2

B Step 3

C Step 1

D No step

💡 Hint

Look at the Function and Output columns in execution_table.

If the input string was 'SQL123!', what would UPPER('SQL123!') return?

A 'sql123!'

B 'SQL123!'

C 'SQL123!' with numbers changed

D 'sql123!' with symbols changed

💡 Hint

Recall from key_moments that numbers and symbols do not change.

Concept Snapshot

UPPER(string) converts all letters to uppercase.
LOWER(string) converts all letters to lowercase.
Non-letter characters stay unchanged.
Use in SELECT queries to format text output.
Example: SELECT UPPER('abc') returns 'ABC'.

Full Transcript

The UPPER and LOWER functions in SQL take a text string and convert all alphabetic characters to uppercase or lowercase respectively. Non-letter characters like numbers, spaces, and punctuation remain unchanged. For example, UPPER('Hello World') returns 'HELLO WORLD', and LOWER('Hello World') returns 'hello world'. These functions are useful to standardize text data in queries. The execution table shows each step: applying UPPER and LOWER to the input string and the final query result. Beginners often wonder why numbers or symbols do not change; this is because these functions only affect letters. The visual quiz tests understanding of outputs at each step and behavior with different inputs.

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