The most common use of a tripod is to combat dreaded "camera shake." This happens when low-light shooting conditions force the photographer to use a shutter speed too low to hand-hold the camera without shaky hands blurring the image. The longer a lens' focal length, the faster the shutter speed needs to be to avoid camera shake. A shutter speed below 1/60th of a second is slow enough to warrant tripod use. Good shooting habits, though, can lower this number a little.
Long exposures of at least several seconds and sometimes up to a few hours --- for instance, when capturing stars moving in the sky --- require the use of a tripod. In this case, the tripod not only isolates the camera from the photographers hands, but it also maintains a static frame composition.
Sweeping panoramic shots can be easily created using a series of slightly overlapping, horizontally displaced images linked together using readily available, specialized software. By using a tripod, a stabilized camera only moves along the horizontal plane, making it much easier for the software to combine the images.
The greater weight of some telephoto zoom lenses, like those used to capture wildlife images from an extended range, necessitates the use of a tripod. This practice guarantees better photos.