3. GIVen a table structure as folloOWs. Courses Student ID PK Couse ID PK Sessio

Question

3. GIVen a table structure as folloOWs. Courses Student ID PK Couse ID PK Sessions CPKFK Corse ID CPKFK Semester Year Grade (a) (15 points) Please show the SQL statement that find all coses taken by the student that has student ID-1 and the grades he/she receives. The result set should look like: Student ID StudFirstName StudLastName StudLevel Student ID course_ID Semester Year Grade Handerson Freshman Bob Bob 2015 4 2016 3 2016 2 2016 3 2014 3 Freshman 1 HandersonFreshman Handersonrehman Fal Sorino Fal Sorino Freshman 1

Explanation / Answer

Hello there,

As i am writing complete sql query for the above asked questions:

1.--> SELECT s.student_ID,s.studFirstName,s.studLastName,s.studLevel,ss.studentID,ss.courceID,ss.semester,ss.year,ss.grade
FROM Students s,
Cources c,
Sessions ss,
WHERE s.student_ID = ss.student_ID
AND c.cource_ID = ss.cource_ID
AND s.student_ID = 1;

In this query i have joined all the 3 tables and then apply condition of student_ID=1, and wrote down all the requiured columns in select statement.

2.-->  select s.student_ID,(sum(grade)/count(grade)) as GPA from sessions s, cources c where c.cource_ID = s.cource_ID groupby s.student_Id;

In this query, i applied group by function and then used sum and count to find out GPA, in this i made join only to assure that cource must be available in cource table.

Hope, you got the answers, feel free to ask any doubts.

Thank you

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

---

---

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