Can someone please help me with these? Database System Oracle For each course, l
ID: 671635 • Letter: C
Question
Can someone please help me with these?
Database System Oracle
For each course, list the COURSE NO, COURSE NAME, and the total number of students in each course as No of Students List the first name and last name of all students who have received A in all the courses that they have taken. List the first name and last name of all students who have taken every course offered by the CS department. List the STUDENTJD, 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 for all the courses taken by the student, and POINTS is determined by the student's grade in the course.Explanation / Answer
1.
SELECT a.COURSE_NO,a.noOfStudents,b.COURSE_NAME
(SELECT COURSE_NO,COUNT(STUDENT_ID) as noOfStudents FROM Grade
group by COURSE_NO) a
INNER JOIN COURSE b
ON a.COURSE_NO = b.COURSE_NO
2.
SELECT b.STUDENT_FNAME,b.STUDENT_LNAME FROM
(SELECT STUDENT_ID,GRADE FROM GRADE
GROUP BY STUDENT_ID,GRADE
WHERE GRADE = 'A'
HAVING COUNT(STUDENT_ID) = 1) a
INNER JOIN STUDENT b
ON a.STUDENT_ID = b.STUDENT_ID
3.
SELECT * FROM GRADE a
INNER JOIN COURSE b
ON a.COURSE_NO = b.COURSE_NO
WHERE b.DEPT = 'CS'
GROUP BY b.STUDENT_ID
HAVING COUNT(COURSE_NO) = (SELECT COUNT(*) FROM COURSE WHERE DEPT = 'CS')
4.
SELECT *,TOT_CR_PT/TOTAL_CREDITS
FROM
(SELECT a.STUDENT_ID,SUM(b.CREDITS) as TOTAL_CREDITS,SUM(COUNT(GRADE)*c.POINTS*b.CREDIT) as TOT_CR_PT FROM GRADE a
INNER JOIN COURSE b
ON a.COURSE_NO = b.COURSE_NO
INNER JOIN GRADE_POINT c
ON a.GRADE = b.GRADE
GROUP BY a.STUDENT_ID,a.GRADE) d
INNER JOIN STUDENT e
ON d.STUDENT_ID = e.STUDENT_ID