Faculty Names Formatted for ID Cards

Easy
129.

Faculty Names Formatted for ID Cards

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

ColumnTypeValues
faculty_idint
first_namevarchar
last_namevarchar
designationenumProfessor, Associate Professor, Assistant Professor, Lecturer
has_phdbool

Primary key: faculty_id.

Names are stored with a capital first letter (Rahul, Sharma).

Example

The rows must come back in the order shown.

Faculty

faculty_idfirst_namelast_namedesignationhas_phd
301RahulSharmaProfessor1
302AnanyaIyerAssistant Professor0
303FarhanKhanAssociate Professor1
304IraBoseLecturer0
305TanviMenonAssistant Professor1

Output

faculty_idcard_name
301Dr. R. SHARMA
302A. IYER
303Dr. F. KHAN
304I. BOSE
305Dr. T. MENON
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
faculty_idint
first_namevarchar
last_namevarchar
designationenum(Professor, Associate Professor, Assistant Professor, Lecturer)
has_phdbool

Primary key: faculty_id.

Names are stored with a capital first letter (Rahul, Sharma).

Examples

Example 1

Faculty

faculty_idfirst_namelast_namedesignationhas_phd
301RahulSharmaProfessor1
302AnanyaIyerAssistant Professor0
303FarhanKhanAssociate Professor1
304IraBoseLecturer0
305TanviMenonAssistant Professor1

Output

faculty_idcard_name
301Dr. R. SHARMA
302A. IYER
303Dr. F. KHAN
304I. BOSE
305Dr. 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_id

Another 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_id

Another 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 →