Library Cards Expiring in March 2025

Easy
79.

Library Cards Expiring in March 2025

EasyStringsDates

MySQL

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

ColumnType
member_idint
first_namevarchar
last_namevarchar
branchvarchar
card_expires_ondate

Primary key: member_id.

One row per card holder.

Example

The rows must come back in the order shown.

library_members

member_idfirst_namelast_namebranchcard_expires_on
1KavyaIyerJayanagar2025-03-14
2HarshNULLCentral2025-03-01
3TanviReddyCentral2025-02-28
4NikhilVermaMalleshwaram2025-03-31
5ZaraKhanIndiranagar2025-04-01
6IshaanDasJayanagar2025-03-14

Output

member_idmember_namecard_expires_on
2Harsh2025-03-01
1Kavya Iyer2025-03-14
6Ishaan Das2025-03-14
4Nikhil Verma2025-03-31
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.

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

ColumnType
member_idint
first_namevarchar
last_namevarchar
branchvarchar
card_expires_ondate

Primary key: member_id.

One row per card holder.

Examples

Example 1

library_members

member_idfirst_namelast_namebranchcard_expires_on
1KavyaIyerJayanagar2025-03-14
2HarshNULLCentral2025-03-01
3TanviReddyCentral2025-02-28
4NikhilVermaMalleshwaram2025-03-31
5ZaraKhanIndiranagar2025-04-01
6IshaanDasJayanagar2025-03-14

Output

member_idmember_namecard_expires_on
2Harsh2025-03-01
1Kavya Iyer2025-03-14
6Ishaan Das2025-03-14
4Nikhil Verma2025-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_id

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

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