I have used Aperture. I must say, not bad. The fact that they organize photos into a single file (you can show the files contents by right control-clicking it in finder), can be problematic to many people, much why a friend stopped using it.
iPhoto: Awesome
LightRoom: Recently started using, and A LOT last night, it has a beautiful interface, and is pretty fast and stable.
I have also seen people use Adobe Bridge for this purpose, Its pretty much a Finder, but focused on photos.
As far as photo manipulation and editing:
Photoshop CS3 Extended and CS4 : Have used both of these, very great. Once you get the hang of how layers work, the whole application is just a breeze to use, and you can use pretty much any other photo editing application that has the same base. As in, layers.
Photoshop Elements 6 : Haven’t used 7, but 6 is quite amazing. Adobe is seriously closing the gap between their flagship software, and their amateur software.
And for Mac:
I use a great application called PixelMator
I really love this application, its 60 dollars, and free upgrades for life. It gets updated at a faster pace than PSE, and is coming closer to its features. It can even open PSD files. It focuses on the GPU rather than CPU. Check it out for a cheap alternative. Its really a GREAT investment.