How does water carve rock?

There are lots of different types of rock, and water can vary in it's acidity, so there are a few variables to consider.

One obvious example is the formation of caves. These are usually in limestone, which is calcium carbonate. If carbon dioxide is present, calcium carbonate will dissolve in water to form calcium bicarbonate. So pure water (OK with a bit of CO2 :-) will happily dissolve limestone.

Another example is in forming gorges like the Grand Canyon. If the surrounding rock is limestone then the gorge forms by dissolution just like a cave; in fact many gorges are actually collapsed caves i.e. a cave forms first then the roof collapses. In the case of the Grand Canyon the rock contains some limestone layers but there is also a lot of sandstone. Sandstone is silica (silica grains loosely bonded) so it isn't soluble in water and wouldn't be eroded just by solution. In this case the erosion is mostly by abrasion of rocks carried in the water, though any limestone present will dissolve and undercut sandstone layers above it. Sandstone is quite soft and easily abraded. If the rock is granite this is very insoluble and very hard so it erodes only very slowly. I'd guess the main water based ersion method would be freeze thaw cracking.

You expanded your title to ask about any erosion of a hard material by a soft material, but rock ersion by water is not a bad place to start as it's quite varied.

Martin Beckett mentioned water jets, and this is another good example if a bit specialised. If you fire the jet of water fast enough it's momentum means it will break off bits from a hard material. I can't think of any example where this is important in nature.