MySQL 8.0

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

WITH Student(StudentId, StudentName, Sex)

(

SELECT 1, 'AMY', 'M' UNION ALL

SELECT 2, 'JACK', 'M' UNION ALL

SELECT 3, 'TONY', 'M' UNION ALL

SELECT 4, 'TARA', 'M' UNION ALL

SELECT 5, 'SARAH', 'F' UNION ALL

SELECT 6, 'TOM', 'F'

Lecture(LecId, Subject, Professor)

(

SELECT 1, 'MATH', 'JOHN' UNION ALL

SELECT 2, 'MATH', 'JOSEPH' UNION ALL

SELECT 3, 'PHY', 'MARK' UNION ALL

SELECT 4, 'PHY', 'MAX' UNION ALL

SELECT 5, 'PHY', 'JOHN' UNION ALL

SELECT 6, 'CHEM', 'JOHN' UNION ALL

SELECT 7, 'CHEM', 'JOSEPH' UNION ALL

SELECT 8, 'HISTORY', 'JOSEPH'

Attendance(StudId, LectureId)

(

SELECT 1, 1 UNION ALL

SELECT 1, 2 UNION ALL

SELECT 3, 1 UNION ALL

SELECT 2, 5 UNION ALL

SELECT 2, 6 UNION ALL

SELECT 3, 4 UNION ALL

SELECT 1, 6 UNION ALL

SELECT 4, 5 UNION ALL

SELECT 5, 1 UNION ALL

SELECT 5, 2 UNION ALL

SELECT 1, 3 UNION ALL

StudentId	StudentName	GROUP_CONCAT(DISTINCT L.Professor)
2	JACK	JOHN
4	TARA	JOHN