For low light, it depends on what the subject is; ifthe subject is close, you might use either a fast lens or a flash, but if the subject is more than 20 meters away, that's more than a flash can handle, so you'll need fast lenses.
For landscapes & architecture, most people use wide angle lenses. (BTW, the geometric distortion inherent in superzooms will rear it's ugly head in either of these subject types.)
Sunrises and sunsets can require from wide angle to medium telephoto.
Actually, except for the low light shooting, the kit lens is a good place to start, whichever camera you choose. But for the low light stuff, Canon has a bettter selection of fast lenses than Nikon. That's because most of Nikon's faster lenses won't autofocus on Nikon's D40, D40X or D60.
Macrophotographyis tough. It depends on what you mean by 'macro'. There's flowers and then there's bugs; there's watches, and then there's watch parts. The choices you make should be appropriate to what you want to shoot.