Museum Guide Holiday Bonus

A history museum pays its tour guides a one-off holiday bonus. A guide receives a bonus equal to their weekly_fee when both conditions hold:

• guide_id is an odd number, and
• guide_name does not start with the letter R, upper or lower case.

Every other guide gets a bonus of 0. Return one row per guide with the columns guide_id and holiday_bonus, sorted by guide_id ascending. An empty table gives no rows.

Tables

tour_guides — One row per tour guide on the roster.

Example

tour_guides

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Strings & CASE · problem 53 of 70 · SQLite

The idea

Every guide must appear, so the rule cannot filter rows; it has to compute a value. A CASE returns weekly_fee when both conditions hold and 0 otherwise.

The odd test is guide_id % 2 = 1. For the name, NOT LIKE 'R%' checks only the first character because the % wildcard comes after the R. In SQLite, LIKE is case-insensitive for ASCII letters, which is exactly what the statement asks for. GLOB would be case-sensitive and miss names like ruth.

The output order is part of the answer, so finish with ORDER BY guide_id. Without it, rows come back in whatever order the engine reads them, which happens to look sorted on small tables and breaks on real ones. The alternative spells out the case handling with upper(substr(guide_name, 1, 1)) and uses the compact iif.

Solution

SELECT guide_id,
       CASE
         WHEN guide_id % 2 = 1 AND guide_name NOT LIKE 'R%' THEN weekly_fee
         ELSE 0
       END AS holiday_bonus
FROM tour_guides
ORDER BY guide_id;

Another way

SELECT guide_id,
       iif(guide_id % 2 = 1 AND upper(substr(guide_name, 1, 1)) <> 'R', weekly_fee, 0) AS holiday_bonus
FROM tour_guides
ORDER BY guide_id;

Why common answers fail

GLOB is case-sensitive, so names entered in lower case such as 'ruth' still get a bonus.

SELECT guide_id, CASE WHEN guide_id % 2 = 1 AND guide_name NOT GLOB 'R*' THEN weekly_fee ELSE 0 END AS holiday_bonus FROM tour_guides ORDER BY guide_id;

'%R%' excludes names that contain an R anywhere, not just at the start.

SELECT guide_id, CASE WHEN guide_id % 2 = 1 AND guide_name NOT LIKE '%R%' THEN weekly_fee ELSE 0 END AS holiday_bonus FROM tour_guides ORDER BY guide_id;

Puts the rule in WHERE, so guides who should get 0 are dropped from the list instead of shown with 0.

SELECT guide_id, weekly_fee AS holiday_bonus FROM tour_guides WHERE guide_id % 2 = 1 AND guide_name NOT LIKE 'R%' ORDER BY guide_id;

What the interviewer is checking

A quick check of CASE, modulo and pattern matching, and of whether you remember that row order is never guaranteed without ORDER BY.

Mistakes to avoid

• Filtering guides out with WHERE instead of returning 0 for them.
• Using a case-sensitive test (GLOB or substr = 'R') when both cases must count.
• Assuming rows come back sorted without ORDER BY.

Follow-ups to expect

• Total bonus budget (SUM over the same CASE).
• What if LIKE were case-sensitive, as in PostgreSQL? (Use ILIKE or compare lower(guide_name).)
• Exclude several initial letters (NOT IN on upper(substr(guide_name, 1, 1))).

In real work

Payroll, promotion and eligibility rules are often a handful of conditions computed per row with CASE and returned in a fixed order for review.

Practice next