Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.
Mail > Preferences > New Mail Sound

You can also add your own sounds if you move them to ~/Library/Sounds.
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.