# $580. 统计各专业学生人数
# https://leetcode-cn.com/problems/count-student-number-in-departments/

# SQL架构
Create table If Not Exists Student (student_id int,student_name varchar(45), gender varchar(6), dept_id int);
Create table If Not Exists Department (dept_id int, dept_name varchar(255));
Truncate table Student;
insert into Student (student_id, student_name, gender, dept_id) values ('1', 'Jack', 'M', '1');
insert into Student (student_id, student_name, gender, dept_id) values ('2', 'Jane', 'F', '1');
insert into Student (student_id, student_name, gender, dept_id) values ('3', 'Mark', 'M', '2');
Truncate table Department;
insert into Department (dept_id, dept_name) values ('1', 'Engineering');
insert into Department (dept_id, dept_name) values ('2', 'Science');
insert into Department (dept_id, dept_name) values ('3', 'Law');

# Write your MySQL query statement below
select
    d.dept_name as dept_name,
    count(s.student_id) as student_number
from
    Department d
    left outer join Student s on d.dept_id = s.dept_id
group by
    d.dept_name
order by
    student_number desc,
    d.dept_name;

# clean-up
drop table Student;
drop table Department;