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

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

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

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

Hint

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

Practice

1. What does the UPPER function do to a text string in PostgreSQL?

easy

A. Reverses the string

B. Converts all letters to lowercase

C. Capitalizes only the first letter of the string

D. Converts all letters to uppercase

Solution

1. Step 1: Understand the UPPER function

   The UPPER function changes every letter in the input text to its uppercase form.

2. Step 2: Compare with other options

   LOWER converts to lowercase, INITCAP capitalizes first letters, reversing is unrelated.

3. Final Answer:

   Converts all letters to uppercase -> Option D

4. Quick Check:

   UPPER = all uppercase [OK]

Hint: UPPER means all letters become capital [OK]

Common Mistakes:

• Confusing UPPER with LOWER
• Thinking INITCAP only changes first letter of whole string
• Assuming UPPER reverses text

2. Which of the following is the correct syntax to convert the column name to lowercase in PostgreSQL?

easy

A. SELECT LOWER name FROM table;

B. SELECT LOWER(name);

C. SELECT LOWER(name) FROM table;

D. SELECT name.LOWER() FROM table;

Solution

1. Step 1: Check function call syntax

   In PostgreSQL, functions are called with parentheses around arguments, e.g., LOWER(name).

2. Step 2: Validate full query syntax

   SELECT LOWER(name) FROM table; correctly uses SELECT LOWER(name) FROM table; which is valid SQL syntax.

3. Final Answer:

   SELECT LOWER(name) FROM table; -> Option C

4. Quick Check:

   Function call syntax = LOWER(name) [OK]

Hint: Use parentheses for functions: LOWER(column) [OK]

Common Mistakes:

• Omitting parentheses after function name
• Using dot notation like name.LOWER() which is invalid
• Missing FROM clause in query

3. Given a table users with a column fullname containing 'john DOE', what is the output of the query SELECT INITCAP(fullname) FROM users;?

medium

A. 'John Doe'

B. 'John DOE'

C. 'John doe'

D. 'JOHN DOE'

Solution

1. Step 1: Understand INITCAP behavior

   INITCAP capitalizes the first letter of each word and makes the rest lowercase.

2. Step 2: Apply to 'john DOE'

   It converts 'john' to 'John' and 'DOE' to 'Doe'.

3. Final Answer:

   'John Doe' -> Option A

4. Quick Check:

   INITCAP('john DOE') = 'John Doe' [OK]

Hint: INITCAP capitalizes first letter of each word [OK]

Common Mistakes:

• Assuming INITCAP only changes first word
• Thinking it keeps uppercase letters as is
• Confusing with UPPER or LOWER functions

4. Identify the error in this query: SELECT INITCAP fullname FROM employees;

medium

A. Column name should be uppercase

B. Missing parentheses around column name

C. Missing FROM clause

D. INITCAP is not a valid function

Solution

1. Step 1: Check function syntax

   Functions require parentheses around their arguments, so INITCAP should be called as INITCAP(fullname).

2. Step 2: Verify other parts

   FROM clause is present, INITCAP is valid, column case does not matter.

3. Final Answer:

   Missing parentheses around column name -> Option B

4. Quick Check:

   Function call needs parentheses [OK]

Hint: Always use parentheses with functions [OK]

Common Mistakes:

• Omitting parentheses for function calls
• Thinking column names must be uppercase
• Confusing missing FROM clause error

5. You want to display a list of product names from the products table where each word in the product name starts with a capital letter, but the rest of the letters are lowercase. Which query achieves this?

hard

A. SELECT INITCAP(product_name) FROM products;

B. SELECT UPPER(product_name) FROM products;

C. SELECT LOWER(product_name) FROM products;

D. SELECT CONCAT(UPPER(SUBSTRING(product_name,1,1)), LOWER(SUBSTRING(product_name,2))) FROM products;

Solution

1. Step 1: Understand the requirement

   Each word's first letter capitalized and rest lowercase matches INITCAP behavior.

2. Step 2: Evaluate options

   The INITCAP option does exactly this. The UPPER option makes all uppercase, the LOWER option makes all lowercase, the CONCAT option only capitalizes first letter of whole string.

3. Final Answer:

   SELECT INITCAP(product_name) FROM products; -> Option A

4. Quick Check:

   INITCAP = capitalized words [OK]

Hint: Use INITCAP for capitalizing each word [OK]

Common Mistakes:

• Using UPPER or LOWER instead of INITCAP
• Trying to manually concatenate substrings for each word
• Assuming INITCAP only affects first word