I found some exmaple photos of super shallow DOF and the effect of aperture size in night photography. Doing this sort of magical stuff requires inconveniently heavy and expensive hardware upgrades. If you happen to like architecture, you’re absolutely going to need a tilt-shift lens. You can also use those lenses to make a real scenery appear as if it’s from a scale model. Nowadays you can also find lots of photos where that effect has been faked in post, but a tilt shift lens will be able to do it for real. Also, the fake versions don’t take distance into account and might blur the wrong parts of the photo for that sexy miniature look.
If you already know you don’t care about bizarre photos like that or if you know that carrying heavy equipment isn’t your thing, then going with a nice point and shoot camera will open up several new possibilities too. For example, having the ability to control depth of field even a little bit is incredibly valuavble in many situations.