If something is scary, it makes you afraid, or scared. Horror films — scary movies — are a very popular genre. Apparently people like to be frightened.
Scary is more a more casual way to say "frightening," and you can also use it to describe something that's just bizarre or odd or exceptional: you've become such a good golfer, it's scary; or, you cleaned up you room so fast, it's scary. Note that there is no "e" in scary, even though the verb to scare ends in one.