1 For each course, list the COURSE_NO, COURSE_NAME, and the total number of stud
ID: 3563853 • Letter: 1
Question
1 For each course, list the COURSE_NO, COURSE_NAME, and the total number of students in each course as No_of_Students
2 List the first name and last name of all students who have received A in all the courses that they have taken.
3 List the first name and last name of all students who have taken every course offered by the CS department.
4 List the STUDENT_ID, STUDENT_FNAME , STUDENT_LNAME, and GPA for each student. GPA is computed as follows: GPA = TOT_CR_PT/ TOT_CREDIT, where TOT_CREDIT is the sum of the credits for all the courses taken by the student TOT_CR_PT is the sum of [CREDITS]*[POINTS] for all the courses taken by the student, and POINTS is determined by the student's grade in the course.
Grade Primary Key: Course_No + Student ID Foreign Keys Course_No references COURSE Student_ID references Student Grade references Grade_Point table 1 For each course, list the COURSE_NO, COURSE_NAME, and the total number of students in each course as No_of_Students 2 List the first name and last name of all students who have received A in all the courses that they have taken. 3 List the first name and last name of all students who have taken every course offered by the CS department. 4 List the STUDENT_ID, STUDENT_FNAME , STUDENT_LNAME, and GPA for each student. GPA is computed as follows: GPA = TOT_CR_PT/ TOT_CREDIT, where TOT_CREDIT is the sum of the credits for all the courses taken by the student TOT_CR_PT is the sum of [CREDITS]*[POINTS] for all the courses taken by the student, and POINTS is determined by the student's grade in the course.Explanation / Answer
Answer 1:
select g.course_no, course_name, count(student_id) as No_of_Students from grade g, course c
where g.course_no=c.course_no
group by g.course_no, course_name
Answer 2:
select student_fname,student_lname from student where student_id in(select student_id from grade
group by course_no,student_id
having grade="A")
Answer 3:
select student_fname,student_lname from student where student_id in(select student_id from grade where course_no="CS100"
union
select student_id from grade where course_no="CS120"
union
select student_id from grade where course_no="CS200"
union
select student_id from grade where course_no="CS220"
union
select student_id from grade where course_no="CS240"
union
select student_id from grade where course_no="CS300")
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.