January 22, 2016 at 2:59 am
I have two simple tables StudentSubiect and Student.
Table StudentSubiect (contains all the grades registered for the students) has 2 columns: StudentID (we have 5 students actually) and Grade (From 1 to 10)
Table Student (contains the 5 students' names and surnames) has 3 columns: StudentID, Name, Surname
I know with INNERJOIN I could use the StudentID as a common column to join the two tables to get an output displaying the StudentID, Name, Surname, Grade in one table, but I do not fully understand its syntax.
I need to join these tables and also calculate a descending average of Grades for each StudentID using the columns StudentID, Name, Surname and Grade (in this order).
Can you please help me with the T-SQL query?
I tried this but there must be something more:
SELECT StudentSubiect.StudentID
AVG (Grade)
FROM StudentSubiect
INNER JOIN Student
on
StudentSubiect.StudentID=Student.StudentID
ORDER BY StudentID
January 22, 2016 at 3:25 am
Since this appears to be a homework assignment, I am not going to spoon-feed you the answer.
But I will help of course. Your query contains an aggregation (the AVG function) per student. That is okay, but it also requires a GROUP BY clause. Ask your teacher (or Google) for specifics if that part of the language has not yet been covered or if you missed that lesson.
January 22, 2016 at 3:36 am
Hi Hugo,
Thanks for the help.
I looked a bit into the GROUP BY clause and I would say this:
SELECT StudentSubiect.StudentID
AVG (Grade)
FROM StudentSubiect
INNER JOIN Student
on
StudentSubiect.StudentID=Student.StudentID
GROUP BY Name, Surname DESC
I am not sure here if I should include StudentID in the GROUP BY clause though.
January 22, 2016 at 4:07 am
SELECT StudentSubiect.StudentID
AVG (Grade)
FROM StudentSubiect
INNER JOIN Student
on
StudentSubiect.StudentID=Student.StudentID
GROUP BY Name, Surname DESC
Should be like this:
SELECT StudentSubiect.StudentID
AVG (Grade)
FROM StudentSubiect
INNER JOIN Student
on
StudentSubiect.StudentID=Student.StudentID
GROUP BY StudentSubiect.StudentID
I guess you are confused with Group by and order by
I will agree with Hugo, to get these concepts clear
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply