1+ -- 1280. Students and Examinations
2+ --
3+ -- Table: Students
4+ --
5+ -- +---------------+---------+
6+ -- | Column Name | Type |
7+ -- +---------------+---------+
8+ -- | student_id | int |
9+ -- | student_name | varchar |
10+ -- +---------------+---------+
11+ -- student_id is the primary key for this table.
12+ -- Each row of this table contains the ID and the name of one student in the school.
13+ --
14+ --
15+ -- Table: Subjects
16+ --
17+ -- +--------------+---------+
18+ -- | Column Name | Type |
19+ -- +--------------+---------+
20+ -- | subject_name | varchar |
21+ -- +--------------+---------+
22+ -- subject_name is the primary key for this table.
23+ -- Each row of this table contains a name of one subject in the school.
24+ --
25+ --
26+ -- Table: Examinations
27+ --
28+ -- +--------------+---------+
29+ -- | Column Name | Type |
30+ -- +--------------+---------+
31+ -- | student_id | int |
32+ -- | subject_name | varchar |
33+ -- +--------------+---------+
34+ -- There is no primary key for this table. It may contain duplicates.
35+ -- Each student from Students table takes every course from Subjects table.
36+ -- Each row of this table indicates that a student with ID student_id attended the exam of subject_name.
37+ --
38+ --
39+ -- Write an SQL query to find the number of times each student attended each exam.
40+ --
41+ -- Order the result table by student_id and subject_name.
42+ --
43+ -- The query result format is in the following example:
44+ --
45+ -- Students table:
46+ -- +------------+--------------+
47+ -- | student_id | student_name |
48+ -- +------------+--------------+
49+ -- | 1 | Alice |
50+ -- | 2 | Bob |
51+ -- | 13 | John |
52+ -- | 6 | Alex |
53+ -- +------------+--------------+
54+ -- Subjects table:
55+ -- +--------------+
56+ -- | subject_name |
57+ -- +--------------+
58+ -- | Math |
59+ -- | Physics |
60+ -- | Programming |
61+ -- +--------------+
62+ -- Examinations table:
63+ -- +------------+--------------+
64+ -- | student_id | subject_name |
65+ -- +------------+--------------+
66+ -- | 1 | Math |
67+ -- | 1 | Physics |
68+ -- | 1 | Programming |
69+ -- | 2 | Programming |
70+ -- | 1 | Physics |
71+ -- | 1 | Math |
72+ -- | 13 | Math |
73+ -- | 13 | Programming |
74+ -- | 13 | Physics |
75+ -- | 2 | Math |
76+ -- | 1 | Math |
77+ -- +------------+--------------+
78+ -- Result table:
79+ -- +------------+--------------+--------------+----------------+
80+ -- | student_id | student_name | subject_name | attended_exams |
81+ -- +------------+--------------+--------------+----------------+
82+ -- | 1 | Alice | Math | 3 |
83+ -- | 1 | Alice | Physics | 2 |
84+ -- | 1 | Alice | Programming | 1 |
85+ -- | 2 | Bob | Math | 1 |
86+ -- | 2 | Bob | Physics | 0 |
87+ -- | 2 | Bob | Programming | 1 |
88+ -- | 6 | Alex | Math | 0 |
89+ -- | 6 | Alex | Physics | 0 |
90+ -- | 6 | Alex | Programming | 0 |
91+ -- | 13 | John | Math | 1 |
92+ -- | 13 | John | Physics | 1 |
93+ -- | 13 | John | Programming | 1 |
94+ -- +------------+--------------+--------------+----------------+
95+ -- The result table should contain all students and all subjects.
96+ -- Alice attended Math exam 3 times, Physics exam 2 times and Programming exam 1 time.
97+ -- Bob attended Math exam 1 time, Programming exam 1 time and didn't attend the Physics exam.
98+ -- Alex didn't attend any exam.
99+ -- John attended Math exam 1 time, Physics exam 1 time and Programming exam 1 time.
100+
101+
102+ select a .student_id , a .student_name , b .subject_name , count (c .subject_name ) as attended_exams
103+ from Students as a
104+ join Subjects as b
105+ left join Examinations as c
106+ on a .student_id = c .student_id and b .subject_name = c .subject_name
107+ group by a .student_id , b .subject_name ;
0 commit comments