I don't actually have a "real" tripod at the moment (still shopping for one), just a table-top tripod that I got for 8 bucks:
But it does the job reasonably well for my D40 with the plastic 18-55 kit lens, although I wouldn't expect it to hold anything much heavier, like metal-constructed lenses or higher-end camera bodies.
For that particular picture, the exposure time was about 6:46 (minutes:seconds). The way you achieve it is by setting the mode dial to M (manual), wheeling the shutter all the way down to bulb mode and adjusting your other settings as necessary (wide aperture, low ISO). In bulb mode, one press of the shutter release button will open the shutter and a second press will close it. For this kind of thing, though, you really need a remote, which I have. The same principle applies: one press of the button on the remote opens the shutter, the next press closes it. However, the D40 will only allow exposures up to 30 minutes, after which it will automatically close the shutter. I've found that 30 minutes is too long, even in the sleepy little town where I live. You need to be way out in the boonies, away from urban centres with unnatural lighting, to use exposures longer than 5-10 minutes with good results.