Those with a high starch content, like Russets, are great for baking and frying. Because of their starch, they don't hold together very well but are absorbent so are best paired with something to absorb (think butter and sour cream.) They are decent mashed, but care must be taken not to over work them, else you'll have a gluey mess. A starchy potato excretes a milky film onto the knife when sliced.
Those with a less starch, like most red-skinned potatoes, have a more waxy texture so they hold their shape while cooking. They work well for dishes like soups, stews, potato salad and scalloped potatoes where you would need to boil, slice, or roast. You can usually determine if a potato is waxy by its thin skin. If the skin feels very thin to the touch and you can easily scratch off a piece (don't do this before buying it!), it's probably waxy.
A third category would be medium starch potatoes which are more all-purpose, and they'll work in most potato dishes. Yukon Golds are a commonly available all-purpose potato. Knowing which potato to use for which dish is the first step toward success with potatoes. Most people who say they don't like potatoes probably haven't had a potato that was cooked properly. A scalloped Russet, for example, will be cloyingly dry and fall apart. Next I'll give you some specifics, based on a recent trip to the farmers' market when I came home with sixteen different varieties of potatoes. • Read on (and see a pictorial guide): Sixteen Kinds of Potatoes