UPPER and LOWER functions in SQL - Mini Project: Build & Apply

Using UPPER and LOWER Functions in SQL

📖 Scenario: You are managing a small customer database for a local bookstore. The customer names are stored in mixed case, but you want to standardize the display of names in reports.

🎯 Goal: Build SQL queries that use the UPPER and LOWER functions to convert customer names to all uppercase and all lowercase letters.

📋 What You'll Learn

Create a table called customers with columns id (integer) and name (text).

Insert three customers with names in mixed case: 'Alice', 'bOb', 'CHARLie'.

Write a query to select all customer names converted to uppercase using the UPPER function.

Write a query to select all customer names converted to lowercase using the LOWER function.

💡 Why This Matters

🌍 Real World

Standardizing text data for reports and searches is common in databases to ensure consistent display and comparisons.

💼 Career

Database developers and analysts often use text functions like UPPER and LOWER to clean and format data for applications and reports.

1

Create the customers table and insert data

Write SQL statements to create a table called customers with columns id (integer) and name (text). Then insert these exact rows: (1, 'Alice'), (2, 'bOb'), and (3, 'CHARLie').

SQL

-- Create the customers table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Add a query to select names in uppercase

Write a SQL query that selects the id and the name converted to uppercase using the UPPER function. Name the uppercase column name_upper.

SQL

CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers (id, name) VALUES (1, 'Alice');
INSERT INTO customers (id, name) VALUES (2, 'bOb');
INSERT INTO customers (id, name) VALUES (3, 'CHARLie');

-- Write a query to select id and name in uppercase as name_upper
-- Your code here

Hint

Use UPPER(name) in the SELECT clause and give it an alias name_upper.

3

Add a query to select names in lowercase

Write a SQL query that selects the id and the name converted to lowercase using the LOWER function. Name the lowercase column name_lower.

SQL

CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers (id, name) VALUES (1, 'Alice');
INSERT INTO customers (id, name) VALUES (2, 'bOb');
INSERT INTO customers (id, name) VALUES (3, 'CHARLie');

SELECT id, UPPER(name) AS name_upper FROM customers;

-- Write a query to select id and name in lowercase as name_lower
-- Your code here

Hint

Use LOWER(name) in the SELECT clause and give it an alias name_lower.

4

Combine uppercase and lowercase queries

Write a SQL query that selects the id, the name converted to uppercase as name_upper, and the name converted to lowercase as name_lower all in one query from the customers table.

SQL

CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers (id, name) VALUES (1, 'Alice');
INSERT INTO customers (id, name) VALUES (2, 'bOb');
INSERT INTO customers (id, name) VALUES (3, 'CHARLie');

SELECT id, UPPER(name) AS name_upper FROM customers;

SELECT id, LOWER(name) AS name_lower FROM customers;

-- Write a query to select id, name_upper, and name_lower in one query
-- Your code here

Hint

Use both UPPER(name) and LOWER(name) in the same SELECT statement with aliases.

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