Most modern cars use a dual tone car horn that actually produces two different notes. The interaction of these two frequencies creates harmonics and are easier for humans to distinguish from general noise. Because of historic manufacturing legacies, most car manufacturers have the same tones in all their cars, and depending on the era, most cars of a single country of origin would have tones in the same key. These common horn keys (F, G & C) are all in the middle octaves, probably because it's easy for humans to hear (we've evolved acoustic and mental sensitivities to noises that are similar in tone to our voice). Most modern horns use a vibrating plate of metal to generate its tone, but trains and semi-trucks take advantage of their onboard air compressors to make really loud sounds.
Marketing plays a big role in what a horn sounds like. You can't have a weedy sounding horn on a big luxury car or a bull deep horn on a sub compact.
A classic example of marketing driven sound though is the horns fitted to the 68 Roadrunner. Chrysler paid $50K a year to Warner Brothers for the rights to the image and "Meep! Meep!" sound of their Road Runner character. And then they paid $284 for retooling and a 47 cent cost penalty per unit (a significant investment on a per car basis in 67) to get a manufacturer to build a horn that would emulate the sound.
I don't have an answer, but I'd like to say /r/askscience may also provide good answers to this interesting question.