We're using Sony Vegas Pro 9.0 and Adobe After Effects for motion graphics/intros. For people just gettintg started who don't need a ton of advanced functionality I'd recommend Pinnacle or Adobe Premier Elements. Both good programs that will do what you need without all of the stuff you don't.
I just purchased Pinnacle Studio Ultimate 12 last week. So far I have been very happy with it. I haven't even opened up the book yet but have started doing basic things like cut clips, add titles and effects. Seems pretty easy and for a good price.