I'm pretty sure we've all played at least one. A game that has had you close to tears of frustration or put you in a position of needing a new controller/keyboard/mouse/monitor/hand.
Recently it has to be The Feeble Files. But then I was never much for adventure games.
I remember most beat 'em ups would drive me nuts as a kid. Street Fighter 2, Killer Instinct, Mortal Kombat all spring to mind.

Oh god I miss that, was it ever ported to PC or am i going to have to settle for SNES emulation?
On-topic: Alien-Hominid. I've never ragequit so many times in my life.

Emulation I'm afraid

Well Tomb Raider Underworld did that when I jumped a fraction of a second after the camera started to move and so I jumped in exactly the wrong bloody direction
The Movies has got to be one of the more frustrating if you're playing in career mode, nobody is ever bloody happy and always finds something to bitch about. Then again I suppose thats a pretty good simulation of the real world
I've never finished Escape From Monkey Island because of that godawful monkey kombat rubbish they put in, that makes me go ARRRGH! and not in the fun pirate way.

Dark Crusade is making me go "FU" and "WTF?!?!?!?!?" at this time.

Soldiers: Heroes of WW2. It hurts so good...
The race in Mafia.
Call of Duty can be extremely frustrating at times.
Left 4 Dead can become a total clusterfuck if your team mates are bad, and/or you're playing on Expert.
One game that actually drove me to tears when I was a kid was Dinotopia. There's one puzzle where you're supposed to jump off a cliff onto the back of a moving pterosaur, but I never got the idea that you're supposed to call it first, so I kept jumping off over and over until I just started crying.
Oh yeah, I never finished Independence War 2 because it was so damn easy to die at a moment's notice and I generally did after I'd finished a mission.

Oh god yes! It was so damned hard! Well it was until you realised you only had to calm down and drive sensibly and you'd be fine. The patch gave you the option to skip it didn't it? Or was it choose the difficulty? Either way I seem to recall it made it too easy then.

Dark crusade put me off faster paced real time strategy titles for a very long time.

Super Contra. And the Teenage Mutant Ninja Turtles on the NES.

Sid Meier's Pirates! See what I did there? :P
But seriously, the Pro Evolution Soccer series is the most angrifying set of games I've ever played, I usually play games for fun and relaxation, but these games bring out all the wrong kinds of competitiveness in me, usually resulting in the controller meeting the floor at high speed.

The last 2 entries in the GTA 3 line. Stupid RC missions be damned! Anytime I have to control something flimsier than a hooker's underwear, I go into RAGE mode and use the actual game discs as skeet shoot.

Now I come to think of it, there was a puzzle in Phantom Hourglass on the DS that drove me nuts. I had to look it up in the end and turned out you had to shut your DS and open it again to solve it.
Speaking of DS games, Trauma center was bloody hard and cause my stylus hand alot of pain.

Space Rangers 2 and Kings Bounty are sooo difficult on even an easy mode. Russian game makers can't balance the difficulty.

What was difficult about Space Rangers 2?? Just asking because my experience was quite different on normal...