First Impressions

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

Oracle

Problem

Only the first three characters of a product's name fit in the compact listing's label slot. From the products table, return each product's ID alongside that three-character prefix, in product ID order.

Just the first three characters. That is all.

Tables

products

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

Expected output

Worked solution and explanation

What this really tests

Let's be honest: there is no algorithmic trap here. This is a one-line string extraction wearing a product-analytics costume, and anyone who knows SUBSTR can write it. What an interviewer actually clocks on a warm-up like this is whether you get the argument order right on the first pass and whether you remember that SQL string indexing starts at 1, not 0. Reach for a zero or a two as the start position and you hand back the wrong characters; drop product_id from the SELECT and the shape is wrong. On an easy question, a clean first attempt is the entire signal.

Break it down

Step 1: Extract the first three characters

SUBSTR(product_name, 1, 3) takes three characters starting at position 1. The 1 is the part people fluff: SQL is 1-indexed, so 1 gives you the first character, not the second. Alias it to name_prefix so the output column matches what was asked.

Step 2: Order by product_id

ORDER BY product_id returns the rows in ascending ID order. It is the only ordering requirement, and forgetting it leaves the row order at the engine's discretion, which will not match the expected output.

The solution

SELECT product_id, SUBSTR(product_name, 1, 3) AS name_prefix
FROM products
ORDER BY product_id

Common follow-up questions

• What does your query return for a product whose name is shorter than three characters, and how would you pad it to a fixed width if the business required exactly three? (Tests whether the candidate knows `SUBSTR` degrades gracefully rather than erroring, and can reason about padding.)
• How would you change the query to take the last three characters of the name instead of the first three? (Tests flexibility with `SUBSTR` positioning, including negative or computed start positions.)
• If product names followed a 'brand-model' convention, how would you extract just the segment after the hyphen? (Tests combining string functions to locate and slice on a delimiter.)