I usually embark in very deep soil, and the aquifer (which is there because I want one) is usually 3 levels down (allowing roof puncture free farming). My thickest aquifer was 8 in conglomerate (don't remember but there might have been two levels of sand as the two top levels), but I've seen a neighboring ocean biome that yielded wet walls all the way down into the first cavern (but the walls weren't crying anyway). Someone reported burying through 17 layers.
An aquifer right below the surface would be an interesting change, but I doubt you'd be able to do much with a single pick challenge, since the jobs rewrite seems to make it impossible to speed dig through wet layers (automatically cancelling the job, manually redesignate the tile, walk away a bit, return to a now water filled dig tile to cancel again permanently. Even if you were to single step to designate the next tile as soon as the previous one was done, you seem to still get the walk away behavior [in fact, I've designated the next tile while channeling away floors while the miner is still working on the previous tile, and have had it still walk away and return, so the next tile job allocation seems to happen prior to the finish of the previous one, somehow]).
The speed-digging used to require at the very least mining 7; 8-9 or 10+ were preferred, and a decent-statted dwarf too. So yea, hard to do without soil above, and you also don't have anything to cave into the aquifer even if you could drain an upper aquifer layer into a lower one. If the aquifer is right below the surface, I think the double-slit method or a winter freeze in a cold climate is really your only choice, but you'll need quite a bit of wood (or a channel elsewhere to absorb the water/lead it away?) to channel the pumped water away from were your dwarves are digging and pumping.
Evil oceans. I've seen loads of them but never found a suitable one for embarking that wouldn't result in instant death.
If the body of water is large enough, you should be able to find an Evil Ocean with a normal shoreline, or at least only a few tiles of Evil land.
Yeah but usually all aquifer'd up or something
Ocean and lake shores IIRC very frequently, if not nearly always or always, have aquifers. If this is an issue, disable aquifers, there *is* an init option for that. Endless free water though (can be desalinated with a pump). Although you can get that from the ocean too, even if building a pump to draw on that can be a bit trickier (and may affect fps, depending on if it causes wandering 6/7 tiles in the ocean...).