Every Power Mac G5 I've used sounds like a jet taking off when you put pressure on the CPU and/or GPU. The thing had something like 13 fans in the case.
The most silent computer I've ever used was my white 24" iMac 2.16 w/ 7600 GT.
I've never used a mini, but would expect it to be fairly quiet.
All of the intel machines, with the exception of the laptops, which need fans to stay cool, are pretty silent machines. However, in my experience, the optical drives in these machines are NOT quiet. Perhaps it's just in contrast to the computers, but I always found my iMac's optical drive loud.
I've used an August '07 Aluminum 2.4 Ghz 24" iMac as well, and it's quiet, but there is slightly more fan noise than in the old white 24" I owned.