🥷 Samurai SQL Practice Questions

Easy

Count samurai per clan
Show clan.name, COUNT(*) of samurai; include only samurai with a clan.
Average valor by role
From participation, group by role, compute AVG(valor_points).
List distinct weapon types available
Use DISTINCT on weapon.type.
Samurai trained in Kyoto
Join samurai → dojo; filter dojo.city = 'kyoto'.

Top 3 most valorous entries overall
Return samurai.name, battle.name, valor_points ordered desc with LIMIT 3.
Average casualties per clan at Sekigahara
Join participation → samurai → clan → battle;
filter battle.name = 'battle of sekigahara';
group by clan.name; exclude NULL clans (WHERE or HAVING).
Dojo style impact
For each dojo.style, compute AVG(p.valor_points) across its samurai’s participations.
Requires join chain. Order by avg desc.
Bow specialists
Find samurai whose highest mastery level is on a weapon.type = 'bow'.
Hint: compare each samurai’s max level across weapons and filter where the max occurs on a bow; use subquery.

Clan dominance by province
For each battle.province, return the clan.name with the highest total casualties_caused in battles held in that province.
Hint: aggregate per province + clan, then pick the max per province via correlated subquery or window-function emulation.
Consistent performers
List samurai who participated in at least 2 battles and have an average valor_points ≥ 75.
Show name, count of battles, and avg valor.
(Use GROUP BY … HAVING COUNT(*) >= 2.)
Weapon breadth vs depth
For each samurai, show:
- name
- distinct_weapons_mastered
- avg_mastery_level
  Then filter to those with distinct_weapons_mastered ≥ 2 and avg_mastery_level ≥ 7.0.
Ronin effect
Compare average valor_points of ronin vs non-ronin across all participations.
Return two rows: group label and avg.
Young commanders
Find commanders (role = 'commander') who were ≤ 35 years old at the time of the battle.
Use battle.year - samurai.birth_year ≤ 35.
Return samurai.name, battle.name, age_at_battle.