1.List all the students and their GPA (result schema: sid and GPA). The GPA is c
ID: 3870295 • Letter: 1
Question
1.List all the students and their GPA (result schema: sid and GPA). The GPA is calculated by summing up the grade of each course multiplied the number of credits for the course and then dividing the result by the total number of credits the student has taken.
2.For every course, return the names of the highest-scoring students (result schema: course title and student name).
3. List all the students (sid and name) that are enrolled in courses for which they have taken the prerequisites.
4. Find the students (sid and name) that have taken at least one of the prerequisite(s) of course ‘CS595’ and got an ‘A’ grade in this prerequisite course.
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
2. select name,title from student s join course c where s,dept=c.dept and (sid,cid) in
((select sid cid from courses where (cid,gradepoint)in
(select cid, max(gradepoint) from courses group by cid));
3. select sid,name from student wher sid in
(select sid from course where cid in(select cid from prereq where pid is not null));
4. select sid,name from student where sid in
(select sid from enroll where cid in(select pid from prereq where cid='cs595') and grade='A')
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.