SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE student(student_id integer, student_name varchar(50));
insert into student values(1,'Tim');
insert into student values(2,'John');
insert into student values(3,'Roy');

CREATE TABLE subject(subject_id integer, subject_name varchar(50));
insert into subject values(1,'English');
insert into subject values(2,'Maths');
insert into subject values(3,'Hindi');

CREATE TABLE marks(student_id integer, subject_id integer, marks integer);
insert into marks values(1,1,50);
insert into marks values(1,2,100);
insert into marks values(1,3,40);
insert into marks values(2,1,50);
insert into marks values(2,2,30);
11 rows affected

select s.student_name, t.subject_name not_appeared
from student s cross join subject t 
left join marks m 
on m.student_id = s.student_id and m.subject_id = t.subject_id
where m.student_id is null


student_name
not_appeared


John
Hindi

Roy
English

Roy
Maths

Roy
Hindi


select s.student_name, t.subject_name failed
from student s 
inner join marks m on m.student_id = s.student_id
inner join subject t on m.subject_id = t.subject_id
where m.marks < 35


student_name
failed


John
Maths


select  
  s.student_name, t.subject_name,
  case when m.marks is null then 'not appeared' else 'failed' end result
from student s cross join subject t 
left join marks m 
on m.student_id = s.student_id and m.subject_id = t.subject_id
where coalesce(m.marks, 0) < 35
order by s.student_name, t.subject_name


student_name
subject_name
result


John
Hindi
not appeared

John
Maths
failed

Roy
English
not appeared

Roy
Hindi
not appeared

Roy
Maths
not appeared

student_name	subject_name	result
John	Hindi	not appeared
John	Maths	failed
Roy	English	not appeared
Roy	Hindi	not appeared
Roy	Maths	not appeared