Given the following tables: • students(sid,name,age,gpa) • courses(cid,deptid, d

Question

Given the following tables:

• students(sid,name,age,gpa)

• courses(cid,deptid, description)

• professors(ssn,name,address,phone,deptid)

• enrollment(sid,cid,section,grade). cid makes reference to the courses table.

• teaches(cid,section,ssn). cid, section makes reference to the enrollment table

Provide SQL instructions for each of the following questions

10. Assume grades are A, B, C, D, F where D and F are failing grades. For each course (section) find the percentage of students that failed the course.

Explanation / Answer

ANSWER:

Select c.cid, Count(Select * From student s, encrollment e where s.sid = e.sid and e.grade = 'D' or e.grade = 'F')*100/ (Select count (*) from students s, enrollment e where s.sid = e.sid) as Failed Percentage
From enrollment e, courses c
Where e.cid = c.cid
Group By c.cid;

To find the percentage use count for the column grade where grade is D or F satisfying the failed criteria for each student, divided by total count of all the students. each student will have one grade. hence, we will get the total failed percentage grouped by course.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.