What are the limitations of SQL(relational) databases?

I was wondering what are the limitations of a SQL database, any ideas on this? Does NO-SQL solve them and what trade-offs do they make in turn.