I created an Island with water around it. I want to make the water around the island get gradually LESS transparent with it's depth, so that you can see a littlebit of the sand below the surface of the water but make it gradually less visible as it goes down. How can I achieve this?
You can use a Principled Volume node for the effect (also have a look at Volume Absorption and Volume Scatter nodes). Most simply just create a cube and apply the shader to it and place it below your ocean plane. With the Density value you can control the "depth transparency".
If you want to have a better volume than a cube, duplicate your plane with the Ocean modifier, apply the modifier and then extrude the ocean mesh (but this can be a little bit heavy).
You can also combine surface and volume shaders:
You can use a black and white image as a mask that you plug into the Mix Shader, then plug your shaders into the 2 inputs of this Mix Shader. I've made it simple (a mix between a Glossy and a Transparent shader) but of course it can be a more complicated mix of shaders. Depending on the black and white of your mask, you'll see either more of the first input or more of the second:
