The majority of the work is done by this cheap little $6 pump that I got from Amazon. I just barely submerged the input under the water's surface and left the output facing up out of the water.
Powering it was incredibly easy. I just stripped and connected the red and black wires from my power supply to those on the pump. I had a spare switch too so I added it, but that isn't necessary if you're willing to plug it in when you want it on.
I added an assortment of rocks and a friend's ceramic frog to a $2 flower pot base and filled it with water. I tilted the fountain a bit so that it would fling the water over the rocks in a little arc and the fountain was finished.
The fountain is lovely and cute and all, but as a quick side note, I was curious just how much that little guy was really going to cost me in power. Specifically, I wanted to compare the power consumption of natural noise generation to that of synthetic noise from a speaker. My back-of-the-envelope analysis is a little sloppy so please correct me in the comments if you see a mistake.
At 5 Volts, that motor was pulling 2 Watts of power. Since electricity costs about $0.08/kWh, I'm estimating that it'll cost me one penny in electricity every 2.5 days or about $1.50 per year. Meanwhile my radio pulls about 5 Watts so that'll cost me roughly $3.75 per year.
Both numbers are pretty small when you think about it, but I'm glad that the natural sound appears to be a better deal. Additionally, this will become less of a concern eventually because I'm hoping to replace the power supply with a solar cell. But then, I'll have to factor in the solar panel cost as well. (Not to mention that the fountain will only run in the daytime.)
When the pump finally breaks down, I'll update this post with approximate usage statistics to let you all know the life cycle cost of this guy. Of course, the pump will almost certainly break down before an audio speaker would, but then again, that means I get to build something even better next time!