The main use for the shift part of a tilt-shift lens is for perspective correction, most typically in architectural photography. But you can do other wacky things with them, such as photographing a mirror without seeing your reflection, or photographing objects when there's something in the way. The Wikipedia article entitled Perspective Correction Lens contains links to a number of good tutorials and articles on this subject.

But what about the tilt part? Well, that gets really weird. Tilting the lens allows you to bring any plane into sharp focus, not just a plane perpendicular to the sensor. If you know what you're doing, you can probably get huge Depth Of Field for landscape shots or for product photography. But we're way out of our depth here. If you are too, you might want to read the Wikipedia article entitled View Camera/Tilt.

What we do know is that the Nikon PC-E 45mm is very well built and is superb optically - very sharp at all apertures. The focal length makes it quite versatile: long enough for product photography (especially on a DX-sensor DSLR), and short enough for landscape or architectural photography (especially on an FX-sensor DSLR).