Count Student Number In Departments Problem


Description

LeetCode Problem 580.

A university uses 2 data tables, student and department, to store data about its students and the departments associated with each major.

Write a query to print the respective department name and number of students majoring in each department for all departments in the department table (even ones with no current students).

Sort your results by descending number of students; if two or more departments have the same number of students, then sort those departments alphabetically by department name.

The student is described as follow:

1
2
3
4
5
6
| Column Name  | Type      |
|--------------|-----------|
| student_id   | Integer   |
| student_name | String    |
| gender       | Character |
| dept_id      | Integer   |

where student_id is the student’s ID number, student_name is the student’s name, gender is their gender, and dept_id is the department ID associated with their declared major.

And the department table is described as below:

1
2
3
4
| Column Name | Type    |
|-------------|---------|
| dept_id     | Integer |
| dept_name   | String  |

where dept_id is the department’s ID number and dept_name is the department name.

Here is an example input: student table:

1
2
3
4
5
| student_id | student_name | gender | dept_id |
|------------|--------------|--------|---------|
| 1          | Jack         | M      | 1       |
| 2          | Jane         | F      | 1       |
| 3          | Mark         | M      | 2       |

department table:

1
2
3
4
5
| dept_id | dept_name   |
|---------|-------------|
| 1       | Engineering |
| 2       | Science     |
| 3       | Law         |

The Output should be:

1
2
3
4
5
| dept_name   | student_number |
|-------------|----------------|
| Engineering | 2              |
| Science     | 1              |
| Law         | 0              |


MySQL Solution

1
2
3
4
5
6
select dept_name, count(student_id) as student_number
from department
left outer join student 
on department.dept_id = student.dept_id
group by department.dept_name
order by student_number desc, department.dept_name




Related Posts

Investments In 2016 Problem

LeetCode 585. Write a query to print the sum of...

Count Student Number In Departments Problem

LeetCode 580. A university uses 2 data tables, student and...

Classes More Than 5 Students Problem

LeetCode 596. Please list out all classes which have more...

Find Customer Referee Problem

LeetCode 584. Given a table customer holding customers information and...

Customer Placing The Largest Number Of Orders Problem

LeetCode 586. Query the customer_number from the orders table for...

Big Countries Problem

LeetCode 595. Write a SQL solution to output big countries’...