That is the question.
SQL Databases
Pros
Flexible updating. SQL allows you to quickly update databases on the fly.
Contains relatively few commands. Even though you can always look up commands and memorization isn't strictly necessary, whatever you're doing with SQL will take less time if you can use a small number of commands without research.
Universal. This may not be true forever, but at this point SQL is almost universally understood across many different platforms. The lingua franca of the database world if you would.
Cons
Flexible updating. I've put this as a con for a similar reason that it's a pro. Because it's so flexible and databases are easily updatable, users can manage it themselves. Depending on the users that may or may not cause more headaches.
Scaling. Because the structure is a little more rigid, it's more difficult to scale SQL databases up when needed.
NoSQL Databases
Pros
Flexible structure. NoSQL makes things go into one giant table. This eliminates the need for joins and enables NoSQL databases to scale up when needed. The power requirement for reads is significantly less so they can go faster.
Management. NoSQL databases require significantly less management which mean that a company or organization has to spend less time and resources to staff or contract out database management.
Cost. Given their tendency to be located on groups of lower-cost servers, rather than the higher-cost servers usually used by SQL databases, storing data is less expensive.
Cons
Young. NoSQL is still relatively young so there is not as much support for it yet in terms of documentation or user guides.
Expertise. Because of its more recent appearance on the scene, there are fewer experts with whom you could consult if you needed help with a NoSQL database.
Flexible structure. Much like SQL, NoSQL's particular brand of flexibility, this time with its structure, also causes some problems. Because it's just one giant table it requires more power to write and contains more redundancy.
Personal View
Right now I don't have too much experience with either. Judging from what little I've experienced of SQL databases and knowing nothing but what I've researched of NoSQL databases, my opinion's pretty split. It's possible that in the future when documentation and support for NoSQL is much greater (I'm assuming this) I will be more excited to work with it. I think that it has great potential, but is not really there yet for me. The resources available for learning SQL aren't that great even now though, so perhaps I shouldn't hope too much for the future.