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 compute, for each department, the total credits of all the courses offered by the department?
Pick an option to check yourself, or open the answer below.
Show answer & explanation
Correct answer: A. select deptid, name, sum(credits) as totalcredits from department, course where deptid=deptno group by deptid, name;
Explanation
Join department and course on deptid = deptno, group by department, and use SUM(credits). COUNT would count courses, and putting the join condition in HAVING after GROUP BY is wrong.
More Database Management Systems questions
- 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 al…
- 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,…