If you really want to be confused, think about this:
When you really boil it down an engine ONLY makes torque. Horsepower numbers are derived by simply applying a mathematical equation to the amount of torque made by the engine.
Typically, the higher in the rev range an engine makes its peak torque, the more peak HP it will make. That's why F1 engines put out 800+ HP but very little torque, because they can rev to 18,000 RPM.
Conversely, if an engine makes its peak torque lower in the rev range (think big, turbo diesel engines) it will have relatively low peak HP numbers, but much more torque. Lots of torque is needed when you need to pull heavy things like a trailer or a boat.
The ideal combination for a fun sports car is to have lots of torque AND lots of HP; usually achieved by some form of forced induction. Otherwise your basic gasoline engine typically falls into one of two categories:
1) A small to medium displacement engine that makes its peak torque higher up in the rev range. Think of the V6 in the 370Z as an example or even the V8 in a Ferrari 458. These engines need lots of revs to make power and are often described as having a "linear" power band. In other words, the more revs the engine turns, the more power it makes.
2) A large displacement engine that makes its peak torque lower in the rev range. Think of the 7.0L V8 in the Z06 or the 8.4L V10 in the Viper. These engines make tons of power (torque) right off idle and have very good throttle response. They might not make as much peak HP as say a 6.0L V12 that revs to 8,500 RPM, but they make much more power down low where most of us do the majority of our driving.
Typically, a high-HP, high-revving engine is more fun/useful on a racetrack and a high-torque engine is more fun on the street. The instant throttle response of a large displacement, high torque engine is what gives you that "pin you to the back of your seat" feeling when you step on the gas. The trade-off being that you usually (not always) don't have as many revs to play with as the smaller displacement, high HP engines do.
I hope that helps some and you're not completely confused by what I just wrote.