There are 3 tables
takes as following
CREATE TABLE student ( ID varchar(5), name varchar(20) NOT NULL, dept_name varchar(20), tot_cred numeric(3,0) CHECK (tot_cred >= 0), PRIMARY KEY (ID), FOREIGN KEY (dept_name) REFERENCES department ON DELETE SET NULL ) CREATE TABLE takes ( ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), PRIMARY KEY (ID, course_id, sec_id, semester, year), FOREIGN KEY (course_id, sec_id, semester, year) REFERENCES section ON DELETE CASCADE, FOREIGN KEY (ID) REFERENCES student ON DELETE CASCADE ) CREATE TABLE course ( course_id varchar(8), title varchar(50), dept_name varchar(20), credits numeric(2,0) CHECK (credits > 0), PRIMARY KEY (course_id), FOREIGN KEY (dept_name) REFERENCES department ON DELETE SET NULL )
tot_cred column data in the
student table now is assigned with random values (not correct), I want to perform the query that updates and renews those data based on the course’s
grade each student has taken. For those students who received
F grade will be excluded and those who didn’t take any course will be assigned 0 as
I came up with two approaches, one is
UPDATE student SET tot_cred = (SELECT SUM(credits) FROM takes, course WHERE takes.course_id = course.course_id AND student.ID = takes.ID AND takes.grade <> 'F' AND takes.grade IS NOT NULL)
This query meets all my needs, but for those students who didn’t take any course, it does assign NULL value instead of 0.
The second is using
UPDATE student SET tot_cred = (select sum(credits) case when sum(credits) IS NOT NULL then sum(credits) else 0 end FROM takes as t, course as c WHERE t.course_id = c.course_id AND t.grade<>'F' and t.grade IS NOT NULL )
But it assigned 0 to all students. Is any way to achieve the above requirement?
If the 1st query meets your requirement and the only problem is that it returns
NULL for the students that did not take any course then the easiest solution would be to use instead of
SUM() aggregate function the function
TOTAL() which will return
0 instead of
UPDATE student AS s SET tot_cred = ( SELECT TOTAL(c.credits) FROM takes t INNER JOIN course c ON t.course_id = c.course_id WHERE t.ID = s.ID AND t.grade <> 'F' AND t.grade IS NOT NULL );
The same could be done with
SELECT COALESCE(SUM(credits), 0)...
Also, use a proper join with an
ON clause and aliases for the tables to improve readability.