In the stock iOS, if an app (such as Mail) allows the choosing of a custom notification sound it will come from the ringtone list, to which you already know how to add sounds. Otherwise, you're limited to the standard iOS sound or whatever choice the particular app may allow in its own settings.