This is one of the bigger changes in iOS 5. Under
Sounds you can change quite a few tones including Twitter, Mail and Sent Mail. It's not everything but the major one's are covered and a huge improvement from the previous versions.
They've also added a considerable amount of new tones, and you can now assign custom tones to all of them which previously didn't work.
If you are referring to the notification beep you hear when you are inside the application itself, that will depend on each application if the developer has setup an option to change it. These aren't notifications as such, but rather built into the application and triggered by events inside the application.
Some applications, for example, What's App, will allow you to change the notification in it's own settings.
The short answer to this therefore is that it depends on the application, some can be globally controlled, and others need to have the ability enabled inside the application.