student (rollno, name, degree, year, sex, deptno, advisor)
department (deptid, name, hod, phone)
professor (empid, name, sex, startyear, deptno, phone)
course (courseid, cname, credits, deptno)
enrollment (rollno, courseid, sem, year, grade)
teaching (empid, courseid, sem, year, classroom)
prerequisite (precourseid, courseid)

Which of the following queries retrieves the deptid and name of all departments whose total credits of offered courses is strictly greater than 40?

1. A

   SELECT deptid, name, SUM(credits) AS totalcredits
   FROM department, course WHERE totalcredits > 40
   GROUP BY deptid, name HAVING deptid = deptno;

2. B

   SELECT deptid, name, SUM(credits) AS totalcredits
   FROM department, course
   WHERE deptid = deptno AND totalcredits > 40
   GROUP BY deptid, name;

3. C

   SELECT deptid, name, SUM(credits) AS totalcredits
   FROM department, course
   GROUP BY deptid, name
   HAVING deptid = deptno AND totalcredits > 40;

4. D

   SELECT deptid, name, SUM(credits) AS totalcredits
   FROM department, course WHERE deptid = deptno
   GROUP BY deptid, name
   HAVING totalcredits > 40;

Pick an option to check yourself, or open the answer below.

SELECT deptid, name, SUM(credits) AS totalcredits
FROM department, course WHERE deptid = deptno
GROUP BY deptid, name
HAVING totalcredits > 40;