SQL Join

SQL Apprentice Question
I'm trying to do a join based on the following tables:


Person(person_id,person_name)
Grade(grade_id,grade_person_id,grade_score)


The data looks like this:


Person:
1,John
2,Dave


Grade:
1,1,80
2,1,90
3,2,60
4,2,70


I'd like a query that returns the each persons name along with their
highest grade.


What would the query be?



Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

If you had followed minimal Netiquette, would you have posted something
that shows that you are dealing with sets (collective or plural names)
that have particular roles in the data model:


CREATE TABLE Students -- we doi not grade people unless we are
cannibals
(student_id INTEGER NOT NULL PRIMARY KEY,
student_name VARCHAR(35) NOT NULL)';


CREATE TABLE Grades -- in what?
(student_id INTEGER NOT NULL
REFERENCES Students (student_id)
grade_score DECIMAL (5,2) NOT NULL,
PRIMARY KEY (student_id, grade_score);



>> The data looks like this: .. <<


I HOPE NOT!! That would a disaster.


>> I'd like a query that returns the each person's [student] name along with their


highest grade. <<

Why does this smell of homework assignment. So first thing is to
answer your quesiton then track you down and report you for cheating
to your school if needed.


SELECT S.student_id, S.student_name, MAX(G.grade_score)
FROM Grades AS G, Students AS S
WHERE S.student_id = G.student_id
GROUP BY S.student_id, S.student_name;


You have to have the student_id in case of duplicate names.