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

Need a 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

Need a 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

Need a 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

Need a hint?

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