Actually term SQL database is not quite correct, because SQL is a language for databases. The more correct question would be Relational or NoSQL database? I will also use SQL because it's more familiar to people.
There are some differences between SQL and NoSQL database, and each one has it's use cases.
Let's first start with SQL databases, SQL language is using Structured Query Language (SQL) to insert / update / delete and read data from database. Your data is stored inside Tables, you also manage those tables using SQL commands. The other distinguishing feature of SQL databases (sometimes referred as Relational databases), is you have predefined properties for each data object. Actually there isn't object in SQL databases, each object data is stored as rows inside table. As you see in real world tables - those tables has limited number of fields which. Each column in table corresponds to 1 field.
Also, it's called Relational databases because, you store data in Tables and those tables can be related to each other, for example if you have "exams" table and you have another "students" table.. you can create another table called "grades" where you can insert which student (using student id) have got which grade from which subject. Later you can use "where" or "join" clauses to retrieve information from multiple tables structured as you need.
The most well-know SQL database is Mysql, but there is other ones like Microsoft SQL, Oracle, PostGre ..etc.
When it's better to use SQL databases?
1. When you are starting programming and you want well structured database.
2. It's easier search, order by properties. When you are creating search based application it's easier to do with SQL database.
3. There are a lot of ORM tools (like Hibernate) which helps you to do operations with SQL databases without even writing SQL queries.
So, Why we need NoSQL databases?
First let's start explainin what's NoSQL database? it's a database that consists of documents. Documents can be thought as rows in Relational database, but key difference is It doesn't have a structure. Each document can have it's own properties. For example: you have collection of animals database, some animals has 4 legs, some has 2 legs. Some anymals have wings properties, some has hands. You can think of many examples in real world. Another example is contracts, each contract has it's own terms, that's why it's easier to store those contracts in NoSQL database.
As you don't have structure in NoSQL database, it means you don't have relations between collections (NoSQL databases doesn't have tables). All information linking should be done in programming language you use to consume database.
When it's better to use NoSQL databases?
1. When you need flexibility when storing and retriving data, without thinking about relations and scheme.
2. When you need highly scalable data storage. Adding more data in NoSQL is much easier and you can scale horizontally.
3. When you have a application that will need a lot of reads per second, as you know SQL databases can't be stored in multiple servers very easily - but NoSQL databases is built for that.