1) What is a long exposure?
Most photographs are taken with a shutter speed of 1/30th of a second or faster. A long exposure is just setting the shutter speed slower than normal for the subject matter (and "normal" will be different from person to person).
2) What effect does it create?
Slow shutter speeds capture time. If your subject doesn't move during the exposure, it may look just like if you had used a fast shutter speed. But if your subject moves during the exposure the movement shows up as a blur, streaks, etc... in the photo. Movement at the camera end is also recorded.
3) Does anyone have a picture so I can see how it's supposed to look?
Take your 350D and set it to Tv, and set the shutter speed to 1 second or longer, and photograph anything moving. You may have to decrease the ISO to 100 if you are trying this outdoors in the daytime.
4) What else can you shoot long exposures of except stars?
5) What purpose does it serve?
Moving water is popular to shoot with a slow shutter. It gives it that creamy look. You can shoot anything you want with a long exposure. It's just a different look. Imagine the difference between a photo of a running horse where the horse is frozen (with a very fast shutter speed) in the air in mid-gallop with all the details sharp, and a photo of the same running horse where the horse is blurred with movement. Neither is right, it's just personal preference, or what look your are trying to achieve.
You can pan the camera with a moving object as it goes past. This will allow portions of the subject to remain somewhat sharp, while the background is motion blurred.
You can combine flash with slow shutter to get your moving subject frozen (by the flash) along with motion blur.
6) How do I do it with my 350D?
Use the camera in manual or Tv (shutter priority) exposure modes, and select a long shutter speed. If you want non-moving objects to be sharp you'll need a tripod and a cable release.
For exposure times longer than 30 sec (or whatever the max shutter speed on the 350D is) you'll set the shutter to "bulb". On bulb the shutter will stay open as long as you hold down the shutter release button (preferably on a cable release). Some cable releases have button locks for really long exposures so you don't have to stand there. Canon has a really fancy cable release for the EOS cameras with timers, alarms, intervalometers, and all sorts of gizmos.