Howdy! Welcome to our community of more than 130.000 members devoted to web hosting. This is a great place to get special offers from web hosts and post your own requests or ads. To start posting sign up here. Cheers!
I would say (and I know these are expensive but..)
Sony Vegas Pro for video EDITING and then Adobe AE for effects and whatnot. I've used AE for making introductions and adding effects to a movie clip, but I use Vegas to splice it all together and then render the final product in there. Vegas can do a few effect as well, but obviously not like AE - so I would just say Vegas since it should be fine.
I was looking for free Video Editing software too at one stage when I was making a video for uploading to YouTube. I had to search quite a bit and then found one that was not too bad: AVS Video Editor from AVS4you.com. I ended up paying for it, but I don't think it was necessary to pay as it didn't ask me for a license number when I installed the software. I got a special package that included most of all of the other software that was available like a video converter. What I liked about it is that it helps prepare videos for YouTube and uploads it directly to YouTube, Facebook, Flickr, Flash and a few other places. The output is not super high-tech quality, but looks reasonable, and is simple enough for novices like me to deal with.