Shonner is right, you need to find your target market first: movies, games, tv shows or just for fun? Before this you will need to watch yor wallet... how much you want to spend?
Here a know selection:

Movies: Maya, XSI, Lightwave, Cinema 4D

Games: 3DMax, SXI

TV Shows: Lightwave

If you want to be a real Pro you will need to choose what your market use not what you want to use. Choose a studio that do what you like and ask for what software they use, this is the right way.

About reallity, well here we get a very large discusion... its very dificult to find someone that do a render without postwork to get a "real" scene. So I saw many guys doing realistic scenes in many softwares, so the problem is the user here, not the application... but with a 3D software with limitations like Dwarg talked about I think is better to think about what to use. Talk to more people, look galleries in the internet (outside the software makers site! there will be only the best) and take you decision.

Follow me at euQfiz Digital