/*I'm have having difficulties solving this...

suppose there exists 3 tables... "S" has only a list of students. "C" has only a list of courses. and the third, "E" lists what course(s) each student is enrolled in. (2 columns, student and course). A student has any number of courses and can appear any number of times in "E".

How do i write a query that gives all unique pairs of students (S1, S2) such that S1 is enrolled in AT LEAST all of S2's courses.

I have been stuck on this for days, any help would be appreciated. THANKS!
*/

DECLARE @student TABLE (
	[studentID] INT PRIMARY KEY
	, [name] VARCHAR(255)
	)

DECLARE @course TABLE (
	[courseID] INT PRIMARY KEY
	, [name] VARCHAR(255)
	)

DECLARE @enrolment TABLE (
	[studentID] INT
	, [courseID] INT
	)

INSERT @student ([studentID], [name])
      SELECT 1, 'Fred Flintstone'
UNION SELECT 2, 'Wilma Flintstone'
UNION SELECT 3, 'George Jetson'
UNION SELECT 4, 'Shaggy Doo'
UNION SELECT 5, 'Captain Caveman'

INSERT @course ([courseId], [name])
      SELECT 1, 'Maritime Law'
UNION SELECT 2, 'English'
UNION SELECT 3, 'Cookery'
UNION SELECT 4, 'Astrophysics'
UNION SELECT 5, 'BJJ'
UNION SELECT 6, 'Chemistry'

INSERT @enrolment ([studentID], [courseID])
      SELECT 1, 2 -- Fred Flintstone (English)
UNION SELECT 1, 3 -- Fred flintstone (Cookery)
UNION SELECT 2, 2 -- Wilma Flintstone (English)
UNION SELECT 2, 3 -- Wilma Flintstone (cookery)
UNION SELECT 2, 4 -- Wilman Flinstone (Astrophysics)
UNION SELECT 3, 4 -- George Jetson (Astrophysics)
UNION SELECT 4, 6 -- Shaggy Doo (Chemistry)
UNION SELECT 4, 3 -- Shaggy Doo (Cooking)
UNION SELECT 4, 5 -- Shaggy Doo (BJJ)
UNION SELECT 5, 5 -- Captain Cavemen (BJJ)

SELECT
	s.[name] AS [Student]
	, c.[name] AS [Course]
	, s2.[name] AS [Matches]
FROM
	@student s
	JOIN @enrolment e ON e.[studentID] = s.[studentID]
	JOIN @enrolment e2 ON e2.[courseId] = e.[courseId] AND e2.[studentID] <> e.[studentID]
	JOIN @student s2 ON s2.[studentID] = e2.[studentID]

	JOIN @course c ON c.[courseID] = e.[courseID]
WHERE
	NOT EXISTS (
		SELECT 1
		FROM
			@enrolment e3
		WHERE
			e3.[studentID] = e2.[studentID]
			AND e3.[courseID] NOT IN (SELECT [courseID] FROM @enrolment WHERE [studentID] = e.[studentID])
		)
			
ORDER BY
	s.[name] 
	, c.[name]
	, s2.[name]