SQL query exists all

Question

I need a query to list students that takes all available courses using two below tables.

declare @Course table (CourseID nvarchar(4))
declare @CourseStudent table 
                       (
                           CourseID nvarchar(4),
                           Student nvarchar(50)
                       )

insert into @Course values ('SOS1')
insert into @Course values ('MAT3')
insert into @Course values ('FEN2')

insert into @CourseStudent values ('MAT3', 'Mehmet')
insert into @CourseStudent values ('SOS1', 'Ahmet')
insert into @CourseStudent values ('MAT3', 'Ahmet')
insert into @CourseStudent values ('FEN2', 'Ahmet')
insert into @CourseStudent values ('SOS1', 'Ali')
insert into @CourseStudent values ('FEN2', 'Ayse')

select * from @Course

select * from @CourseStudent

Answer 1

You could nest some not exists queries. The solution below translates as follows:

1. Select all unique students...
2. Where there is no course...
3. That does not exist for that student.

Solution

select distinct cs.Student
from @CourseStudent cs
where not exists ( select 'x'
                   from @Course c
                   where not exists ( select 'x'
                                      from @CourseStudent cs2
                                      where cs2.CourseID = c.CourseID
                                        and cs2.Student = cs.Student ) )

Fiddle to see things in action.

Answer 2

This is a relational division question. There are a number of ways to solve it. The simplest in this case uses COUNT:

SELECT cs.Student
FROM @CourseStudent cs
GROUP BY cs.Student
HAVING COUNT(*) = (SELECT COUNT(*) FROM @Course);

This does not deal with the case of students taking courses which are not in @Course