NoSQL doesn't mean "no SQL", as in there is no SQL here. It means "Not Only SQL". Granted, the two biggest heavyweights in the NoSQL space, Apache's HBase and Cassandra, don't support SQL. However, it doesn't mean you can't use a domain specific query language on them.
Cassandra has CQL which means exactly what you think, i.e., Cassandra Query Language. This is very close in syntax to SQL but adapted to work with Cassandra.
Now, I encourage users of both systems to discover the power of Pig. Pig works works with both HBase and Cassandra. Personally, I find Pig much easier to use than SQL. The key to its simplicity is that it has a concept that represents the current row. If you've ever had to deal with non trivial SQL before, you know all too well the evils of sub-selects that could be easily avoided if you just had a way to refer to the current item when you needed to.