Faculty Names Formatted for ID Cards — SQL Strings Problem
The college is printing new staff ID cards. A card shows the faculty member's first initial, a full stop and a space, then the surname in capitals — R.
- Difficulty: Easy
- Topics: Strings, Conditional Logic
- Dialect: MySQL
- Problem: #129
Problem statement
The college is printing new staff ID cards. A card shows the faculty member's first initial, a full stop and a space, then the surname in capitals — R. SHARMA — and holders of a PhD get the prefix Dr. — Dr. R. SHARMA.
Return every faculty member with the columns faculty_id and card_name built that way. has_phd is 1 for a PhD holder and 0 otherwise. Order the rows by faculty_id.
Tables
Table: Faculty
| Column | Type |
|---|---|
| faculty_id | int |
| first_name | varchar |
| last_name | varchar |
| designation | enum(Professor, Associate Professor, Assistant Professor, Lecturer) |
| has_phd | bool |
Primary key: faculty_id.
Names are stored with a capital first letter (Rahul, Sharma).
Examples
Example 1
Faculty
| faculty_id | first_name | last_name | designation | has_phd |
|---|---|---|---|---|
| 301 | Rahul | Sharma | Professor | 1 |
| 302 | Ananya | Iyer | Assistant Professor | 0 |
| 303 | Farhan | Khan | Associate Professor | 1 |
| 304 | Ira | Bose | Lecturer | 0 |
| 305 | Tanvi | Menon | Assistant Professor | 1 |
Output
| faculty_id | card_name |
|---|---|
| 301 | Dr. R. SHARMA |
| 302 | A. IYER |
| 303 | Dr. F. KHAN |
| 304 | I. BOSE |
| 305 | Dr. T. MENON |
How to solve Faculty Names Formatted for ID Cards
The card name is a concatenation of pieces, one of which depends on a condition. LEFT(first_name, 1) takes the initial, UPPER(last_name) capitalises the surname, and the literal '. ' sits between them.
The prefix is CASE WHEN has_phd = 1 THEN 'Dr. ' ELSE '' END. The empty string in the ELSE matters: in MySQL CONCAT returns NULL if any argument is NULL, so a CASE without an ELSE would blank out every card for staff without a PhD.
CONCAT_WS behaves the other way round — it skips NULL arguments — so the alternative leaves the prefix NULL for non-PhD staff and lets CONCAT_WS(' ', …) drop it, putting single spaces between the remaining parts. Both are a single pass over the table.
Reference solution (MySQL)
SELECT faculty_id,
CONCAT(CASE WHEN has_phd = 1 THEN 'Dr. ' ELSE '' END, LEFT(first_name, 1), '. ', UPPER(last_name)) AS card_name
FROM Faculty
ORDER BY faculty_idAnother way
SELECT faculty_id, CONCAT(IF(has_phd = 1, 'Dr. ', ''), SUBSTRING(first_name, 1, 1), '. ', UCASE(last_name)) AS card_name FROM Faculty ORDER BY faculty_idAnother way
SELECT faculty_id, CONCAT_WS(' ', CASE WHEN has_phd = 1 THEN 'Dr.' END, CONCAT(LEFT(first_name, 1), '.'), UPPER(last_name)) AS card_name FROM Faculty ORDER BY faculty_id ASC← Exam Marks to University Grade Letters · Students Who Accepted Offers of 10 LPA or More →