Student Departments

Problem Statement

Student Departments A university stores its students in the table STUDENT and its departments in the table DEPARTMENT . Every student has declared a major, recorded as the id of a department. Write an SQL query that reports, for every department, its name and the number of students majoring in it. Departments with no students must still appear, with a count of 0 . Table: STUDENT Column Data Type Description ID INTEGER Primary key, the student's id NAME VARCHAR(255) The student's name (two students may share a name) GENDER CHAR(1) The student's gender, M or F DEPT_ID INTEGER Id of the department of the student's declared major; a foreign key to DEPARTMENT.ID Table: DEPARTMENT Column Data Type Description ID INTEGER Primary key, the department's id NAME VARCHAR(255) The department's name Expected Output Return the following columns, in this order: Column Description department_name Name of the department student_count Number of students whose DEPT_ID is this department's ID ( 0 if there