Finding total participation in sql

Question

This is a question I could not answer in oracle lab exam.

Given the schema:

(Courses: cid(int), deptid(int)...);
(Students: sid(int), sname (string), deptid(int)...);
(Participation: cid(int), sid(int), ...);

A student can attend courses outside his department.

Need to get the names of the students who take all the courses offered by his department.

How to do this in sqlplus?

Almost identical to this question: http://stackoverflow.com/questions/7803775/sql-queries-involving-for-all/7803860#7803860 — Dylan Smith, Oct 20 '11 at 02:38

Joe Stefanelli · Accepted Answer · 2011-10-20T13:11:52.737

2

SELECT s.sid, s.sname, s.deptid
    FROM Students s
        INNER JOIN Participation p
            ON s.sid = p.sid
        INNER JOIN Courses c
            ON p.cid = c.cid
                AND s.deptid = c.deptid
    GROUP BY s.sid, s.sname, s.deptid
    HAVING COUNT(DISTINCT c.cid) = (SELECT COUNT(*)
                                        FROM Courses c2
                                        WHERE c2.deptid = s.deptid)

edited Oct 20 '11 at 13:11

answered Oct 20 '11 at 02:33

Joe Stefanelli

132,803
19
237
235

1 slight mistake,the OP allows a student to take courses outside of their department, so the join to courses should not restrict s.deptid = c.deptid - e.g. the student can attend every course offered by a different department to their own – Andrew Oct 20 '11 at 02:35
@Andrew But we're trying to get students who take all courses offered by their department, hence the restriction to get the correct count. – Joe Stefanelli Oct 20 '11 at 02:39
It says 'his department' - and the student could be from another department but still take every course in 'his' department - the question does not specify to only consider that departments students taking all their courses within their own department - it just asks for all the student who take all the courses from that department. I would group on the student ID, the names can duplicate too easily – Andrew Oct 20 '11 at 02:41
@Andrew: Good point on the Grouping. I still don't follow you on the department though. The student belongs to exactly department. That department offers a given number of courses. Find all students taking all courses offered by their assigned department. The student may take courses in other departments as well, but those don't count toward the total we're interested in. – Joe Stefanelli Oct 20 '11 at 02:44
Its a subtle point, but the question does not specify within their 'assigned department' at any point. It does specify that you can take a course outside of your own assigned department. The edge case is therefore a student from another assigned department, taking every course within your own. The filter to restrict to your 'assigned department' is an assumed requirement the OP did not specify / restrict to – Andrew Oct 20 '11 at 02:46
@Andrew To me it's clear that when the OP says "his department", he means `Students.deptid`, not some arbitrarily chosen department. – Joe Stefanelli Oct 20 '11 at 02:48
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/4405/discussion-between-andrew-and-joe-stefanelli) – Andrew Oct 20 '11 at 02:50
@JoeStefanelli thats what I meant. Thanks you for the answer.BTW, does the order in which we specify the join here change the result? I am new to 2 joins. – softwarematter Oct 20 '11 at 02:51
No, the join order is immaterial – Andrew Oct 20 '11 at 02:53
I am getting this error in sqlplus. What could be the reason? `WHERE c2.deptid = s.deptid) * ERROR at line 11: ORA-00979: not a GROUP BY expression` – softwarematter Oct 20 '11 at 03:25
@cod3r Try Adding `s.deptid` to the SELECT and GROUP BY clauses. I'll update the answer as well. – Joe Stefanelli Oct 20 '11 at 13:11

score 0 · Answer 2 · answered Oct 20 '11 at 07:00

I cannot tesst the query right now, so I don't know if I have a syntactic error, anyway, you can try this idea to achieve your requirements.

SELECT studentName
FROM
  (SELECT stu.sname      AS studentName,
    cour.deptid          AS dept,
    COUNT(*)             AS assistedCoursesByDpt,
    Max(cour.total)           AS total
  FROM students stu,
    participation part,
    (SELECT cour.deptid,COUNT(*) AS total FROM courses cour GROUP BY cour.deptid
    ) AS cour
  WHERE stu.sid=part.sid
  AND part.cid =cour.cid
  GROUP BY stu.sid,
    cour.deptid
  )
WHERE total=assistedCoursesByDpt

Th idea is to create a subquery (cour) that has a new calculated column, the total courses by debt. Then you can compare this total with the agrouped student courses by dept.

Finding total participation in sql

2 Answers2