Ebbie, where do you get "sweet water" from? I'd assume that's just another name for soft water, right? Still, at least you can tell your friends they're wrong and you were right! :-)
To add to Geoff's list, there's another major source of water - ground-water. This is water that's sat in permeable rock under the earth. Sometimes this water has been sat around for a *very* long time; sometimes it's arrived relatively recently. But permeable rock usually equals limestone, and it's been sat there for a while, so you can expect it to be very hard indeed.
Rainwater is indeed slightly acidic. So is our body though - all animal fats AFAIK are acidic, and skin, hair and all the external parts of our body need their accompanying layer of grease to stay healthy. Hence some skincare products mention pH5.5, which is the same (average) acidity as skin grease.