Product Name Letter Replace

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Amazon

Problem

A lowercase 'e' is nearly unreadable in the storefront's new display font, so the merchandising team wants every name in the products table shown with each lowercase 'e' as a capital 'E' and nothing else changed. Return each product ID with its reformatted name, in product ID order.

A quick text transform on product names.

Tables

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

This is a scoped, case-sensitive character swap dressed up as catalog reformatting. Anyone can find a string function; what separates candidates is reading the ask literally: only the lowercase 'e' changes, and the capital 'E' already leading 'Essential' and 'Eco' stays exactly as it is. Reach for UPPER(product_name) and 'Basic Device 1X' comes back as 'BASIC DEVICE 1X' instead of 'Basic DEvicE 1X': every row is wrong while the query looks finished.

Getting to the answer

Step 1: Swap one character, not the whole string

REPLACE(product_name, 'e', 'E') matches the exact character 'e' and nothing else. It is case-sensitive, so the capital in 'Essential Set 5X' is never a match and passes through, while the two lowercase ones turn it into 'EssEntial SEt 5X'.

Step 2: Keep every row

Nothing is filtered. A name with no 'e', like 'Pro Unit 3X', still belongs in the catalog and comes back unchanged. A WHERE product_name LIKE '%e%' would quietly drop it and shrink the result.

Step 3: Sort by the returned product_id

The catalog is read in ID order, and product_id is unique, so ORDER BY product_id fixes one sequence. Because it is also a returned column, the grader can check the order directly.

SELECT product_id, REPLACE(product_name, 'e', 'E') AS modified_name
FROM products
ORDER BY product_id

Uppercase the value

UPPER(product_name) turns 'Basic Device 1X' into 'BASIC DEVICE 1X'. Every letter changes, so no row matches.

Swap only the 'e'

REPLACE(product_name, 'e', 'E') turns it into 'Basic DEvicE 1X'. Every other character, existing capitals included, is untouched.

Common follow-up questions

• How would you capitalize both 'e' and 'a' in one expression? (Nesting two `REPLACE` calls, and whether the order of nesting matters here.)
• What if only the first lowercase 'e' in each name should change? (Locating a character's position instead of swapping every occurrence.)
• If `product_name` could be `NULL`, what would those rows return? (String functions pass `NULL` through; whether to show a default is a product decision.)