However sophisticated a camera's autofocus system may be - and these days they are very smart indeed - it still cannot know which part of the scene you actually want to in sharpest focus.
One example would be a large group of people arranged so that some of them are a bit further from the camera than others. Left to its own devices, the camera might well pick the nearest face to focus on, but if the guy at the back is too far behind he could well look a little blurred. It will be better to choose one single AF point and focus on the person about halfway.
Another example would be a small brown bird perched in a tree with lots of brown branches around and behind it. Again, the camera really cannot be expected to know which particular small area of brown - that's all it can "see", it doesn't know what is bird and what is branch - you want to be sharp. Using a single AF point helps to get around that problem.
One more - some kind of sporting event with players running about all over the field of play. You want to focus on one particular player, who sometimes might be just in front of, or even partially hiddeen by, another guy. That's pretty difficult for the camera to handle without some help from the photographer.
Edited to add...
By the way - none of this is a reason not to get a D7000. The advice to take control of your AF system and help the camera know what you want it to do applies equally well to your existing D90