Some simple tips for having better control over the mood:

Take control of the ambient light

Too often people just leave the ambient light to the default gray color. This is a huge missed opportunity, since the ambient light has huge influence on the mood.

For day scenes, it works well to make the sun light source have a yellow tint (or whatever color the sun is) and make the ambient light have a blue tint (or whatever the overall color of the sky is). If you have Unity Pro and are making an outdoor scene, you can set the Skylight color in the lightmap settings instead and maybe make the ambient color Black, but if not, then setting the ambient light color generally works well.

For night scenes, the ambient light should be really dark. If you want things to be pitch black where there's no light, just set the ambient to black. However, you can often get better mood by setting it to a really dark color. A very dark blue is good at simulating moon light, but experiment at find out what creates the mood you need.

Always use fog

The fog setting in the Render Settings is also hugely valuable for creating atmosphere. If you think fog is only to be used for foggy scenes then you're wrong! If you enable fog but set the density really low, you can use it to create aerial perspective. Aerial perspective is the effect that for example mountains long away get more and more blueish tinted. However, you're not limited to make it blue. In real life, aerial perspective has different colors as well, and generally is closely related to the color of the sky. So you'd typically use a blue color on mid-day, but an yellow, orange, or red color at sunrise or sunset. This creates tons of mood!

Use multiple colors for lighting

It's often a good idea to use multiple tinted colors to make the scene more interesting and less bland looking. For day-light, an obvious choice is the blue for ambient and yellow for direct light setup, which creates a nice balance between yellow and blue lighting. In indoor scenes it's a good idea to make light from artificial light sources yellow tinted (since most electrical lights are yellowish), while light from windows is blueish (since it mainly comes from the sky). But other combinations than yellow vs. blue can be used as well.

Underwater atmosphere is one of the toughest to recreate. The Island demo is giving a first very basic step. These are the 6 points I see for underwater apart from sounds and physics.

1/ you will need to script, there is no choice.

2/ you need to create an underwater surface, transparent, specular, with a shifting bumpmap texture representing the wave. If it's a lake or any still water, you can leave the shifting texture.

3/ the underwater is not clear, it is a 'fog', depending on the murkiness, the turbidity of the water. In my prototype, I use 0.3-0.5 because the lake is polluted.

4/ the sea/lake bottom will generate a kind of dust when you touch it. It needs to be taken in consideration. These particle system will need to interact with the surface or you will get the same stupid bug I had (mud cloud going out of the surface !)

5/ you have to simulate the light in the water, I recommend you a spot light following the diver but above the surface. I am still working on that so this might not be the best solution

6/ One great thing to create a real underwater atmosphere (apart from the sounds) are the caustics. Caustics are these kind of shifting polygons of lights you see in outdoor swimming pool floors. Don't think about caustics in a dungeon or at night. But in a lake, during the day, it's a must. You can find an excellent caustic generator at http://www.dualheights.se/caustics/

Good luck.

Also realistic particles effects... Its really just trying to get the mood right. good skyboxes, rain, particles, lighting. It is not really a question that it easy to answer.

It really depends on the persons artistic mind.

For moon type lighting I use a normal direction light with a ever so slight blue tint to it.

In day light scenes use a light thats a little bit yellow.

Caves should have almost black ambient light, and not very much light. you really want the highlights to show.

A dungen should have torches(duh) And bumpmaps for stone add a lot of detail and mood.

I'm not quite sure what you mean by water scene?

Fantasy can not really look that good without glow and bloom postProcessing.. but try to make blown out texture.

For indoor scenes add point lights here and there to simulate GI. and spec/bump maps come in handy here also. As well as fake cubemap reflections.

and fog can be done quite realistically with particles... additive or additive soft shaders work well for fog.

I hope this is of some help to you!

For basic lighting, the A Lab Software guy made a really nice little library that is Unity-Indie friendly, and its super cheap:

http://www.alabsoft.com/lightlib.shtml

A lot of mood (in outdoor scenes) comes from skyboxes. Use GoogleImages and search for 'skybox', you will get many examples. After setting it up in the inspector you can adjust your light intensity, direction and color to match the skybox images. Often one directional light is enough.

(Shadows and Image Effects (e.g. Color Correction) are very helpful ... but Pro only ;-)