Depending on how large you want to make it, will determine what you use. I've only done birdbath size ones with a solar pump.
YOU WILL NEED TO BUY A PUMP SUITABLE FOR YOUR PROJECT!
A fountain can be the pump placed into a sink or container of some sort and decorated out as required........Hope that helps a bit.
Here’s one we made: https://www.frugalfamilytimes.com/2016/07/how-to-make-backyard-fountain-in.html?m=1
I did this fountain out of 2 plant pots and i put the water circulating pump in center of the largest pot , i put the small pot upside down then I fill it with water, I made a wooden light post plus redesigned the surrounding garden with redstone blocks and later I did make a toucan with light, then I build that beautiful garden storage shed size 2x4x5ft (20 cu-ft)with slide out tray it is like garden workshop and storage for my wife garden tools, I Am retired hvac,plumbing and electrician,gas fitter certified, licensed contractor plus i do repair car transmission and almost every part in car, truck