user4514473 user4514473 - 6 months ago 12
SQL Question

How to get each course name in a grades table where I only have the id for course?

I have a Grades table where I have the following fields:

-STUDENT_ID

-COURSE_ID

-FIRST_TERM

-SECOND_TERM

-FINAL

And a Course table:

-COURSE_ID

-NAME

-DEPARTMENT_ID

I'm trying to get all the grades for a particular student with grades for each course specified, I was wondering how do I get the name of each course?

This is how I get the grades but I want to include the course name also:

SELECT student_id,
course_id,
(first_term+second_term+final) AS "Total Mark"
FROM MARKS
WHERE student_id = 1;

Answer

You can use this query:

SELECT     s.student_id,
           s.course_id,
           c.course_name,
           (s.first_term+s.second_term+s.final) AS "Total  Mark"
FROM       marks s
INNER JOIN course c ON c.course_id = s.course_id
WHERE      s.student_id = 1

Make sure to prefix field names with table names when they are used in both tables (like for course_id). I have prefixed all fields with table aliases.

Table aliases are like short names for tables and you define them right after the table name in the FROM clause.