MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.30


CREATE TABLE students (
    student_id int,
    department_id int
);

INSERT INTO students (student_id, department_id)
VALUES 
(1, 1),
(2, 1),
(3, 2),
(4, 2),
(5, 3),
(6, 3),
(7, 4),
(8, 4),
(9, 5),
(10, 5);

CREATE TABLE department (
    department_id int,
    department_name varchar(255)
);

INSERT INTO department (department_id, department_name)
VALUES 
(1, 'Informatics'),
(2, 'Biology'),
(3, 'Physics'),
(4, 'Geography'),
(5, 'Chemistry');

CREATE TABLE exam_results (
    student_id int,
    grade int
);

INSERT INTO exam_results (student_id, grade)
VALUES 
(1, 6),
(2, 6),
(3, 4),
(4, 4),
(5, 3),
(6, 3),
(7, 2),
(8, 2),
(9, 6),
(10, 5);

Records: 10  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0



Records: 10  Duplicates: 0  Warnings: 0


SELECT * FROM students;

SELECT * FROM department;

SELECT * FROM exam_results;


student_id
department_id


1
1

2
1

3
2

4
2

5
3

6
3

7
4

8
4

9
5

10
5




department_id
department_name


1
Informatics

2
Biology

3
Physics

4
Geography

5
Chemistry




student_id
grade


1
6

2
6

3
4

4
4

5
3

6
3

7
2

8
2

9
6

10
5


SELECT d.department_name as department
      FROM exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id
      WHERE e_r.grade = 6
      GROUP BY d.department_name
      ORDER BY COUNT(e_r.grade) DESC
      LIMIT 1


department


Informatics