Is the Playing XI Combination Valid? — SQL Easy Problem

Problem statement

Before each league match the captain submits a plan for the playing XI, saying how many players of each role it uses. A plan is valid only when all three rules hold:

• it has exactly 11 players in total;
• it has at least one wicket-keeper;
• at least 5 players can bowl — bowlers and all-rounders together.

Return plan_id and a column is_valid holding 'Yes' for a valid plan and 'No' otherwise, one row per plan, in any order.

Tables

Table: SquadPlan

Primary key: plan_id.

Each column after plan_id is how many players of that role the plan picks. No column is ever NULL.

Examples

Example 1

SquadPlan

Output

How to solve Is the Playing XI Combination Valid?

Each plan is checked on its own, so the answer is a CASE expression evaluated once per row: when all three rules hold, 'Yes', otherwise 'No'.

Write the rules exactly as the statement gives them and join them with AND:

• batters + keepers + all_rounders + bowlers = 11 — exactly eleven, so neither 10 nor 12 passes;
• keepers >= 1;
• bowlers + all_rounders >= 5 — all-rounders count as bowling options, and five is enough.

The usual mistakes are off-by-one comparisons (> 5 instead of >= 5) and leaving a role out of the total.

MySQL's IF(condition, 'Yes', 'No') is a shorter spelling of the same two-way CASE. The rules can also be inverted: list the ways a plan fails — a total other than 11, or no keeper, or fewer than five bowling options — and return 'No' when any of them holds. By De Morgan's law the two forms agree, and because no column is NULL there is no third, unknown outcome that could fall into the ELSE branch by accident. The query reads every row once, so it runs in linear time.

Reference solution (MySQL)

SELECT plan_id,
       CASE
         WHEN batters + keepers + all_rounders + bowlers = 11
          AND keepers >= 1
          AND bowlers + all_rounders >= 5 THEN 'Yes'
         ELSE 'No'
       END AS is_valid
FROM SquadPlan

Another way

SELECT plan_id, IF(batters + keepers + all_rounders + bowlers = 11 AND keepers >= 1 AND bowlers + all_rounders >= 5, 'Yes', 'No') AS is_valid FROM SquadPlan

Another way

SELECT plan_id, CASE WHEN batters + keepers + all_rounders + bowlers <> 11 OR keepers < 1 OR bowlers + all_rounders < 5 THEN 'No' ELSE 'Yes' END AS is_valid FROM SquadPlan