I have a question. How does tuning come into play in guitar songs? See, I can find the tab of a song in the guitar by ear and I was taught that in tuning standard the fifth fret of the low e string is same sounding as the open a when plucked. This means I could derive a same sounding note from one string to another, isn't this correct? So why does one need to alternate tune sometimes? Is this because in some songs some notes go from low to very high? So that the player could easily play that song because the notes would be nearer to each other?
You've pretty much answered the question yourself. Sometimes the guitarist needs access to other notes (usually lower, such as in drop-D tuning) or to take advantage of chords that would be difficult/impossible to form in standard tuning. Two other options are wanting the change in sound (particularly with the open strings) or simply preference (preferring to play songs one way over another). Naturally, tunings can be combined with a capo to create many variations.
The change in sound is a big reason, especially in folk styles… I've learned one song that is played in DADGAD tuning for a specific type of sound, one with a lot of suspensions in most of the chords. It's simply not possible to play it in standard tuning and keep the same character of sound, and it's what makes the song what it is. However it's also used for the ease of playing specific things. It's also sometimes used as a songwriting tool, at least by some, as it lets you escape the progressions you know, and explore the ones you don't, completely by ear. I've used it on occasion, and though I'm about as far as you can get from a gifted songwriter, it has certainly helped get me out of many a rut.
In addition, some individual guitarists use alternate tunings, especially open tunings, to overcome physical difficulties. My father cut two fingers of his left hand off on a table saw, and now has only his thumb, pinkie, ring finger, and minimal use of his index finger (won't bend, so usable mostly just for barre chords). He has no middle finger any longer. He used to be a phenomenal guitarist, and although he primarily plays bass now, he does play guitar in open tunings, which help him do what he needs to.
The character of the sound, rather than ease of playing, is most often the reason seasoned guitar players use open tunings. I don't know what kind of music you anticipate playing in alternate tunings, but they are frequently used in everything from blues to country to rock. Guitarists from Mississippi John Hurt to Doc Watson to Keith Richards to Neil Young use alternate tunings.
To really answer your question, find a song you like that you know was done with an alternate tuning and try to learn it in standard tuning. You'll almost certainly succeed. Then relearn it in the alternate tuning and you'll appreciate why it was done that way.