List all the courses (their id) and for each course return the number of courses

Question

List all the courses (their id) and for each course return the number of courses it has as a prerequisite.
Return the number of courses for which the average grade of all students enrolled in the course is lower than ‘B’ (grade point below 3.0).

Consider the following database schema and example instance: Course Student title Databases dept credits cid CS425 name dept sid 001 Alice CS 002 Bob EE 003 Carol CS 004 David PHYS CS CS595 Database Security CS EE VLSI Design EE 3 591 Microcomputers EE401 4 PHYS571 Radiation Physics PHYS 3 Enroll sid grade gradepoint cid CS425001A CS595 001B CS595 002A EE401 001 A EE401 002 B EE401 004 A 4.0 3.0 4.0 4.0 3.0 4.0 2.0 4.0 Prereq cid pi CS595 CS425 EE591 EE401 PHYS571 002 C PHYS571 004A Hints: Attributes shown with grey background form the primary key of a relation.

Explanation / Answer

Query 1:

select cid, count(pid) from prereq group by cid

union

select cid,0 from cources where cid not in (select cid from prereq);

Query 2:

select * from

(select grade, avg(gradepoint) gradepoint from enroll group by grade) where gradepoint<3;

output:

grade gradepoint

______ __________

C 2

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

---

---

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