He's not claiming that query planners are so good that you'll never have to restructure your queries. I've written many queries that seem perfectly reasonable, but once I start optimizing I can see I'm doing things completely backwards. It's just like almost everything else programmers do. If I dash off quick program, I don't expect the VM figure out how to make it perform perfectly. Why would I expect database software to be different?
Except that SQL wasn't designed as a programming language. It was designed to be a declarative language that normals could use.
In a normal language, you have the freedom to express an algorithm in a billion different ways. You're expected to find the right way to write it. That SQL falls down here is a fundamental flaw in what was supposed to be it's greatest strength.
We may be getting off the query hint topic, but normals can use SQL Server as you've described. They can write queries and get data out, they just can't expect those queries to perform optimally without expertise.
In normal language, you and I have the freedom to convey our thoughts a billion different ways. But only one guy wrote The Sun Also Rises (for example.) Hemingway found the right way to write it.
(They also claim that query optimizers all over are good enough that they don't need the hints anymore, and the only reason that anyone asks for them is because they've gotten so used to messing with bad query planners that they don't know how to work with a good one. Which, regrettably, has not been my company's experience with Oracle 11g --- we haven't resorted to explicit hints, but we've restructured queries in other ways for order-of-magnitude improvements in performance.)