Insert grades only for student missing those grades with SQL

I'm new to SQL and have this problem: I must check if every student has all grades (1-10). If a student is missing a grade, I must insert it. But no student should have duplicates of the same grade - they should have it exactly once.

All that I've done is to display all students with their grades:

 SELECT CONCAT(studentdata.FirstName,studentdata.LastName) AS 'Name', grade.Grade
 FROM studentdata
 CROSS JOIN grade
 WHERE studentdata.Id = grade.IdStudent
 ORDER BY Name

Solution

If you have table gradelevels with only one column (Grade) with the possible grade values (1, 2, 3, ..., 10) you can do it like this:

INSERT INTO grade
SELECT
   studentdata.Id AS IdStudent,
   gradelevels.Grade AS Grade
FROM studentdata                  -- Get all the students,
CROSS JOIN gradelevels            -- one time for each possible grade.
LEFT JOIN grade ON
  -- Now we will know what (student, grade) combinations have an entry in grade.
  studentdata.Id = grade.IdStudent AND gradelevels.Grade = grade.Grade
WHERE grade.IdStudent IS NULL     -- Only take the rows where there isnt already a grade.

If you dont want to have a specific table with the gradelevels, you can just replace CROSS JOIN gradelevels with this:

CROSS JOIN (SELECT Grade FROM grade GROUP BY Grade) gradelevels

The SELECT will give you all of the grades that are used, so if there is some grade that is not used for any students, it will not be inserted.

I would recommend you to have an unique index on IdStudent and Grade in the table Grade (or have them as primary key), to ensure no student has the same grade twice.