Search code examples
mysqlsqlrelational-databaserelational-algebra

SQL Relational Division query

I have three tables student(ID, name), takes(ID, course_id), course(course_id, dept_name). I would like to query student ID and name who take all courses from dept_name = 'Comp. Sci.'. I know I need to use Relation Division. But I'm not sure how to do.

Can anyone explain how can I achieve this?

PS. I just start learning SQL today.

Solution

  • You can try this below logic-

    SELECT S.ID, S.name 
FROM student S 
INNER JOIN takes T ON S.ID = T.ID
INNER JOIN course C ON T.course_id = C.course_id AND C.dept_name = 'Comp. Sci.'
GROUP BY S.ID, S.name
HAVING COUNT(T.course_id) = (SELECT COUNT(*) FROM course WHERE dept_name = 'Comp. Sci.')

    As gordon said about possibility of taking same course twice, we can use the Having clause as below-

    HAVING COUNT(DISTINCT T.course_id) = (SELECT COUNT(*) FROM course WHERE dept_name = 'Comp. Sci.')