Do they also take good pictures in low-light with shaky hands?
Surely there is a point where work smarter not harder actually pays off - your own eyes likely has better capabilities than any camera, yet hardware-wise the latter is surely better than your slightly incorrect bio-lenses. It just has a huge NN attached to it
Well, no, human eyes are way worse in low light compared to modern DSLR sensors.
With a monochrome sensor you can get already exceed 50% quantum efficiency, requiring only a few hundred photons per pixel to get decent quality, and around 10000 in the bright spots to reasonably match what a TV can reproduce (dynamic range and perceived noise).
But with a color sensor you can get recognizable/useful colors down to about 100 photons per pixel. Yes, it will be noisy, but at that light level (assuming a remotely-fast lens) your eyes will give you a hard time when you even try to read normal text. Should be enough to walk without tripping, though.
Human eyes are better than cameras at a lot of things - that's one of the main problems with self-driving cars. We can see at 240fps, see light polarization, we have different "pixel wells" for low- and high-luminance environments, etc. Even the latest HDTV color system omits a lot of colors (electric greens) people can see.
You're definitely going to get a better shot from a f/1.8 lens in low light than a cellphone camera, even vs night mode on iPhone. If you just set it on a stable surface you can take exposures >10" and have it come out crystal clear. (Or carry a gorrilapod)
An f/1.8 lens never comes out crystal clear if you want the entire scene to be in focus. Though since you mentioned long exposures you probably meant closing it to f/4+, but then it doesn't matter what the top spec is.
I didn't mean the entire image would be in focus, just that your subject would be clear.
A faster aperture will let in more light, so you will not need as long an exposure to get the same amount of detail. It can make a meaningful difference in low light shots.
Adjust your aperture to get the appropriate amount of bokeh, of course; but generally I prefer as wide an aperture as possible in most situations, as long as the focus is appropriate.
That statement is completely dependent on what you think the job of the camera is.
A high-quality P&S (like the Sony RX, Fuji X100, or Ricoh GR) is going to outperform a phone camera in just about any edge-case (low light, motion, natural bokeh). Even more so if you shoot raw.
Somebody said it best in a sibling comment - a high-end camera is a tool, like canvas and brush, and you need to know how to use it, practice with it, and get in the zone when using it.
Nobody is arguing that cell phone cameras aren't amazing. They are, no question. And they're almost always in your pocket/purse, so even better. They're great for generic snaps of life.
But there are things they simply can't do - wildlife photography and outdoor sports (not enough zoom), low light - toddlers playing inside or indoor sports (sensor too small, stacking exposures with night mode doesn't work with fast moving subjects), natural feeling bokeh (portrait mode has some odd edge cases). If you don't care about any of those, then a cell phone camera is likely all you need. If you do care about those, then a high-end super zoom, mirrorless, or DSLR is going to make a massive difference.
