People often wonder 'why shoot slide?' The answer to this question is a matter of 'what do you want to do with that photograph?'
Gone are the days of shooting slides and showing slide shows of vacation photographs to the neighbors. Few people today have slide projectors and the screens necessary. Instead the trend has gone to digital cameras and slide shows on the web.
If you are planning on taking three dozen photographs (standard size of a 35mm roll of film) and then getting prints made of all of them (most often each photograph is of a different subject) and sticking these prints in a photo album - the best choice is likely that of the color negative film.
However, if you are planing on taking three dozen photographs with all three dozen being of the same or similar subjects in an attempt to get the one or two in the roll that are nice photographs, slide is likely the choice here.
Likewise, if all you are after is the developed film (be it negative or positive) for scanning (scanners for 35mm film get in the range of 4000 dpi - allowing for 11x17 prints at 300 dpi without problem), the cost for slide or print film is about the same (cost of film and development).
The biggest issue with slide film, however, is the latitude (or lack thereof). Color print film can capture 7-9 stops of light compared to slide film typically capturing 3-5 stops (black and white negative film often has an even greater latitude). This means that with slide film it is harder to capture detail in the shadows of a forest and at the same time capture the bright sky.
The second biggest issue with slide film is the exacting nature of its color representation. On one hand, most of the special color film is slide film (tungsten balanced, warm balanced, saturated color). This can be seen with the wealth of difference between the different brands and how each captures the same image differently (different sensitivity to different colors). At the same time, the very nature of slide film means that the photo lab can't adjust for the color. Good photo labs (and I don't mean 1h photo at the drugstore) may look at the print and try to adjust it so that it is what you took rather than just printing them as fast as possible. This is impossible with slide film. If you shot a slide in the mountains and didn't use a warming filter, your slide will have a blue cast to it and there isn't anything you can do about it.
The last issue with slide film is the print. As I said above, if you want prints of everything, go with print film - your pocket book will thank you. Even the least expensive option - the intrenegative, is still reasonably expensive if one was to make for every frame of a slide. To capture the true nature of the slide film, the Cibachrome (now known as Ilfachrome). While this is a more expansive option, it takes a direct positive image (no intermediary steps) and allows for large enlargements (larger because of the finer film grain and sharpness provided by slide film) and superior paper. These are photographs for printing large, framing and display rather than printing out at 4x6 and sticking in a photo album.
As professionals tend to use slide film when extra accuracy is needed, there are lines of slide film made to match tungsten lights rather than daylight. This is used in studios to get accurate color representation. Tungsten balanced film is more sensitive to blue (to compensate for the red balance of the lights) - one trick in movies would be to film with tungsten balanced film and underexpose by two stops giving a blue cast and low light - similar to that at night, but filmed by day (watch for 3pm shadows at midnight). I have only found one color negative film that is balanced for tungsten compared to half a dozen color transparency films.
There is a wide range of slide film (this is but a small sample):
Velvia is the first choice for many serious photographers. It provides saturated color. Of special note is the saturation of green - in all of its variations. Photographs of landscapes that have greens, show an amazing range.
Some photographers will set the ISO to 40 for Velvia (1/3 of a stop slower - some even go to ISO 32). Here, the photographer has chosen to lean to the highlights rather than the shadow detail (which Velvia captures quite well). Assuming that the scene is properly metered, this may result in a better photograph.
Provia is Fuji's other major slide film and comes in at 100 ISO. In most cases, the film is very similar, just a stop faster. There are some indications that Provia might be better for digital work (providing a smoother scan, and slightly less contrast). Provia has a finer grain than Velvia. There is a slight green cast to Provia compared to other films.
Kodachrome is the old great of slide film. For a long time, Kodachrome 25 was used by National Geographic for almost every photograph. Today, Kodachrome is only found as 64 speed for 35mm film (I haven't found it for 120 and I've looked). Kodachrome 25 was abandoned due to environmental concerns. Development of Kodachrome is rare to find outside of Kodak labs because it uses cyanide as part of the development process (the name cyanide shouldn't scare you any more than selenium or sliver sulfide - selenium is a toner that is particularly nasty to the point of a warning not to let it come in contact with skin).
Kodachrome does not actually contain color dyes in the film - these are added later as part of the development process (which also leads to the stability of the film over long periods of time). Kodachrome gives natural colors and is very sharp. Kodachrome has a tendency to cooler color renditions (more blueish) than other films that people are used to (though these are accurate color representations - most people just expect warmer colors from film). This can be a 'good' thing when taking photographs of sunsets, capturing more detail in the clouds (this tends to get washed out in saturated color films).
Ektachrome is Kodak's current line of E6 process film. This comes in three major 'favors' - natural, warm, and saturated. Of old, this came as 'Ektachrome 100', 'Ektachrome 100w', and 'Ektachrome 100vs' though recently has undergone a name change emphasizing the tight grain structure of the film: 100G (natural), 100GX (warm) - the saturated film has not yet undergone a name change.