What you're talking about are two things-- 1) motion graphics 2) special effects
Really fancy motion graphics (stuff like a "spinning globe", twinkling background, or animated intro, etc) are usually made from scratch in 3D programs; high end video editing programs like Adobe After Effects; or any other type of software that you can make animations from scratch and export to video format.
However, budget video editing software will usually come with premade "motion graphics" clips that you can incorporate into your projects. Usually, this lowcost software will give you some basic tools to create motion graphics on your own-- but nothing too fancy, and you'll definitely have to learn a little about keyframing and timelines and the like to produce anything decent. If you can't afford a commercial video editor, I guess you can search YouTube for "free motion graphics."
As for "special effects"-- AFAIK, stuff that "sparkles" or "shines" and etc, I definitely think you need something powerful like Adobe After Effects, because those are the only types of programs I know that allow you to create what are known as "particle effects" and the like from scratch.
Hoped this helped!