Serenade of Water
How to Learn it
To learn it, you must first have beaten the Forest Temple and returned to the Temple of Time to learn the Prelude of Light from Sheik. You now have the choice of going through the Fire Temple or the Water Temple. Many do the Fire Temple first, as it is easier, and Navi gives you a hint about the Fire Temple before the Water Temple. Either way, when you are ready to do the Water Temple, head to Zora's Domain. Go over to Zora's Fountain, where Lord Jabu-Jabu used to be. Jump across the spinning, floating ice slabs in the lake towards the cave (you can get a Heart Piece here if you would like). Enter the cave to get to the mini-dungeon, Ice Cavern.
Go through Ice Cavern until you reach the end where you will fight the final enemy of the dungeon, before Sheik will appear. After a lengthy cutscene, and some more great words of wisdom, he will teach you the Serenade of Water, as well as giving you the Iron Boots. Before heading to the Water Temple, however, make sure you have at least one bottle filled with Blue Fire and go back to see King Zora. Unfreeze him, and he will give you the Zora Tunic. This is essential for the Water Temple.
How to Play it
In Ocarina of Time, it is played on the Ocarina using the following notes, which are played on the C-Buttons: A ↓ → → ←.
In the 3DS remake of the game, it is played using the following notes: L R Y Y X.
In Twilight Princess, the Serenade of Water can be heard as the background music when Link encounters Queen Rutela. These encounters are at the throne in Zora's Domain and after escorting Prince Ralis from Hyrule Castle Town to Kakariko Village.