Yes, these kinds of photos are really what separates the lower-cost point and shoots from the more advanced cameras. Tripods work for fixed subjects, like the castle, but the challenge here for your camera is to have high enough ISO speeds and fast enough lenses to "freeze" the action in low light situations. Otherwise, moving objects like the floats in Spectromagic will be blur. Of course, sometimes, artistically, you want that.
Like others have mentioned, the Nikon D50 and Nikon D70 are nice entry level digital SLRs that will get you what you want. My personal choice is the Canon Digital Rebel XT. The Canon's are known for low noise levels with their CMOS chips. This is particularly important when shooting at ISO speeds like 800 or above. Of course, the Nikon's have their advantages too. Especially when using fill-flash in sunlight. But I digress.
With my signature below I have posted a url of a bunch of night shots (including Spectromagic) and indoor attraction shots from our trip to Disney last July. They were taken with my Canon Digital Rebel XT with the kit lens at an ISO of 1600, aperture of 5.6 and shutter speed of 1/30. This was fast enough to freeze the action without a tripod or flash.