All of these types of vehicles experience different environments, so it’s important to study them individually.
Regular rockets have to work under their biggest limitation, mass. They don’t spend much time in the thicker parts of the atmosphere, as they launch straight up and only start pitching over to horizontal gradually after a few dozen seconds have gone by. As such they’re better off with the reduced mass of a rounder nose than with marginal gains from a slightly more aerodynamic sharper nose that weighs significantly more.
The shuttle is a special case because, unlike regular rockets, the orbiter itself has to survive re-entry. At the speeds at which it hits the atmosphere, the compression of air forming shockwaves in front of the orbiter causes it to heat up to extremey high temperatures. With a sharp nose, those shocksaves stay very close to the spacecraft, which means the heat transfers faster into it. A rounded nose moves those shockwaves further away, creating a buffer layer of air that slows down the heat transfer. This is also why all space capsules have a rounded, almost blunt bottom.
Subsonic aircraft like passenger jets are built to be as efficient as possible above anything else, because that saves fuel and fuel costs money. Conversely to common intuition, below Mach 1 rounded noses are actually more aerodynamically efficient.
Lastly for missiles and other supersonic aircraft, you’ll find their noses are indeed sharp as you would expect. This is because sharp noses are the most aerodynamically efficient at supersonic speeds, but these aircraft don’t go fast enough for heat to be a major concern.
Latest Answers