Any LucasArts PointnClick adventures - puzzles that have you going "oh DUH!" (or "WTF?") when you finally figure out somethin you spent 5+ hours trying to figure out what to do.
Discworld II has some even more far-fetched puzzles... for example, at one point you need some mouse blood for some magic spell.
As far as I remember, this is what you had to do: Find corn, obtain some booze, soak the corn in the booze, feed it to a rooster, wait for the rooster to get drunk so you can grab it, take it to a catacomb in the graveyard, get it sober again, and get it to crow somehow. A resident vampire hears the rooster and thinks its daybreak, so he goes to sleep, putting his dentures in a cup next to his coffin. You can then swipe the vampire dentures to get the blood from the mouse.
(I don't remember where to get the mouse. ;-)
At another point you need to break down a wall. There's a large wrecking-ball machine, but it's not strong enough. You need to change the 1 Ton stick-on label on the ball to say 10 Tons, then it magically works.
(But other than two two things, a great game)