SQL to select records from multiple table using joins

I want a SQL select query for below scenario. I have 6 tables.

1) University

• Univ_id
• University_Name

2)College

• College_id
• College_Name
• Univ_id (FK)

3)Department

• Dept_id
• Dept_name
• College_id (FK)

4) Subjects

• Subject_id
• Subject Name

5)Student

• Student_id
• Student_name
• Dept_id (FK)
• Subject_id (FK)

6)Student_Subject

• Student_id (FK)
• Subject_id (FK)
• Marks

I want the sum of marks for per Subject and per University. Please suggest and SQL statement for the same.

Answer

This should work:

SELECT u.university_name, su.subject_name, sum(ss.marks) sum_of_marks
FROM university u
JOIN college c ON c.univ_id=u.univ_id
JOIN department d ON d.college_id=c.college_id
JOIN student st ON st.dept_id = d.dept_id
JOIN student_subject ss ON ss.student_id=st.student_id
JOIN subjects su ON su.subject_id=ss.subject_id
GROUP BY  u.university_name, su.subject_name