Pfew, I can't think of it from the top of my head since it's been 10 years but there's a site that handles these fiendish puzzles which I've read a few months back - I can't find it right now though. One I remember in King Quest involving a shield and a dragon. In LSL2 there were several including money shortage, buying the wrong item, losing an item, not carrying an item you should have bought long before (like sunscreen in a store way before you knew you'd needed it), etc. so there's plenty - I think you just forgot them :p

I remember the shield thing from the walkthrough i've read in one mag, didn't sound weird. ;)
But SURELY, it's NOT "nearly every old Sierra game". :) [yeah, that's why i'm picky]

If you're referring to KQ1 <SPOILERS AHEAD!>, the shield or a bucket of water can be used IIRC (bucket of water being the "proper" way, but the shield can be used just because it makes you invincible or something). <END OF SPOILERS!>
Of course, there are several things that you can do wrong in Sierra games, like not haggling with the spaceship salesman (SQ1), not getting the code from the time machine (SQ4), not getting the mutton/cheese/saving the rat (KQ5), not checking your gun sights (PQ2), and many more.
I think that people seem to be overly bothered with the dying aspect, which I don't get. If you saved regularly (which most sierra games allow you to do, and in separate save files), then there isn't really a problem.
Oh and what about the Quest for Glory games was bad? That series in particular often had multiple solutions to problems. I can't remember any puzzle I couldn't get past, because the puzzles were usually quite obvious depending on your character class.

It's mostly the "having no clue what to do" aspect of Sierra games I don't like. I've tried to play all the KQ and SQ games but get bored of them due to not being able to figure out any puzzles, dying a lot and not having interesting stories. I mean I like a challenge, but I seem to hit a huge brick wall right at the start.

I still think the single worst puzzle I have ever come across is the lifeboat in LSL2. (Spoiler alert!) To survive, you have to, among other things, pick up a can of spinach on the ship before it sinks, and later, in the lifeboat, throw it away (otherwise Larry eats it and dies, because it's gone bad). But if you don't pick it up in the first place, you always die, no matter what - because you don't have any food. No, it doesn't make any sense at all.
Today it's really hard to believe game designers were once able to get away with stuff like this.

Not really. Gabriel Knight 3 as the most difficult adventure game ever ruins the list completely. Yes, the puzzle mentioned on the video is stupid, but that doesn't make the game particularly difficult.

I don't know why are people so worked up about the difficulty in GK3. Getting out of Rennes-le-chateau was easy, that mustache thing didn't even pinch me (although I must admit, even I thought it was corny). The most difficult thing about the first part of the game was the fact that you needed to look at the french title of the book "holy grail". It was somewhere in the back, out of sight. The middle of the game was much more difficult, but also fun.

Years ago, Old Man Murray used the moustache puzzle as a major point in a famous article on why the genre of adventure games died, and the reputation has stuck with GK3 ever since. Of course it's terribly unfair. It's not even the most difficult Gabriel Knight game. It was used in the article because it was a recent example (and there were not many games to choose from), not a particularly bad one.
Sometimes, information spreads in mysterious ways.
EDIT: Thinking about GK3 and its unfair reputation now, I also want to add I consider the La Serpent rouge sequence the single most finely crafted puzzle I have ever seen in an adventure game; and I have played pretty much all of the major ones. No cat moustache silliness can detract from that.

This game also needs more Police Quest, like number 3 and 4 were my favorite. 4 gave me nightmares as a kid xD

I would argue that it is a fair #1 for a few reasons. First, the puzzles in that game are ludicrously difficult though probably not the most difficult ever. Second, much like KQ2 they put the murderously difficult section right at the front which is an asinine thing to do since it means some players will blow $50 on a title and get virtually no game from it. Third, and most importantly it was the last adventure game Sierra ever produced and that single puzzle has tarnished the reputation of adventure games to this day. That kind of crap along with the absurdly difficult Myst clones killed adventure games for a long time.

I've finally watched the video and it really is deeply flawed. Among other things, Mainac Mansion is winnable with any combination of kids. He's clearly too much of a Sierra fanboy to be objective.
But coming back to GK3, not only does he quote the Old Man Murray review I've mentioned two posts back pretty much verbatim (there goes original research; without the review, I'm sure no one would even remember the puzzle), it still stands that this is not a hard puzzle at all. It makes very little sense, sure, but arriving at the solution one step at a time is really easy (using the black marker on the passport is a very obvious thing to do; and since Gabe draws a moustache on it, well, I suppose we'll be making a false moustache, won't we?). And I absolutely can't agree that GK3 killed the genre - the chronology there is all wrong. It came out at a time when it was pretty much dead and buried already. Myst clones certainly played their part, but most importantly, that time was the rise of the Impatient Gamer. And there was nothing the genre, by definition rather slowly moving, could do against that.
I think it's well established now that the sudden death of adventure games can be pinpointed quite exactly: Grim Fandango is released and doesn't sell even remotely as well as expected, year 1998. The genre fell down with a heavy crash at that moment; it just took a while for the dust to settle. Which includes GK3, already in development at that time.

No, any game that does this has a critical design flaw

I think adventure games died because the newer generation of gamers wanted more dynamic games since they had trouble concentrating on slower type of gameplay. Hence the success of FPS-games.
Also, I think that list isn't totally accurate, but I do agree about them KQs. Luckily I avoided going to therapy after playing those games by knowing when to quit (even back then, when I was 12 years old).