Spoiler for hopefully the words I wrote next to some of the vids aren't too much of an eyesore:
Killer7 might have the best soundtrack of all time
Live A Live is like a top ten soundtrack, this is a bit of a Rocky/Street Fighter thing
heavier part starts at about :50
I'm a huge sucker for a good character select theme
the music levels in Rayman Legends were so fucking cool.
(after 20 seconds it gets heavier)
(this isn't much of a favourite, but the fact it's in such a cutesy game makes it stand out to me)
MVC2 not using unique themes for each character is so upsetting. It could have just recycled them from previous X-Men/Marvel vs. Street Fighter games and nobody would have complained.
can't say I really care for this too much as a song itself, but couple it with the context (the water ending in Silent Hill 2 and the fact that no other song in the game, at least IIRC, is like this so you'd probably expect the music to be more soft) it's so much better
The entire F-Zero X soundtrack, also. I can't post any because I refuse to leave any off.
I'm not any kind of music expert so I don't really know if any of these can be considered not rock or metal. If I really knew I could probably post like 50 more.
Halo 2. The last mission as Master Chief. Some of the rock music is timed to kick in at certain points and I thought this game handled it well. I'll try to upload a clip after work, but I think many who played the single player campaign will remember it. You're in the Citadel and fighting brutes.
Tekken 3 and Fifa 2000 have great soundtracks with some rock/metal songs on there. Also great mentions with the Killer Instinct and Doom tracks.
I'll post some examples. Not really sure though if these are rock/metal songs but that's how they sound to me. Also not sure if you're looking for actual songs used on games or songs composed for games, so I'll post both.