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 nullif(sh.senshi_name,'') as sailor_senshi,
nullif(sh.real_name_jpn,'') as real_name,
nullif(c.name,'') as cat,
nullif(s.school,'') as school
from sailorsenshi sh
left outer join cats c
on sh.cat_id = c.id
left outer join schools s
on sh.school_id = s.id;

Link

Reference

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store