My database schema is as follows:
Students(sid, firstname, lastname, status, gpa, email)
Courses(dept_code, course#, title)
Course_credit(course#, credits)
Classes(classid, dept_code, course#, sect#, year, semester, limit, class_size)
Enrollments(sid, classid, lgrade)
I'm looking to get the sid(student ID), lastname and the total credits attained by every student. For a student who has not enrolled in any course, the number of credits attained should be zero and it should be reported as zero.
I have successfully done every thing except the zero part. I cannot figure out how to use NVL() to replace the credits to zero if a letter grade is NULL. If a particular student is not enrolled in any course, he simply does not show up in the output.
SQL> SELECT * FROM enrollments;
SID CLASS L
---- ----- -
S001 c0001 A
S002 c0002 B
S003 c0004 A
S004 c0004 C
S004 c0005 B
S005 c0006 B
S006 c0006 A
S001 c0002 C
S003 c0005 B
S007 c0007 A
S001 c0004 B
For S008 is not enrolled in any class. Hence its corresponding credit earned must be zero. But S008 does not get printed at the moment.
Code:
FROM enrollments JOIN classes ON classes.classid = enrollments.classid JOIN students ON students.sid = enrollments.sid JOIN course_credit ON course_credit.course# = classes.course#
GROUP BY enrollments.sid, students.lastname
ORDER BY enrollments.sid;```