Schema You will have to write SQL queries against the following database schema

Question

Schema

You will have to write SQL queries against the following database schema (keys are in bold):

        student(sid, sname, sex, age, year, gpa)

        dept(dname, numphds)

        prof(pname, dname)

        course(cno, cname, dname)

        major(dname, sid)

        section(dname, cno, sectno, pname)

        enroll(sid, grade, dname, cno, sectno)

Question

Some courses are popular among students just because students enrolled in those courses usually get good grades. Print the department name, course number, and course enrollment of each course C such that the following two conditions hold: (1) course C's enrollment is 3% larger than the average enrollment of courses offered by C's department, and (2) course C's average grade is 3% larger than the average grade obtained by students in courses offered by C's department. Assume that the enrollment of a course is the sum of the enrollment of all its sections. You can completely ignore any course that has no students enrolled in it.

Explanation / Answer

SELECT e.dname, e.cno, count(*) FROM enroll e

WHERE e.cno IN

(SELECT e1.cno FROM enroll e1

GROUP BY e1.cno

HAVING (count(*) > ((SELECT 1.3*avg(cnt) FROM (SELECT e2.dname, e2.cno, count(*) as cnt FROM enroll e2 WHERE e1.cno=e2.cno GROUP BY e2.dname, e2.cno) AS src)))

AND (avg(e1.grade)>((SELECT 1.3*avg(grades) FROM (SELECT e3.dname, e3.grade as grades FROM enroll e3 WHERE e3.cno=e1.cno GROUP BY e3.dname) AS src2))))

GROUP BY e.dname, e.cno;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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