They won't take better night time pics. When taking night photos there are two things to consider.. how much light there is, and how fast the subject matter is moving.
The point and shoot's advantage is they will always take their best possible photo with what you have. No worry about which lens you have with you, or if your lens is any good for nighttime shooting, etc. Basically 'its as good as its going to get, all the time' is what the value of the point and shoot is.
The point and shoot will do absolutely squat without it's flash in low light.. you'll get all junk. It will be worthless for taking photos of anything but illuminated things.. or flash photos of people up close, etc.
So it's ok to take photos of the castle.. and even fireworks to a degree.. but you aren't going to get any on ride photos, or distant photos of things.
If you have a decently fast lens.. you should be able to get all those same portrait or castle shots with your DSLR without a tripod. Worst case, crank up the ISO and accept the more noise for faster shutter.
Here are some photos taken with a SD800is canon years ago
see the need for flash (and still grainy) in this one?
See.. still not sharp
(with flash)
Indoor at night with flash
Here are three photos in a row from the same area showing just how fickle they can be with lighting. One is under exposed with no flash, one has flash, and the other because of looking the other way had enough light to get a reasonable photo
This is a good example of just how noisey the photo can be when trying to take low light photos