Vegan and Gluten-Free Bakes

A neighbourhood bakery is setting up a special shelf for customers with strict diets. An item may go on the shelf only if it is both vegan and gluten-free. Each flag is stored as the text 'Y' or 'N'.

Return two columns, item_id and item_name, for every item that qualifies, in any order. Items that meet only one of the two conditions do not qualify. If nothing qualifies, return no rows.

Tables

bakery_items — One row per item the bakery sells.

Example

bakery_items

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Select & Filter · problem 8 of 70 · SQLite

The idea

Each row describes one item, so the question is a pure row filter: keep rows where is_vegan = 'Y' and is_gluten_free = 'Y'. AND requires both tests to be true for the same row, which is exactly the rule "vegan and gluten-free".

Be explicit about the flag encoding. In databases with a real boolean type you might write WHERE is_vegan AND is_gluten_free, but here the flags are text. SQLite converts 'Y' to the number 0 in a boolean context, so that shortcut silently returns nothing. Comparing against the literal is clear and correct.

The alternative turns the rule around: an item qualifies when neither flag is 'N', written as 'N' NOT IN (is_vegan, is_gluten_free). That works here because both columns are NOT NULL and only hold 'Y' or 'N'. With NULLs present, NOT IN would need extra care.

Solution

SELECT item_id,
       item_name
FROM bakery_items
WHERE is_vegan = 'Y'
  AND is_gluten_free = 'Y';

Another way

SELECT item_id,
       item_name
FROM bakery_items
WHERE 'N' NOT IN (is_vegan, is_gluten_free);

Why common answers fail

Uses OR, so items that satisfy only one dietary rule end up on the shelf.

SELECT item_id, item_name FROM bakery_items WHERE is_vegan = 'Y' OR is_gluten_free = 'Y';

Treats the text flags as booleans; in SQLite the text 'Y' converts to 0 (false), so no item is ever returned.

SELECT item_id, item_name FROM bakery_items WHERE is_vegan AND is_gluten_free;

Compares with lowercase 'y'; SQLite compares text case-sensitively by default, so nothing matches.

SELECT item_id, item_name FROM bakery_items WHERE is_vegan = 'y' AND is_gluten_free = 'y';

What the interviewer is checking

Checks that you combine conditions with the right connective and pay attention to how flags are actually stored.

Mistakes to avoid

• Using OR instead of AND.
• Treating 'Y'/'N' text flags as booleans.
• Getting the letter case of the flag value wrong.

Follow-ups to expect

• Count how many items fall into each of the four flag combinations. (GROUP BY is_vegan, is_gluten_free)
• Add a third flag, nut-free, to the rule. (one more AND condition)
• List items that satisfy exactly one rule. (is_vegan <> is_gluten_free)

In real work

Product catalogues filter on several attribute flags at once, for example "in stock and shippable" or "organic and local".

Practice next