There's the usual barrelmancy and elemental stuff but there's also things such as: manipulating enemy AI (eg casting Minor Illusion to draw guards away from their posts or lure them into traps), using magic or manipulation to navigate the environment (eg moving objects to create stairways, or casting Reduce on yourself to reduce your weight and then having a party member with high STR pick you up and throw you onto a higher ledge), occasional alternate routes (eg breakable walls or destructible bits of scenery that can be used to slip by enemies or plan ambushes), and more. The game also has a stealth system and crime system which allows you to try and sneak into places and get arrested - the crime system is far from perfect but it's nice that it's there.
This all opens up a lot of routes for cool stuff - the goblin camp is still one of the best examples, where my solution involved splitting the party into three groups who each had to pull off their parts of the plan near-simultaneously. My solution for getting into the fortress in Act 3 was equally fun, my Gnome protag got picked up and thrown across the moat by the party's Barbarian and then had to sneak her way through the fortress solo until a way could be found to bring the others across. There's few other games that will just go along with whatever batshit plan the player comes up with, and have systems in place to allow it to work.
It has some of the best use of non-combat magic in any RPG I've played, no doubt. And the world is often designed around accomodating creative use of it, as opposed to something like Daggerfall/Morrowind where you can do insane shit with magic but in doing so you just break the game since Todd didn't anticipate it.