Be sure to un-murkypool all tiles in your moat because they will evaporate your water right quick if your embark gets hot enough. Also, while wells use up almost no water at all, a waterfall will result in water loss, and so you'll need to replenish your supplies. I see no reason why your various uses for water need to be connected, and i can see now a titan going swimming in your moat and coming out the waterfall in your dining room. Not to mention a vampire falling into your moat or something would infect your waterfall and well. (wouldn't THAT be fun). So keep em separate.
1) moat. A dry channel is better protection than a water moat. just make a big ditch around your place. save your limited water for important things.
2) well. I usually have a water source somewhere far away from my hospital, then dig a 2z level deep hole in the hospital, stick a well on it, and have dwarves fill the well with buckets of water from the water source. if there is a well on both ends this may or may not generate extra water. I know that it takes 7 buckets of water to fill a square, and i'm also pretty sure that it takes 10 fillings of a bucket from a well to use up 1/7 of a square. Anyway, the hospital well doesn't see that much use and so hand filling it should be fine.
3) waterfall. Everypony wants these and they are hard to build, hard to keep running and they cause lag. Anyway, you'll need a reactor to power it and a significant area made of floor grates so that it is unlikely water will get on the ground and dry up. Dwarves getting wet doesn't actually use up water, so you're safe there, but hitting them with falling water is dangerous to unarmored dwarves. I'd recommend having the water fall on a tile and restrict traffic there to keep dwarves off it. Then the mist flys outward from the tile a few squares and falls down though the grates into a cistern where it is then pumped back up. You'll still lose water though due to random extra far mist travel and the 1 tile it lands on. Collecting rain water is hard. You'd have much better luck finding water in your caverns.
...
Water travels down up/down stairs just fine, and those are most convenient for dwarves. Digging empty holes is a trick to learn too. if you want a 5x5 hole 10z levels deep, first dig the whole thing out of up down stairs. This will be make caveins unlikely. Then, starting at the top, designate each level to be channeled. The miners will do this from the level below where you designated and result in nothing left on the disignated level. Then go down 1 level and designate for channeling and repeat. Smooth each level if you want it look pretty and consider adding windows so dwarves can look in and see the fishes/goblins going by.