STUDENT, COURSE, GRADE and GRADE_POINT tables presented below .
STUDENT
Primary Key: STUDENT_ID
COURSE
Primary Key: COURSE_NO
GRADE_POINT
Primary Key: GRADE
GRADE
Primary Key: COURSE_NO + STUDENT_ID
Foreign Keys: COURSE_NO references COURSE ,
STUDENT_ID references STUDENT ,
GRADE references GRADE_POINT table
.
1 Retrieve the STUDENT_FNAME and STUDENT_LNAME for all students with CS Major.
2 Retrieve the STUDENT_ID, STUDENT_FNAME , STUDENT_LNAME for all students in CS100.
3 List the first name and last name of all students who have received A in all the courses that they have taken. (Show the query and the result).
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.
Thank you everyone
Kiran Kumar TalikotiPosted Oct 21, 2013, 1:41 AM
1: select STUDENT_FNAME,STUDENT_LNAME
From Where Major=='CSE';
2: Select STUDENT_ID,Student_FNAME,Student_LName
From Student,Course
Where Student.Student_ID==Grade.Student_ID and Course.Course_No=='CS100';
3:Select Stduent_Fname,Student_Lname
From Student,Course
where Student.Student_ID==Course.Student_ID and Course.Grade=='A'
4: This Question is Complex so i explained with various steps check it out
//Based on Each Course Retriving Points and Credits are take in result Table
(Select Student_Id,Course_NO,Points,Credits
From Student,Grade,Grade_Point
where Student.Student_ID==Grade.Student_Id and Grade.Grade==GradePoint.Grade) Result_Table1
Now,
//All Students Credits and Each Subject Tot_Credit
(Select Student_Id,Course_NO,Credits, (Credit*Points) as TOT_CR_PT
From Result_Table1) Result_Table2
//here we will get the student and their GPA
(select Student_Id,Sum( TOT_CR_PT )/Sum(Credits)
from Result_Table2
group by Student_Id ) Result_Table3
//Now Compare Result_Table3 with Student Table To Get Each Student Details
Select STUDENT_ID,Student_FNAME,Student_LName
From Result_Table3 ,Student
Where Student.Student_ID== Result_Table3 .Student_ID