select 'Courses along with names of students currently taking them'
select c.D_Code, c.C_no, s.S_Name
from class c, student s, enrollment e
where s.SSN = e.Student_SSN
and e.Class_no = c.Class_no
order by s.S_Name



select 'Classes in which John got an A'
select t.D_Code, t.C_no
from transcript t, student s
where t.Student_SSN = s.SSN
and s.S_Name like 'John'
and t.Grade = 'A'



select 'Classes without a prerequisite'
select distinct c.D_Code, c.C_no
from course c
where not exists
(select * from prereq p
where p.D_Code = c.D_Code
and p.C_no = c.C_no)



select 'Student that is enrolled in INFS614 and has taken all the prerequisites'
declare @D_Code varchar (4)
declare @C_no int
select @D_Code = 'INFS'
select @C_no = 614
select distinct s.SSN
from student s, enrollment e, class c, transcript t, prereq p
where c.Class_no = e.Class_no
and s.SSN = e.Student_SSN
and c.D_Code = @D_Code
and c.C_no = @C_no
and s.SSN = t.Student_SSN
and p.P_Code = t.D_Code
and p.P_no = t.C_no
group by s.S_Name
having count(t.C_no)>= (select count(*) 
from prereq p
where p.D_Code = @D_Code
and p.C_no = @C_no)