Are these any better than air coolers for normal use or overclocking or are they just hyped up?
Depends how cheaply you can pick one up and which kind it is.
My H100 does a notably better job in both normal and overclocked modes than most air coolers. I got a refurbed one for £40, which was far better bang for buck than most of my options. It's a cheap, hassle-free entry to watercooling.
It can also depend on things like your case/fan setup. I have a side fan intaking directly into the space from which the H100 fans draw air and push through the rad.
I'm put off that you can't perform any real maintenance on them. At least with an air cooler the worst that will happen is the fan will fail.
I've also read of a few people that weren't careful with their installation and had water go everywhere. Knowing my luck, I'll probably encounter that.
Air coolers are also big and heavy. I heard some have bent motherboards in the past, though I'd expect most to have resolved that issue by now.
AIO coolers are supposed to be maintenance-free, sealed units, though.
Yes, you have to be a little careful during installation, but probably no more than with any custom loop radiator. Obviously I can only speak for the H100, but I wasn't especially delicate about it. I suspect build quality depends on the brand.
Just to butt in on this, how do you guys find the pump noise on these units?
Again, I can only speak for the H100... It's noticable, but not especially loud. My GPU fans at 40% drown the sound. I have a friend with a proper custom loop and it's about the same volume as his rig.
What makes more noise is the air being pushed through radiator and case grilles, meshes, etc.
The stock 120mm fans are pretty high performance and shift mega air (156m³/h (92CFM), generating 7.7mm/H20) but are consequently noisy as heck on full chat (40db at 2500rpm).
I swapped those out for a pair of Noctuas, personally and am happy with those in place.