When taken correctly long exposure photography can appear very professional even with the most basic of cameras.
There's a general rule for hand held photography - that you need to shoot at 60th of a second or above to maintain sharpness. If you have really steady hands this can be pushed a little to 30th or a 15th of a second.
Longer shutter speeds than this (when held by hand) will cause softness, blurring or ghosting - which, whilst generally undesirable, can be used to your creative advantage in the right situations.
Most new digital cameras come with the ability to expose for up to 30 seconds - making it fairly easy to set up for long exposures.
Some compact cameras have pre-programmed long exposure modes - such as the 'firework' setting on many cameras.
Higher end cameras - through from bridge cameras to SLRs and beyond - may have a Bulb (B) and a Time (T) setting when using manually.
Bulb & Time
The Bulb (B) will keep the shutter open for as long as the shutter button is held pressed - upon releasing the button the exposure is complete.
The Time (T) function allows you to set the length of the expose anywhere up to infinity - the difference here is that the shutter button does not need to be continually depressed. Instead the first press opens the shutter, the following press completes the exposure.
Of course, having the shutter open for such an extended period of time will require some serious camera support if you intend to avoid blurring! Plus your exposures are going to have to be good - which can be tricky over such an extended period. Any subtle changes in light will alter the final result.
At 'shorter' periods of time, it may be possible to rest your camera upon something sturdy - say a desk or railing, even the ground.
Proper support is recommended however - the use of a good tripod for long exposures, or a monopod for 'shorter' long exposures is certainly the advisable route.
B tends to be used for hand held work. When you may wish to, for example, flash a subject (renders them sharp) and then move the camera to bring the addition of surrounding light blur.
This photograph will have been taken in low light/darkness. Whilst the subject was waving the light, the shutter will have opened and the flash gun fired. The flash captures a sharp single image over a short part of the exposure, the remainder of the exposure (now with no flash or light) allowing the movement of the light to render in such a fashion all within one frame, one shot. 4 second exposure, f7, tripod mounted camera, handheld flash
T tends to be used for tripod work. Where an exposure is long, it is important to note that even touching the camera with (generally) clumsy hands will possibly bring a subtle movement - this will render your image soft.
Using a cable release will remove this problem.
Photographing Lights
Photographing car headlights can create some amazing results. If the camera is fixed, the passing lights give the impression of a 'trail' in the final image.
This photo will have been exposed for a number of seconds - each of the cars lights being the brightest overall areas, so you need to ensure that the exposure does not last too long for threat of excessive bleed. However, any extension of the exposure does allow for the surrounding lights to influence the picture - here there is a general yellow caste due to the streetlamps. Light has a temperature (degrees Kelvin (K)) that corresponds to a specific colour - i.e. Colour Temperature. These streetlamps have a relatively low colour temperature.
To achieve this affect yourself you will need to count how many seconds it takes for a vehicle to travel through your frame to give you a rough guide for exposure length.
You can expose for more or less time, dependant upon the sort of results that you hope to achieve - plus you don't want to overexpose the whole frame, otherwise the light bleed will become too strong.
Fair ground rides can be photographed in a similar way. You may want to count how long it takes for the ride to complete several revolutions before setting your shutter speed. 2 seconds tends to give quite a good effect.
Similar position to the previous photograph. Here the exposure has been left for a time to expose the wheel's lights correctly, yet the floodlights further down the image (being that they are brighter) have overexposed. When you look to make long exposures you need to assess everything that is within frame and see if it will make a viable image, or if you can adjust exposure accordingly.
Other effects you may have seen include 'light writing'. This is where, with the aid of a torch/sparkler/similar light-emitting implement, the motion of any movement will be captured. You could get a model to write their name for example, and the complete trail of light will be captured.
Here the exposure has been for a considerable period of time for the subject to have barely registered in the frame whatsoever - the exposure is made with consideration to the brightness of the light facing into the lens.
Again you will need to count how long it takes for the subject to make the desired movement before setting your shutter speed.
Depending on how energetic their movements are will determine the finished shape. Torches give a nice clearly defined line where as a sparkler will give a shape which looks as if it is erupting. This technique works really well for photographing Fire Poi dancing too!

Don't forget to check out the ThinkCamera guides on Photographing Fireworks and Painting with Light
Moving water & landscape
Images of water can be made more appealing with a long exposure time - and it is often the single biggest effect that can make a land/seascape look like a truly professional image.
Water becomes 'smooth' where movement 'accumulates' within exposure - try out photographing waterfalls, streams and other areas where water is running fast.
It's easier than it looks - to get running water to become like a silky solid element to the picture in its own right simply takes a long exposure with sturdy tripod support. 30 seconds @ f16
A tripod or something to hold your camera steady is essential for these sorts of shots to work. Without one the entirety of the photograph will be blurred due to the movement of your hands.
The slowest (or longest, however you prefer to think about it) shutter speeds create the 'softest' effects. The best long exposure photos of moving water usually have a shutter speed of over 4 seconds.
Many landscape images will have extended exposure times - this permits for subtle changes in light to have a stronger imprint upon the final image. Shifting light may add dramatic areas of different brightness and colour that wouldn't occur with a quick snap.
Even fairly still water will become smooth if captured for an extended exposure - which can be key to having wonderfully smooth reflections.
Also a reason for longer exposures may be that you wish to utilise a smaller aperture - say f32, f45, f60 or even f90 (most of these are only available in older cameras, but are a key component in many landscape photographers' image making).
The depth of field with a tiny aperture also becomes considerably wider - a significant amount of the image will be in focus, another common element in professional landscape photographs.
Exposure can begin to become more difficult to get right when dealing with extended periods of time (if you're using film then you need to know about 'reciprocity failure' - digital doesn't suffer this, but noise can become an issue instead!)
Filters may become an option when dealing with some seriously long exposures. If you cannot use the desired aperture, then a neutral density filter (ND) is a graded grey filter that essentially allows you to stop down. It reduces the amount of light that passes through the lens by a specified number of stops.
There's a fairly extensive PDF about filters that can be found on the Lee website
Sports photography
Using motion blur on images of speeding object or a game of sport can help to give the illusion of the subject moving fast, even when that isn't the case.
Imagine being at the Grand Prix - if you set your camera to 1000th of a second then you ought to catch even a fast moving object in perfectly sharp focus. If you were to use a time nearer to an 15th of a second then the car would appear to blur (considering the speed of travel), but there may still be enough detail rendered to add to the dynamic of the final picture.
Here we have a snapshot of a passing car - the blur gives you the sensation of great speed given the stationary background. This theme can be played on by taking the same/similar shots again and again at different shutter speeds to see which creates the most desirable results. Playing with effects like this can really pay off - sometimes it can be a bit of luck!
This is where monopods come in handy - they assist in support, but don't do it all for you. This allows for quick movement, and helps when trying to keep heavy equipment steady. It's not going to totally save you, but will allow for those 'on the edge' shutter speeds (like a 15th of a second) to become more viable.
Using ghosting to your advantage
Though in most cases ghosting is accidental and leaves for unsightly photos, on occasion it can be used to your advantage.
Ghosting can give the effect that the picture has had multiple exposures. Though you need to make sure either the background or the subject you wish to behind any 'ghosts' is completely still, it can create some very eerie effect when administered properly.
This is not a multiple exposure - more that there will have been a 'short' long exposure that was flashed, and likely the surrounding lights will have flashed and rendered a secondary image to final exposure - only in different positions. This is a fairly extreme example that still presents itself as a coherently enough to provide enough context.
It's all about playing around - thinking about your subject matter and the way in which movement and long exposure can help or hinder a final image.
Experiment with multiple possibilities, write down what you do so you can redo it - and see what you come up with!