What illnesses are brought about by drinking contaminated water?

Several. Contaminated water, especially with human excrement, can cause several illnesses, many of which are still seen in the developing world. Some examples include polio, cholera, giardia, etc. Tapeworm infections may also be spread in this manner.