The main difference is that many options are 'locked out' and set to their defaults in Full Auto (Auto) mode. The manual has all the info you'll need on what is available and when. In decreasing order of automation the modes are...

Full Auto

Do everything - leave only the most basic functions open to the photographer.

Scene modes

Like Full-Auto with preset values for the chosen conditions.

Programmed modes

Unlike the auto/scene mode many settings are user controlled in the P/S/A modes such as...

• Metering mode
• Auto Focus mode
• Exposure lock/compensation
• Flash settings
• White balance
• ISO Sensitivity

Also, advanced options (i.e. bracketing, HDR, Active D-Lighting, multiple exposures) are also available. In P-mode where shutter/aperture is calculated for you, the dials allow you to shift the balance between shutter/aperture.

These modes offer a good balance of automation and user control. So, any situation where you want more control of the camera than that is offered by Auto mode, but don't want to have to do everything yourself would be a good use case for the program modes (P/S/A).

P Flexible Program - Do most things but allow plenty of scope for overrides

A Aperture Priority - Use the photographers chosen aperture and balance the shutter speed up to get a good result.

S Shutter Priority - Use the photographers chosen shutter speed and balance aperture settings up to get a good result.

Manual

Automate as little as possible.

Scenarios

Probably, the best way to summarise the differences is that P is the 'photographer's auto', see it as an equal to the A/S modes. It's intended for someone who knows what they're doing but is happy to let the camera do the work and will step in to enhance or inform the camera's decisions. Adding fill-in flash or over/under-exposing would be some good examples.

The scenario when you dial in Auto is when you're giving a camera to someone who knows nothing about photography, usually with the only instruction "Look through the hole, press the button!"