Strictly speaking, tension is not the same as force, although it is sometimes described as the magnitude of the 'pulling force' experienced by an element (such as a rope).
The important thing to remember when resolving forces in classical mechanics and to understand tension is to apply Newton's three laws of motion. They are:
1st Law: an object with no external force will not change velocity
2nd Law: Force = Mass x Acceleration
3rd law: Every applied force (action) has an equal and opposite force (reaction).
So for the one dimensional cases you've given, think of the 'tension' of the rope as the magnitude of any pulling force it would be experiencing, bearing in mind that this tension is not actually a force (it has no direction), whereas the force whose magnitude it has, would be appear to be pulling the rope in opposite directions (as per Newton's 3rd law).
$T\leftarrow\rightarrow T$
So, back to your questions:
1 - When you pull on a rope tied to an immovable object, applying a force $F$, it reacts with force $-F$ (Newton's 3rd law) and the 'tension' in the rope is the magnitude of this force $F$.
$F\leftarrow\rightarrow F$
2 - If you pull on a rope which is tied a mass $M$ (initially at rest and free to move) it will accelerate towards you (Newton's second law). If you keep keep pulling the rope, keeping it taut by applying a constant force $F$ for a time $t$ and then remove the force thereby slackening the rope (no tension), the final velocity of the mass will be $v=at$ (neglecting friction). You can determine the force applied by $F=Mv/t$.
3 - If you apply a force of $X$ Newtons pulling a rope tied to a mass $M$ which I am holding, the tension on the rope is $X$ as long as the mass isn't moving. If I increase my pulling force to $Y$, the resultant force, $F=Y-X$ will pull you along with the mass, towards me. Note that we subtract the forces because they are acting in opposite directions. The resultant force $F$ will accelerate both you and the mass towards me at a rate $a=F/(M+m)$, where $m$ is your mass (assuming the mass of the rope is negligible). The tension on the rope will be equal to the magnitude of resultant force on the rope, which is $T =\lvert X-ma\rvert = \lvert X-m\times \frac{F}{M+m} \rvert= \lvert X-\frac{(Y-X)m}{M+m}\rvert$. Note that if your mass, $m$ is negligible, the tension of the rope becomes $X$, whereas if the mass of the body $M$ is negligible, the tension of the rope becomes $Y$. If your mass is equal to the mass of the body $(m=M)$ then the tension on the rope is $(Y-X)/2 = F/2$.
If I apply a pushing force $Y$ directly to the body of mass $M$, while you pull on the rope tied to it by applying a force $X$, the resultant force on the mass will be $F=X+Y$ (in your direction). The two forces are added not subtracted (since they are applied in the same direction towards you). The body will therefore accelerate in your direction (Newton's second law) under the total force $a=F/M$ and the tension on the rope will be equal to the magnitude of the resultant force, $(F-Y)=X$. Note in this instance, your mass is irrelevant, because the rope does not transmit my pushing force $Y$ to you (a rope does not work under compression!).
4 - If two bodies of mass $M$ are tied together with a rope and are moving in opposite directions at a speed $v$, they will each have momentum with magnitude $Mv$ but in opposite directions. Since neither mass is experiencing a force, they will continue to move at at constant velocities in opposite directions (Newton's 1st law), until the rope between them becomes taut. At that point, they will quickly decelerate and travel back towards each other. The rate of deceleration and subsequent speed at which they will travel towards each other will depend upon the 'elasticity' of the rope as well as the amount of 'friction' in the rope. In the case of an 'inextensible' rope with no friction, the rope will have a non-zero 'impulse' tension only at the instant it is taut. The two bodies will then move towards each other with the same velocity as they were previously moving away from each other (due to conservation of momentum).
Until you realise that tension is not the same as force, you may experience a little tension yourself as you grapple with the concept!
As an aside, you may come across some textbooks on engineering mechanics or materials which describe tension as a type of pressure or stress (force per unit area) as in 'tensile stress' applied to a truss member. If we define the area as a vector whose magnitude is the cross sectional area of the material under stress and whose direction is normal (perpendicular) to the cross sectional area, then the resulting force is the product of stress and area. In the most general sense, since the tension may have a different effect in different directions (anisotropic), the resulting force is not necessarily in the same direction as the area. In a three-dimensional Euclidean space, the tension is a tensor of rank 2. This is a linear transformation (mapping) with $3^{2}$ co-ordinates, something like a (3x3) matrix, which when 'multiplied' by the "area vector" produces the resultant "force vector" (not necessarily in the same direction).
However, since your examples are all dealing with forces in 1 dimension only, we can treat tension as a scalar (that is, a tensor of rank 0) whose magnitude is that of the force exerted by the rope under tension.
Best Answer
Your wife could be right about the rocket.
Whenever we say something is small physically, we need to be sure what it is small with respect to. In the case of a thread, the drag force exerted by air beats the centripetal force keeping it taut. Because centripetal forces scale with mass, a denser thread will work.
The issue facing a space elevator is different. For a rigid cable, we need the terminus to be above geostationary orbit. The height of the atmosphere is about 100km from sea level, while geostationary orbit is about 36000 km, so very little of the cable will be exposed to atmospheric drag. What net drag there is will be mostly from the prevailing winds, I suspect. The effect of this will be small but persistent. If it is never corrected, over time the elevator will drift away from vertical. Whether this effect is large enough to matter over, say, the lifetime of a civilization, I don't know.
For the other part of your question - what mass would we need to keep it taut? - I think your intuition about the rope is good. Here our concern isn't wrapping, but whether the rope will collapse. Imagine the rope is made of little beads of mass d$m$ connected by little massless cables of length d$h$. In the rotating frame, a bead at height $h$ in the cable experiences a total force $\mathrm{d}F = -\mathrm{d}m\frac{M_eG}{(h+R_e)^2}+T(h+\mathrm{d}h) - T(h) + \mathrm{d}m \, \omega^2(h+R_e) = 0$ for a system in equilibrium, where $T$ is the tension. If we call the mass per unit length of the cable $\lambda$, then we can turn this into a differential equation:
$$\frac{\mathrm{d}}{\mathrm{d}h} T(h) = \lambda \left ( \frac{M_eG}{(h+R_e)^2} - \omega ^2 (h+R_e) \right )$$ Integrating: $$ T(h) = \lambda \left ( M_eG \left( \frac{1}{R_e} - \frac{1}{R_e+h} \right ) - \frac{1}{2}\omega^2 (h^2 + 2 h R_e) \right) +C $$ We find the integration constant $C$ we look at the base of the cable. The tension there must be sufficient to keep the whole cable in place. The force the whole cable exerts at that point is
$$T(0)=C=\int_0^L \lambda \left ( -\frac{M_eG}{(h+R_e)^2} + \omega ^2 (h+R_e) \right ) \mathrm{d}h$$
where $L$ is the total length of the cable. We can see right away that this integral becomes more and more positive as $L$ increases, so even without finishing the integral we know there is some value of $L$ that will make this base tension positive. The condition that the cable stay taut is the condition $T(h) > 0 \;\forall\; h < L$ - that is, there is tension in the cable everywhere but the very end. Plug in the value of $C$ and you will find that if $C$ is positive, this is the case.
So all we technically need is a long cable. But it might be more efficient to have a counterweight at the end.