then I am not sure which f/stop to get everything in focus and sharp.
It varies.
Generally speaking - with a 1.6x crop (current gen consumer and prosumer dslrs) - f/9.0 and smaller (larger number) will give you a decent amount of DOF. However - it all depends on the scene! If you are shooting say a Walmart parking lot for some reason - f/9.0 will give you plenty of DOF. Shooting a mile long valley - or a mountain from a great distance will require more like f/22 on a tripod and maybe an ND filter at like a 1 second exposure will get you the largest DOF and sharpest view.
[1]This is one thing that I find DSLR a lot less forgiving than P&S.[/1]
Apples and oranges. The f/stop number displayed on a point and shoot is not the same f/stop number for a dslr. This is for the same reason that focal lengths aren't the same either. A p&s sensor is less than 1/10 the area of an APS-C (current gen dslr 1.6x)
http://en.wikipedia.org/wiki/File:SensorSizes.svg
While the size of the hole is constant - it's effect is not - because the image is focused very close to the image sensor due to its miniscule size. You can figure 3-4 stops down from what your point and shoot tells you - for its actual value.
Thus if your P&S says f/2.8 - its really more like f/11, and when it says f/8.0 thats really f/32. It happens to work for the market at which point and shoots are aimed. (set on auto press the shutter, the whole scene is in focus - Aunt Barb is happy).
Shoot your dslr at f/11 all day and see how most pictures have the whole scene in soft focus.
Andy is mostly right - since nearly all lenses have a max aperture of f/3.5-f/5.6 about 1 stop down is usually the sweet spot. Its also a rather narrow DOF for mid-long landscapes.
In general, are we supposed to test and memorize the sharpest aperture for a given focal length?
No. You don't always want the sharpest imaginable picture. If you were shooting a waterfall for instance, or were trying to imply motion than you would use other tricks to convey the effect. Nice bokeh specular highlights only come out wide open, otherwise its octogonal. Sunsets are often at f/22 or smaller. Shooting in the Everglades (where you can see for miles around) - I never went above f/13 unless I happened upon a gator or heron, then I went past the sharpest point to increase bokeh 1/3 stop down from wide open.
Sharpness generally goes down with increasing focal length, but the sharpest possible aperture is within a stop for the whole range. Sometimes you can't shoot at that aperture because its too bright, or too dark. Not much you can do. This is where films strength lies - it's latitude. You can totally shoot film wrong and it will mostly come out OK. Digital is far less forgiving. You should know where your lens sweet spot is, and for those photos that matter - get as close as you can.
In all - you use your camera as a tool. There is a lot you don't know it can do.