Today, in a distributed cloud environment, there is no good DB that can have both ACID and SQL support, at the same time keep the performance scale. So, there is NoSQL.
NoSQL means Not Only SQL. It does not means NO SQL. MongoDB-like applications have set up a bad example for its followers. A NoSQL DBMS might not use SQL as its base query language, but it at least should support SQL as a higher layer query language, just like what FoundationDB tries to do. Of course, the problem is ACID with the flexibility of SQL language, which is a rather difficult problem for a sharding DB, who becomes more and more common since cloud computing is conquering the server world. SQL language itself is not difficult to carry out, ACID with the ability to support complex SQL is.
But ACID is a must-have feature in many apps, not only bankers needs ACID, all app developer who wants to make a robust app must have transaction as one of their basic tools. No one can accept an app if it only acts normally when the user is lucky. Those who want to throw away ACID will only find they are implementing their own ACID solutions later.
There are many ways to overcome ACID implement problem. In the cloud, locking is an unacceptable way, unless there is some way to lock and sync at a very small, accurate level, which is not an easy job for 100+ sharding servers. Log and check(MVCC) is another way, which is easier than locks, and implemented in many DB solutions.
If ACID is possible in a cloud, SQL will be, too. But it may exist as a layer on an ACID system based on some simpler API. Whatever, SQL will not be ended by NoSQL and cloud, it will still be used in many places for whoever wants to keep data update easy(or even possible). Maybe one day there will be only copied and not reference in DB world, but I think the day has not come yet.