SQL query for the given table

I have 2 tables, Student and Supervisor:

• STUDENT(supervisorid(pk),name,email....)
• SUPERVISOR(supervisorid(pk),name,email....)

Now I need to print supervisor name, email and t开发者_Go百科he # of students under the supervisor (they will have same supervisor id). Something like:

select supervisorname,
       supervisoremail,
       tot_stud as (select count(*) 
                           Phd_Student s 
                     where s.supervisor_id = r.supervisor_id) 
  from Phd_Supervisor r

Can you please tell me the SQL query for this.

You will want to use the group by clause for this query. You can specify all of the fields that you want to display, as well as the count(*), join the tables, relate the tables , and then put in your group by clause, listing all of the display fields,(without the count(*)), as those are the fields you are grouping the students by to get their count.

select supervisorname,
   supervisoremail,
   (select count(*) 
    from Phd_Student s 
    where s.supervisor_id = r.supervisor_id) as tot_stud
from Phd_Supervisor r