The directions to the Wind's Requiem are written on a tablet on Dragon Roost Island, at a shrine to the wind god Zephos. When Link first attempts to play the Wind's Requiem on the Wind Waker, Zephos will appear to greet him.
This song can change the direction of the wind, as long as Link is outside. This is useful when Link is out on the ocean on his boat, because it is a lot faster to travel with the wind filling the sail. It can also be used to make treasure chests appear when Link is standing on certain tiles, which is used to find all the Triforce Charts.