When you play with X or Y inverted in means that you are moving the camera. When you don’t have them inverted, it means that you are “turning” the “head” of the character.
If you remember in Super Mario 64, there was Lakitu. He was assigned to the C-Buttons. If you pressed C right, the view turned to the left, because you were actually controlling Lakitu (the camera) instead of the view (what Mario was seeing). Still, I think that game had Y inverted by default, since when you go to first person, you still had to pull down the stick to look up.
Many Nintendo 64 games (Banjo-Kazooie, Conker, DK64, Diddy Kong Racing) had the X and Y inverted because they were letting you control the camera. You move the camera down, you were looking up. You moved the camera left, you look to the right.
Flying an airplane is inverted because of the nature of the control stick. You pull the stick towards you to move the ailerons down and make the plane go up. In reality, they are not inverted, but the perception, makes you think that down is up, because pulling islike moving down, therefore you move an anologue stick in the controller downwards.