Directions (Q96–100): Academic database schema: 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). deptno in student, professor and course refers to department.deptid; advisor refers to professor.empid; hod refers to professor.empid; enrollment.rollno refers to student.rollno; courseid in enrollment and teaching refers to course.courseid; teaching.empid refers to professor.empid; precourseid and courseid in prerequisite refer to course.courseid.
Which of the following queries would retrieve the dept id and name of all the departments that are such that the total of the credits of all the offered courses by the department is strictly greater than 40 ?
Pick an option to check yourself, or open the answer below.
Show answer & explanation
Correct answer: D. select deptid, name, sum(credits) as totalcredits from department, course where deptid = deptno group by deptid, name having totalcredits >40
Explanation
A condition on an aggregate (total credits > 40) belongs in HAVING after GROUP BY; the join condition belongs in WHERE.
More Database Management Systems questions
- Given query: select s.rollno, s.name from student s where EXISTS (select c.courseid from course c Where c.deptno =5 and EXISTS (select e.*…
- Identify the incorrect statement(s).
- Consider the two relations below. The primary keys are underlined. Identify all possible foreign key(s) from the options based only on the…
- Which of the following is consistent with this instance of R(X, Y, Z): (1, 1, a), (1, 1, a), (2, 1, b), (3, 2, b)?
- R1(Name, Class) = {(a1, 9), (b1, 5), (c1, 6), (d1, 11)} and R2(Name, Class) = {(b1, 5), (f1, 8)}. Which operation produces the output {(b1,…
- Match List I and List II