0

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;```
MT0
  • 143,790
  • 11
  • 59
  • 117
Podri12
  • 1
  • 1
  • 5
    Did you learn about outer joins in your class? The first step is to make SID = S008 appear in your result set (even though he did not enroll in any classes); then making his count 0 instead of NULL is quite easy. –  Mar 18 '22 at 23:51
  • *"...(student ID)... and ... total credits attained by **every student**"*. Tip: If That means starting your joins with the `Student` table, not `enrollments`. – SOS Mar 19 '22 at 19:02

0 Answers0