Top tier spells can bring your whole enemys army down. Spells give you many strategic choices and I think this is still a visual joy to watch.
A somewhat niche example, since you were pretty much limited to being a spellcaster (and no other class). But yes, raising a volcano in the middle of your opponent's army, or bringing down a rain of exploding frogs is pretty awesome.
For a completely different take on the genre,
Darklands. 'True' magic is demonic in nature, and only practiced by witches and devils. Your characters instead use the power of faith to pray (to one or more saints) for miracles.
Arcanum allows you to feel like the avatar of destruction in the late game. There's a spell,
Tempus Fugit, which doesn't just make your opponents twice as slow, it also makes you party
twice as fast. You end up being four times faster than your foes. It makes for pretty short battles, which is just as well, since you usually end up nearly collapsed from the lack of stamina (Arcanum Magick doesn't use mana, but rather your character's stamina, also used for running and swinging your sharp implement of chioce).
A similar spell system can be found in
Betrayal at Krondor. Your spellcasters use both stamina
and health (if they run out of stamina) to cast spells. Most of the spells have quite creative names, like
Fetters of Rime,
Grief of 100 Nights,
Gift of Sung, and, last but not least
Mad God's Rage.