JazzBullets JazzBullets - 2 months ago 7
SQL Question

Oracle SQL: For each class, list how many students are in each major

I'm in a database management class, and while I've been blazing through this week's homework, I've been stuck on this last question for a couple days: "For each class, list how many students are in each major. List class name, major, and count of students." The schema is as follows:

Student(*snum:* integer, *sname:* string, *major:* string, *level:* string, *age:* integer)
Class(*name:* string, *meets_at:* time, *room:* string, *fid:* integer)
Enrolled(*snum:* integer, *cname:* string)
Faculty(*fid:* integer, *fname:* string, *deptid:* integer)


I've figured out how to run this query for an individual class:

SELECT s.major, COUNT(s.snum)
FROM class c, student s, enrolled e
WHERE c.name=e.name AND s.snum=e.snum AND c.name='Class Name'
GROUP BY s.major;


I tried to do the following query, but the inner query doesn't have any context for c1, so I don't know where to go from here:

SELECT c1.name, s.major, totalstudents
FROM class c1, (SELECT s.major, COUNT(s.snum) AS totalstudents
FROM class c, student s, enrolled e
WHERE c.name=e.name AND s.snum=e.snum AND c.name=c1.name
GROUP BY s.major)
GROUP BY c1.name;


So how do I do this?

Answer

Try adding Class name in Select and Group by

SELECT c1.NAME,
       s.major,
       Count(s.snum)
FROM   class c
       INNER JOIN enrolled e
               ON c.NAME = e.NAME
       INNER JOIN student s
               ON s.snum = e.snum
GROUP  BY s.major,
          c1.NAME 

As mentioned in comments by Gordan, start using INNER JOIN syntax it more readable than Old style comma separated Join