Hi, making sure this is right, the exam is coming up andthis homework won\'t be

Question

Hi, making sure this is right, the exam is coming up andthis homework won't be graded until after the mid-term :( SoI need to make sure I am doing this right for the exam. I didexamples in the book but these are more advanced.

For each class, find the average rno of students in the class

SELECT c.cid , AVG(s.rno)
FROM Students s, Courses c, Enrolled e
WHERE c.cid = ecid AND s.sid = e.sid;

2) For each class find the number of students in the class thathave rno greater than the average rno in that class

Now this one actually looks like using views would help, so I triedthat, not sure if its supported though.
CREATEVIEW tempTable AS
SELECT Courses.cid AS cid, AVG(Students.rno) AS averageRno
FROM Students, Courses, Enrolled
WHERE Courses.cid = Enrolled.cid AND Students.sid =Enrolled.sid;

SELECT Courses.cid, COUNT(Students.rno)
FROM Students, Courses,tempTable
WHERE Courses.cid = tempTable.cid AND
                Students.rno > tempTable.averageRno;

I'm kind of thinking because the professor didn't stress much onthe views, he may not want me to do it this way, not surethough.

Explanation / Answer

1) For each class, find the average rno of students in theclass SELECT Courses.cid , AVG(Students.rno) FROM Students, Courses, Enrolled WHERE Courses.cid = Enrolled.cid AND Students.sid =Enrolled.sid; 2) For each class find the number of students in the class thathave rno greater than the average rno in that class SELECT Courses.cid, COUNT(Students.rno) FROM Students, Courses,SELECT Courses.cid AS Cid, AVG(Students.rno) ASaverageRno INTO tempTable FROM Students, Courses, Enrolled WHERE Courses.cid = Enrolled.cid AND Students.sid =Enrolled.sid WHERE Courses.cid = tempTable.cid AND                 Students.rno > tempTable.averageRno;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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