I'm going to jump in here and defend Lightroom a little.
Adobe Lightroom is fantastic. It's perfect for the sorts of tasks DK described. Kayto's comparison to a dark room is right on. Lightroom can do exposure adjusting, color corrections, sharpening, de-noising, red-eye removal, lens correction, and various effects. What it can't do is compositing or retouching. For example, you couldn't remove a zit, or copy-paste a person from one photo into another.
Lightroom's key strength is in managing and organizing photos. You can group photos into albums, tag, rate, and output slideshows and stuff. This beats the folder-full-of-folders-full-of-jpegs system most people use.
I disagree that Lightroom "was created for a 'once-in-a-while user'." Every professional photographer I know uses Lightroom (or Aperture) as a part of their workflow. Here is a popular workflow:
1. Import photos from camera into Lightroom.
2. Organize photos, using star ratings to identify the best images and weed out the bad images.
3. Adjust the exposure and color balance of selected images.
4. Apply other adjustments as needed, such as de-noising or lens correction
5. Only if an image still needs additional work, export it as a Photoshop file.
6. When you're done in Photoshop, import the file back into Lightroom so that it doesn't get lost and forgotten.
As a more casual photographer, I can accomplish 99% of what I want in Lightroom. I'm an experienced Photoshop user (I use it every day for my job), but I still very very rarely use it on my photos.