http://en.wikipedia.org/wiki/List_of_video_editing_software A complete list there. The best is probably Adobe Premiere (the market's leader) but it's also very expensive at over $800. Look at the free ones, maybe a combination of two or three can do a good job. I don't really know, this is not my area.
Well, if you're running Windows XP/Vista, you could always use Windows Movie Maker. It's not that advanced, but good for putting together a few vids/pics and adding a few effects.
Another option you might try is Adobe Premiere Elements ($99). It takes a lot of the important features of Premiere and it is a fraction of the cost. I've never used it but people have told me most of the things they leave out of Premiere you would probably never use anyways. Another thing to definitely do. Before you drop an outrageous amount of money, try some free trials. Just about every video editing suite has a free trial so give them a whirl. You could have the most expensive, feature-packed video editing software but it'll be worthless if you aren't comfortable with the user interface.
I tried Adobe Premier and it was just too much for me to bother learning, so I just stuck with Movie Maker.
I use Sony Vegas 8, Adobe Premiere Pro, Avid Express Pro and Final Cut Pro (Mac). For Special Effects, I use Adobe After Effects CS3. But for these programs, you need to be well experienced, maybe not Vegas, but for the rest, yeah.