Concatenate the Name and the Profession - Problem

You are working with a Person database table that stores information about various professionals. Your task is to create a formatted report that displays each person's name followed by the first letter of their profession in parentheses.

Table Schema:

Person
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| person_id   | int     |
| name        | varchar |
| profession  | ENUM    |
+-------------+---------+

The profession column contains one of these values: 'Doctor', 'Singer', 'Actor', 'Player', 'Engineer', or 'Lawyer'

Your Goal: Write a SQL query that returns each person's name concatenated with the first letter of their profession enclosed in parentheses. The results should be ordered by person_id in descending order.

Example: If someone is named "John" and their profession is "Doctor", the output should be "John(D)".

Input & Output

Basic Example
$ Input: Person table: +----------+-------+------------+ | person_id| name | profession | +----------+-------+------------+ | 1 | Alex | Singer | | 3 | Alice | Actor | | 2 | Bob | Player | +----------+-------+------------+
› Output: +----------------+ | formatted_name | +----------------+ | Alice(A) | | Bob(P) | | Alex(S) | +----------------+
💡 Note: The result shows each person's name followed by the first letter of their profession in parentheses. Results are ordered by person_id in descending order (3, 2, 1).
All Professions Example
$ Input: Person table: +----------+--------+------------+ | person_id| name | profession | +----------+--------+------------+ | 1 | John | Doctor | | 2 | Mary | Engineer | | 3 | Sam | Lawyer | | 4 | Lisa | Actor | | 5 | Tom | Player | | 6 | Emma | Singer | +----------+--------+------------+
› Output: +----------------+ | formatted_name | +----------------+ | Emma(S) | | Tom(P) | | Lisa(A) | | Sam(L) | | Mary(E) | | John(D) | +----------------+
💡 Note: Shows all possible profession types and their corresponding first letters. Results ordered by person_id DESC (6,5,4,3,2,1).
Single Person Edge Case
$ Input: Person table: +----------+-------+------------+ | person_id| name | profession | +----------+-------+------------+ | 100 | Alice | Doctor | +----------+-------+------------+
› Output: +----------------+ | formatted_name | +----------------+ | Alice(D) | +----------------+
💡 Note: Even with a single row, the formatting works correctly, showing 'Alice(D)' for Alice the Doctor.

Visualization

Tap to expand
Professional Name Tag GeneratorDatabase TableID: 3, Alice, ActorID: 2, Bob, PlayerID: 1, Alex, SingerExtract & FormatActor → APlayer → PSinger → SName Tags (DESC)Alice(A)Bob(P)Alex(S)SQL Query BreakdownSELECT CONCAT(name, '(', SUBSTRING(profession, 1, 1), ')') AS formatted_nameFROM PersonORDER BY person_id DESC;🔑 Key: CONCAT for joining strings, SUBSTRING for first letter, DESC for reverse order
Understanding the Visualization
1
Read Person Data
Scan through each person's record in the database
2
Extract First Letter
Take the first character of each profession (D, E, L, A, P, S)
3
Format Name Tag
Combine name + '(' + profession_letter + ')'
4
Sort by ID DESC
Arrange results by person_id in descending order
Key Takeaway
🎯 Key Insight: SQL string functions make it easy to format data in a single query operation, combining concatenation and substring extraction efficiently.

Time & Space Complexity

Time Complexity
⏱️
O(n)

Linear scan through all rows in the table

n
2n
✓ Linear Growth
Space Complexity
O(1)

No additional space needed beyond result set

n
2n
✓ Linear Space

Related Problems

Constraints

  • 1 ≤ person_id ≤ 1000
  • name contains only English letters and spaces
  • profession is one of: 'Doctor', 'Singer', 'Actor', 'Player', 'Engineer', 'Lawyer'
  • Results must be ordered by person_id DESC
Asked in
Amazon 25 Microsoft 18 Oracle 35 IBM 22
23.4K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen