mysql> select student.sname, count(study.cname) from student left join study on (student.sname=study.sname) group by student.sname;
+-------+--------------------+
| sname | count(study.cname) |
+-------+--------------------+
| A | 2 |
| B | 0 |
+-------+--------------------+
2 rows in set (0.00 sec)