StudentExaminations.sql

Create table  Students (student_id int, student_name varchar(20))
Create table  Subjects (subject_name varchar(20))
Create table  Examinations (student_id int, subject_name varchar(20))
Truncate table Students
insert into Students (student_id, student_name) values ('1', 'Alice')
insert into Students (student_id, student_name) values ('2', 'Bob')
insert into Students (student_id, student_name) values ('13', 'John')
insert into Students (student_id, student_name) values ('6', 'Alex')
Truncate table Subjects
insert into Subjects (subject_name) values ('Math')
insert into Subjects (subject_name) values ('Physics')
insert into Subjects (subject_name) values ('Programming')
Truncate table Examinations
insert into Examinations (student_id, subject_name) values ('1', 'Math')
insert into Examinations (student_id, subject_name) values ('1', 'Physics')
insert into Examinations (student_id, subject_name) values ('1', 'Programming')
insert into Examinations (student_id, subject_name) values ('2', 'Programming')
insert into Examinations (student_id, subject_name) values ('1', 'Physics')
insert into Examinations (student_id, subject_name) values ('1', 'Math')
insert into Examinations (student_id, subject_name) values ('13', 'Math')
insert into Examinations (student_id, subject_name) values ('13', 'Programming')
insert into Examinations (student_id, subject_name) values ('13', 'Physics')
insert into Examinations (student_id, subject_name) values ('2', 'Math')
insert into Examinations (student_id, subject_name) values ('1', 'Math')


select * from Students
select * from Examinations
select * from Subjects
SELECT S.student_id, S.student_name, T.subject_name, count(E.student_id) AS attended_exams FROM Students S CROSS JOIN Subjects T 
LEFT JOIN Examinations E on E.student_id = S.student_id AND E.subject_name = T.subject_name
GROUP BY S.student_id, S.student_name, T.subject_name
ORDER BY S.student_id,T.subject_name ASC;