UPPER and LOWER in MySQL - Mini Project: Build & Apply

Using UPPER and LOWER Functions in SQL

📖 Scenario: You work at a bookstore database. The store wants to standardize how author names are displayed in reports. Some names are in uppercase, some in lowercase, and some mixed. You will use SQL functions to convert all author names to uppercase or lowercase.

🎯 Goal: Create a table of authors, then write queries using UPPER() and LOWER() functions to display author names in uppercase and lowercase.

📋 What You'll Learn

Create a table called authors with columns id (integer) and name (varchar).

Insert three authors with exact names: 'Jane Austen', 'mark twain', 'GEORGE ORWELL'.

Write a query to select all author names converted to uppercase using UPPER().

Write a query to select all author names converted to lowercase using LOWER().

💡 Why This Matters

🌍 Real World

Standardizing text case in databases helps keep reports and user interfaces consistent and professional.

💼 Career

Database developers and analysts often need to clean and format text data using functions like UPPER and LOWER.

1

Create the authors table and insert data

Create a table called authors with columns id as integer and name as varchar(50). Then insert these three rows exactly: (1, 'Jane Austen'), (2, 'mark twain'), and (3, 'GEORGE ORWELL').

MySQL

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

Hint

Use CREATE TABLE to make the table, then INSERT INTO to add rows.

2

Write a query to select author names in uppercase

Write a SQL query to select the name column from authors but convert all names to uppercase using the UPPER() function. Name the output column name_upper.

MySQL

CREATE TABLE authors (
  id INT,
  name VARCHAR(50)
);

INSERT INTO authors (id, name) VALUES
(1, 'Jane Austen'),
(2, 'mark twain'),
(3, 'GEORGE ORWELL');

-- Write a query to select names in uppercase
-- Your code here

Hint

Use SELECT UPPER(name) AS name_upper FROM authors; to convert names to uppercase.

3

Write a query to select author names in lowercase

Write a SQL query to select the name column from authors but convert all names to lowercase using the LOWER() function. Name the output column name_lower.

MySQL

CREATE TABLE authors (
  id INT,
  name VARCHAR(50)
);

INSERT INTO authors (id, name) VALUES
(1, 'Jane Austen'),
(2, 'mark twain'),
(3, 'GEORGE ORWELL');

SELECT UPPER(name) AS name_upper FROM authors;

-- Write a query to select names in lowercase
-- Your code here

Hint

Use SELECT LOWER(name) AS name_lower FROM authors; to convert names to lowercase.

4

Combine uppercase and lowercase queries

Write a SQL query to select the name column from authors and show two columns: one with names in uppercase called name_upper and one with names in lowercase called name_lower. Use UPPER() and LOWER() functions in the same query.

MySQL

CREATE TABLE authors (
  id INT,
  name VARCHAR(50)
);

INSERT INTO authors (id, name) VALUES
(1, 'Jane Austen'),
(2, 'mark twain'),
(3, 'GEORGE ORWELL');

SELECT UPPER(name) AS name_upper FROM authors;

SELECT LOWER(name) AS name_lower FROM authors;

-- Write a query to select names in both uppercase and lowercase columns
-- Your code here

Hint

Use SELECT UPPER(name) AS name_upper, LOWER(name) AS name_lower FROM authors; to show both columns.

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