Library Cards Expiring in March 2025 — SQL Strings Problem
The library mails a renewal reminder to every member whose card expires during March 2025.
- Difficulty: Easy
- Topics: Strings, Dates
- Dialect: MySQL
- Problem: #79
Problem statement
The library mails a renewal reminder to every member whose card expires during March 2025. Members store their first and last names separately; some members gave no last name (NULL).
Return member_id, member_name — the first name, a single space and the last name, or just the first name when the last name is NULL — and card_expires_on, ordered by card_expires_on, then member_id.
Tables
Table: library_members
| Column | Type |
|---|---|
| member_id | int |
| first_name | varchar |
| last_name | varchar |
| branch | varchar |
| card_expires_on | date |
Primary key: member_id.
One row per card holder.
Examples
Example 1
library_members
| member_id | first_name | last_name | branch | card_expires_on |
|---|---|---|---|---|
| 1 | Kavya | Iyer | Jayanagar | 2025-03-14 |
| 2 | Harsh | NULL | Central | 2025-03-01 |
| 3 | Tanvi | Reddy | Central | 2025-02-28 |
| 4 | Nikhil | Verma | Malleshwaram | 2025-03-31 |
| 5 | Zara | Khan | Indiranagar | 2025-04-01 |
| 6 | Ishaan | Das | Jayanagar | 2025-03-14 |
Output
| member_id | member_name | card_expires_on |
|---|---|---|
| 2 | Harsh | 2025-03-01 |
| 1 | Kavya Iyer | 2025-03-14 |
| 6 | Ishaan Das | 2025-03-14 |
| 4 | Nikhil Verma | 2025-03-31 |
How to solve Library Cards Expiring in March 2025
Two small ideas meet here. The date filter is a closed range: BETWEEN '2025-03-01' AND '2025-03-31' includes both ends, which is right for a month — the 1st and the 31st both count, and 28 February and 1 April do not. YEAR(...) = 2025 AND MONTH(...) = 3 or DATE_FORMAT(..., '%Y-%m') = '2025-03' say the same, though the range form can use an index on the date column and the function forms cannot.
The name is the trap. CONCAT(first_name, ' ', last_name) is NULL for a member with no last name, because CONCAT propagates NULL. CONCAT_WS(' ', …) skips NULL arguments and puts the separator only between the values present, which gives just the first name. Wrapping the last name in COALESCE(…, '') and trimming the trailing space, or choosing with IF, are the other ways.
Two cards can expire on the same day, so the order adds member_id.
Reference solution (MySQL)
SELECT member_id, CONCAT_WS(' ', first_name, last_name) AS member_name, card_expires_on
FROM library_members
WHERE card_expires_on BETWEEN '2025-03-01' AND '2025-03-31'
ORDER BY card_expires_on, member_idAnother way
SELECT member_id, IF(last_name IS NULL, first_name, CONCAT(first_name, ' ', last_name)) AS member_name, card_expires_on FROM library_members WHERE YEAR(card_expires_on) = 2025 AND MONTH(card_expires_on) = 3 ORDER BY card_expires_on, member_idAnother way
SELECT member_id, TRIM(CONCAT(first_name, ' ', COALESCE(last_name, ''))) AS member_name, card_expires_on FROM library_members WHERE DATE_FORMAT(card_expires_on, '%Y-%m') = '2025-03' ORDER BY card_expires_on, member_id← Box Office Revenue per Show · Cash Donations and 80G Eligibility →