There are many good theme songs... Legend of Zelda Link's Awakening (GB / GBC) is awesome, Magic of Scheherazade (NES) is another one. Maniac Mansion (NES, C64, PC) is catchy as well.
Theme songs of Devil may cry are awesome beside it if you are asking about childhood stuffs then, Man! i used to love mario's theme that was really annoying but yet good for listening it gets stucked in your mind easily
Firstly, The Legend of Zelda has one of my favorites! I'm biased, though, as it's my favorite series. Final Fantasy 7 theme and "One Winged Angel" Final Fantasy 8 "Liberi Fatali" Final Fantasy 9 "Melodies of Life" Dragon Quest theme Chrono Cross "Time's Scar" Chrono Trigger "Frog's Theme" ICO "You Were There" Lots more, but I'm not going to take up everyone's time.
Uh... Theme Song, right? I was just confirming because most of the themes they are mentioning are BGMs and sound effects. Nevertheless, for my favorite songs, I am enthralled with Ayumi Hamasaki's songs from Thousand Arms on PSOne, Suteki da Ne on Final Fantasy X, and Eyes on Me on Final Fantasy VIII. For Theme Music, I am mesmerized with Dance of Pales from Castlevania: Symphony of the Night, and Aeris' theme on Final Fantasy VII. I also find it pleasurable to listen to OCRemix versions of Sonic the Hedgehog stages, regardless of the series.