If, by effects, you mean the opening title, you will not be able to that in any stock video editor, whether it is Premiere (I use Premiere Pro CS4), Vegas, Avid or something else. There are some third-party plug-ins for these packages that might do it. I've got an older stand-alone product called Particle Illusion that could produce something similar to the "sparkling dust" effect, and a couple of other programs that could the 3D title modeling. However, whatever you use to produce the effect, it will involve fairly extensive compositing, either within a program like After Effects or, if you're really masochistic, within the video editing program itself.
You may be able to pull off the lower-thirds title, but if you want it to look like the one in the video, you'll also need decent photoediting software, e.g. Photoshop.
Syncing audio to video is easy. Just shoot different masters for each POV shot, place each one on a different video track and then cut out whatever you don't want. It's also relatively easy to line up pick-ups with the corresponding audio -- you can do it by eye, through trial and error, or you can look at the audio waveform and pick out a beat that corresponds with a clear video cue, e.g. the downbeat of a note played on the bass, and then line them up in the editor.
With that said, I can guarantee you're not going to turn out a "professional video" for a few reasons:
1. As should be apparent from reading this website, producing a professional-sounding audio track is far from easy, and requires far more than just setting up a microphone and recording audio into a computer.
2. Producing professional-level video is similarly very complex. Anyone can dump video into a computer, import it into an editor and cut it together. However, to look professional, you need to match lighting, gamma, color (though this video was b&w) from shot to shot. You need to know how to cut to music. You need to understand focal lengths, depth of field (see below), video codecs and a variety of other video-specific concepts.
3. You will not be able to use a consumer camcorder to produce a video that looks like the one in your link. It displays considerable depth of field, which can only be achieved using either a DLSR that can do video, a DOF simulator that goes over the lens of the camcorder, or a semi-pro to professional grade camera.
Honestly, saying, "I'm studying violin and want to make a professional video of a project," is no different than saying, "I'm studying violin and I want to produce a professional recording of a project." Professionals have both the knowledge, experience and equipment that lets their work look professional.
I've been an amateur videographer, as well as an amateur at audio recording, for decades and have invested many thousands and thousands of dollars in software and equipment to do both. I've spent years and years learning how to use that software and equipment. If you see my videos, or hear my music, you may say, "Not bad . . . for an amateur." However, no one will ever mistake what I do for professional work.
I'm sorry if I sound discouraging, but you really need to adjust your expectations.
