Analog televisions do not need to be "tuned in" to the composite cable signal.
Huh, you need to select "composite input" on your TV, don't you? That means it sets up its internal receiver chain so that it can deal with the signal.
And it's really what happens. Essentially, selecting the composite input configures your TV to tune to a really low frequency, which it receives through that RCA connector.
So I have a few questions:
And we might have a few answers!
What is the real difference between the signal on a composite cable and the RF signal that comes over the air?
The carrier frequency. In fact, in early 1990s lingo, when I started playing around on a TV, the technical term used in the manual was "FBAS" signal (and that's German for Farb-Bild-Austast-Synchron-Signal, Color Video Blanking Synchronization, CVBS in English). And it's identical to what is in picture information on your favourite analgue TV station at their carrier frequency, but at a carrier frequency that's effectively putting the whole signal symmetrically around 0Hz – Baseband.
I understand that the composite cable signal does not have audio, but apart from that, what is different?
Nothing, really. It's more or less PAL, SECAM or NTSC (depending on which analog TV standard your composite video source produces), mixed to baseband.
What frequency is the signal transmitted down the composite cable?
Well, strictly speaking, the carrier would be at 1.25 MHz, I think, with the signal then extending from somewhere above 0 Hz to ~ 5.25 MHz, depending on the video bandwidth.
Would it be possible to transmit at a different frequency down the composite cable, say for example at a frequency range that an analog TV could pick up?
It's already doing that.
Also, no. Composite video cables are a nightmare. They are better (if even) audio cables. The fact that you shove through megahertzes of bandwidth through an RCA connector is already an indication of how terribly designed that whole consumer standard is:
Low cost above literally everything.
And if yes, would it be possible to just have that signal transmit into the air, effectively making the composite cable a transmitting antenna?
Modulator boxes. Was very common before every TV hat a composite input.
And could an analog TV sitting nearby then pick up that analog TV signal?
Be very careful about transmitting in a band that your analog TV can pick up – that's illegal. These frequencies are either still in use for analog TV or have been repurposed for digital TV or other applications (this depends on your country's legislation), and you are not entitled by law to use that spectrum.
So, yes, modulator + amplifier + antenna = transmitter, but also = hefty fines or jail if someone else also picks up your signal and reports you to your national spectrum authority (FCC, BNetzA, OFCom, …).