Students And Examinations Problem
Description
LeetCode Problem 1280.
Table: Students
1
2
3
4
5
6
7
8
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| student_name | varchar |
+---------------+---------+
student_id is the primary key for this table.
Each row of this table contains the ID and the name of one student in the school.
Table: Subjects
1
2
3
4
5
6
7
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
subject_name is the primary key for this table.
Each row of this table contains the name of one subject in the school.
Table: Examinations
1
2
3
4
5
6
7
8
9
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| student_id | int |
| subject_name | varchar |
+--------------+---------+
There is no primary key for this table. It may contain duplicates.
Each student from the Students table takes every course from Subjects table.
Each row of this table indicates that a student with ID student_id attended the exam of subject_name.
Write an SQL query to find the number of times each student attended each exam.
Order the result table by student_id and subject_name.
The query result format is in the following example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
Students table:
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1 | Alice |
| 2 | Bob |
| 13 | John |
| 6 | Alex |
+------------+--------------+
Subjects table:
+--------------+
| subject_name |
+--------------+
| Math |
| Physics |
| Programming |
+--------------+
Examinations table:
+------------+--------------+
| student_id | subject_name |
+------------+--------------+
| 1 | Math |
| 1 | Physics |
| 1 | Programming |
| 2 | Programming |
| 1 | Physics |
| 1 | Math |
| 13 | Math |
| 13 | Programming |
| 13 | Physics |
| 2 | Math |
| 1 | Math |
+------------+--------------+
Result table:
+------------+--------------+--------------+----------------+
| student_id | student_name | subject_name | attended_exams |
+------------+--------------+--------------+----------------+
| 1 | Alice | Math | 3 |
| 1 | Alice | Physics | 2 |
| 1 | Alice | Programming | 1 |
| 2 | Bob | Math | 1 |
| 2 | Bob | Physics | 0 |
| 2 | Bob | Programming | 1 |
| 6 | Alex | Math | 0 |
| 6 | Alex | Physics | 0 |
| 6 | Alex | Programming | 0 |
| 13 | John | Math | 1 |
| 13 | John | Physics | 1 |
| 13 | John | Programming | 1 |
+------------+--------------+--------------+----------------+
The result table should contain all students and all subjects.
Alice attended Math exam 3 times, Physics exam 2 times and Programming exam 1 time.
Bob attended Math exam 1 time, Programming exam 1 time and didn't attend the Physics exam.
Alex didn't attend any exam.
John attended Math exam 1 time, Physics exam 1 time and Programming exam 1 time.
MySQL Solution
1
2
3
4
5
6
7
8
9
10
11
12
select t.student_id, t.student_name, t.subject_name,
if(e.attended_exams is null, 0, e.attended_exams) as attended_exams
from
(select *
from Students
cross join Subjects) t
left join
(select *, count(*) as attended_exams
from Examinations
group by student_id, subject_name) e
on t.student_id = e.student_id and t.subject_name = e.subject_name
order by student_id, subject_name
LeetCode Database - Easy
LeetCode 1050
LeetCode 1068
LeetCode 1069
LeetCode 1075
LeetCode 1076
LeetCode 1082
LeetCode 1083
LeetCode 1084
LeetCode 1113
LeetCode 1141
LeetCode 1142
LeetCode 1148
LeetCode 1173
LeetCode 1179
LeetCode 1211
LeetCode 1241
LeetCode 1251
LeetCode 1280
LeetCode 1294
LeetCode 1303
LeetCode 1322
LeetCode 1327
LeetCode 1350
LeetCode 1378
More LeetCode Database
MySQL Tutorials