This is no longer true. We now have second-generation NoSQL data stores that provide strong consistency, fault-tolerance and ACID transactions, based on the sharded NoSQL architecture that scales easily.
If you're exposed to the accretion of business requirements you do not control, you'll eventually be pushed toward a definition of "power" that relational databases meet the best... except when the dataset gets large.
What you say is still true, of course, and will only become more true over time. As more work is put into technologies, they tend to become more well-rounded. Languages that used to have awful performance (100x C) are now closer to 3-5x, for one example. The best NoSQLs now have many of the traditionally relational benefits, and the best relational databases (e.g. PostgreSQL) are overcoming traditional deficits associated with "relational".
http://www.cs.cornell.edu/johannes/
and
http://www.cs.cornell.edu/annual_report/99-00/Demers.htm
?
More seriously: Al, Johannes, and many other notable researchers in the DB space have complex, nuanced opinions on NoSQL systems. And these opinions are being revised daily as new information comes in; we've seen some researchers go from decrying the whole NoSQL movement to becoming some of its fiercest proponents, seemingly overnight. I would do them disservice if I were to try to present them here.
So I'd encourage you to ask them directly. Or perhaps we could have a panel at a conference. That'd be fun if done right.
NoSQL came into style with the web, in which you have large data sets but don't need most of the relational features. You might have a trillion Wuphfs, but you'll never need ad-hoc queries because you're doing mostly CRUD on the Wuphfs. Sharded SQL is a painful way to deal with that problem space. NoSQL systems are often better adapted to it. NoSQL is almost always less powerful abstractly but easier to scale.
The major problem that I've seen with NoSQL systems is that they tend not to handle the edge cases very well. Remember the old joke about the man who goes to the doctor and says, "It hurts when I do this", so the doctor says "Don't do that". Sometimes, you have to "do that". Many NoSQL products are also quite low-level, requiring that more traditional database functionality be written in client code. That gets nasty-- slow, flimsy, hard to coordinate. You don't want, for example, clients to be responsible for generating sequential unique IDs because the clients all have to agree on what the rules are, in addition to generating a lot of communication overhead each time a new block of IDs is requested.
Furthermore, if you're using NoSQL to build something that will be marketed as a database product (e.g. within your company) and are exposed to requirement accretion, you'll find that the requirements are often inspired by expectations derived from relational models: ACID transactions being the big one.
I am not saying that SQL is the best for all problems, but it is a damn good technology. Personally, I think that a lot of SQL hate comes from the limitations of MySQL. If that's the source of your headaches, look into Postgres. Postgres is surprising in how modern and powerful it is.