What are the different majors?
select distinct major from student
How many students are studying each subject?
select major, count(*) from student group by major