MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS Students (
    TERM INT,
    ID INT,
    LAST_NAME VARCHAR(255),
    FIRST_NAME VARCHAR(255),
    DEGREE VARCHAR(50),
    COURSE INT,
    COURSE_CODE VARCHAR(50),
    CREDIT VARCHAR(1),
    YEAR VARCHAR(1)
);

INSERT INTO Students (TERM, ID, LAST_NAME, FIRST_NAME, DEGREE, COURSE, COURSE_CODE, CREDIT, YEAR) VALUES
(2024, 0011, 'Richards', 'Diana', 'PHD', 3000, '030011', 'S', 'N'),
(2024, 0011, 'Richards', 'Diana', 'PHD', 3011, '030111', 'S', 'N'),
(2024, 0011, 'Richards', 'Diana', 'PHD', 3015, '030015', 'S', 'N'),
(2024, 0012, 'Smith', 'Alex', 'PHD', 2011, '020012', 'S', 'N'),
(2024, 0012, 'Smith', 'Alex', 'PHD', 2001, '020013', 'S', 'N'),
(2024, 0012, 'Smith', 'Alex', 'PHD', 2021, '020014', 'S', 'N'),
(2024, 0012, 'Smith', 'Alex', 'PHD', 2023, '020015', 'S', 'N'),
(2024, 0013, 'Thompson', 'Robert', 'PHD', 1009, '010011', 'S', 'N'),
(2024, 0013, 'Thompson', 'Robert', 'PHD', 1029, '010020', 'S', 'N');

CREATE TABLE IF NOT EXISTS ClassMarks (
    TERM_YEAR INT,
    STUDENT_ID INT,
    CLASS_CODE VARCHAR(50),
    CLASS VARCHAR(255),
    CLASS_MARK VARCHAR(2)
);

INSERT INTO ClassMarks (TERM_YEAR, STUDENT_ID, CLASS_CODE, CLASS, CLASS_MARK) VALUES
(2020, 0012, '010011', '1001', 'A'),
(2020, 0011, '010021', '1009', ''),
(2021, 0011, '010020', '1029', 'B+'),
(2021, 0012, '010001', '1011', 'B'),
(2022, 0011, '020012', '2011', 'C+'),
(2022, 0012, '010021', '1009', 'A-'),
(2022, 0012, '010020', '1029', 'A'),
(2023, 0011, '020014', '2021', 'A+'),
(2024, 0012, '020012', '2011', ''),
(2024, 0012, '020013', '2001', ''),
(2024, 0012, '020014', '2021', ''),
(2024, 0012, '020015', '2023', ''),
(2024, 0013, '010011', '1009', ''),
(2024, 0013, '010020', '1029', '');

Records: 9  Duplicates: 0  Warnings: 0

Records: 14  Duplicates: 0  Warnings: 0

with year_rules as (
    select Student_ID,
           max(case when class = 1009 then term_year end) as latest_enrollment_in_1009,
           max(case when class = 1029 then term_year end) as latest_enrollment_in_1029,
           max(case when class = 2011 then term_year end) as latest_enrollment_in_2011,
           max(case when class = 2021 then term_year end) as latest_enrollment_in_2021
      from ClassMarks
     group
        by Student_ID)
select distinct
       s.term,
       s.id,
       s.last_name,
       s.first_name,
       s.degree,
       s.credit,
       case when latest_enrollment_in_1009 = s.term
             and latest_enrollment_in_1029 = s.term then 1
            when latest_enrollment_in_2011 = s.term 
             and latest_enrollment_in_2021 = s.term then 2
            when greatest(latest_enrollment_in_1009, 
                          latest_enrollment_in_1029,
                          latest_enrollment_in_2011,
                          latest_enrollment_in_2021) < s.term then 3
         end as derived_year
  from Students s
 inner
  join year_rules yr
    on s.id = yr.student_id;

term	id	last_name	first_name	degree	credit	derived_year
2024	11	Richards	Diana	PHD	S	3
2024	12	Smith	Alex	PHD	S	2
2024	13	Thompson	Robert	PHD	S	1