The Binary BrothersTwo Mentors, One Mission – Your Selection
All mock tests are FREE till 31 December — register to save your progress
Unit 4 · Database Management Systems Paper 2 CS · December 2022 Shift 1

Directions (Q96–100): Use the following schema of an academic institution's relational database:

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 (student) and hod (department) refer to professor.empid; rollno in enrollment refers to student; courseid in enrollment and teaching refers to course; empid in teaching refers to professor; precourseid and courseid in prerequisite refer to course.

Which of the following queries computes, 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 on deptid = deptno in WHERE, group by department, and SUM the credits. COUNT counts courses; C has no join or grouping; D filters the join after grouping, which is invalid.

More Database Management Systems questions