SQL with Sailor Moon: Thinking about JOINs...

Practise some SQL fundamentals by making a simple database on a topic you feel familiar with. Or use mine, populated with a wealth of Sailor Moon trivia.

sailorsenshi schema

• id
• senshi_name
• real_name_jpn
• school_id
• cat_id

cats schema

• id
• name

schools schema

• id
• school

Return a results table - sailor_senshi, real_name, cat and school - of all characters, containing each character's high school, their civilian name and the cat who introduced them to their magical crime-fighting destiny.

Keep in mind some senshi were not initiated by a cat guardian and one is not in high school. The field can be left blank if this is the case.

 

Solution:

SELECT 
  senshi_name AS sailor_senshi,
  real_name_jpn AS real_name,
  cats.name AS cat,
  schools.school
FROM 
  sailorsenshi AS s
LEFT OUTER JOIN schools ON s.school_id = schools.id
LEFT OUTER JOIN cats ON s.cat_id = cats.id

 

 

Result:

sailor_senshi	real_name	cat	school
Sailor Moon	Usagi Tsukino	Luna	Juuban Municipal Junior High School
Sailor Mercury	Ami Mizuno	Luna	Juuban Municipal Junior High School
Sailor Mars	Rei Hino	Luna	TA Academy for Girls
Sailor Jupiter	Makoto Kino	Luna	Juuban Municipal Junior High School
Sailor Venus	Minako Aino	Artemis	Juuban Municipal Junior High School
Tuxedo Mask	Mamoru Chiba		Moto Azabu High School
Sailor Uranus	Haruka Tenou		Infinity Academy
Sailor Neptune	Michiru Kaiou		Infinity Academy
Sailor Saturn	Hotaru Tomoe		Infinity Academy
Sailor Pluto	Setsuna Meiou