UPPER, LOWER, INITCAP in PostgreSQL - Mini Project: Build & Apply

Using UPPER, LOWER, and INITCAP Functions in PostgreSQL

📖 Scenario: You are managing a customer database for a small online store. The customer names are stored in mixed cases, and you want to standardize the display of these names in reports.

🎯 Goal: Learn how to use the PostgreSQL string functions UPPER, LOWER, and INITCAP to format customer names properly.

📋 What You'll Learn

Create a table called customers with columns id and name.

Insert specific customer names with mixed letter cases.

Write queries to display customer names in all uppercase, all lowercase, and capitalized (first letter uppercase, rest lowercase).

Use the exact function names UPPER, LOWER, and INITCAP.

💡 Why This Matters

🌍 Real World

Standardizing text data like customer names helps keep reports and user interfaces consistent and professional.

💼 Career

Database developers and analysts often need to format text data for better readability and presentation in reports and applications.

1

Create the customers table and insert data

Create a table called customers with columns id (integer) and name (text). Then insert these exact rows: (1, 'alice JOHNSON'), (2, 'Bob smith'), (3, 'CHARLIE brown').

PostgreSQL

-- Create the customers table and insert the given rows
-- Your code here

Need a hint?

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

2

Select names in uppercase

Write a SELECT query to get the id and the name converted to uppercase using the UPPER function. Name the uppercase column name_upper.

PostgreSQL

CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers (id, name) VALUES
(1, 'alice JOHNSON'),
(2, 'Bob smith'),
(3, 'CHARLIE brown');

-- Select id and name in uppercase as name_upper
-- Your code here

Need a hint?

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

3

Select names in lowercase

Write a SELECT query to get the id and the name converted to lowercase using the LOWER function. Name the lowercase column name_lower.

PostgreSQL

CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers (id, name) VALUES
(1, 'alice JOHNSON'),
(2, 'Bob smith'),
(3, 'CHARLIE brown');

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

-- Select id and name in lowercase as name_lower
-- Your code here

Need a hint?

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

4

Select names with initial capitals

Write a SELECT query to get the id and the name converted to capitalized form using the INITCAP function. Name the capitalized column name_initcap.

PostgreSQL

CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers (id, name) VALUES
(1, 'alice JOHNSON'),
(2, 'Bob smith'),
(3, 'CHARLIE brown');

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

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

-- Select id and name with initial capitals as name_initcap
-- Your code here

Need a hint?

Use INITCAP(name) in the SELECT clause and alias it as name_initcap.