SELECT all_st_with_mjr.sid, all_st_with_mjr.sname AS name, all_st_with_mjr.gpa, all_st_with_mjr.dname AS major
FROM (SELECT all_st.sid, all_st.sname, all_st.gpa, all_mjr.dname /* select all students that have a major */
      FROM student all_st JOIN major all_mjr ON all_st.sid = all_mjr.sid) all_st_with_mjr JOIN (SELECT DISTINCT ON(pop_mjr_max_gpa.dname) /* pop major highest gpas */
                                                                                                pop_mjr_max_gpa.gpa, pop_mjr_max_gpa.dname
                                                                                                       FROM(SELECT s.sid, s.gpa, pop_mjr_sids.dname
                                                                                                      FROM (SELECT st_mjr.sid, pop_mjr.dname /* get all the students that are in the most popular majors */
                                                                                                              FROM (SELECT major.dname /* get the majors that have 8 or more students */
                                                                                                                  FROM major
                                                                                                                  GROUP BY major.dname
                                                                                                                  HAVING COUNT(major.sid) > 8) pop_mjr JOIN major st_mjr ON pop_mjr.dname = st_mjr.dname) pop_mjr_sids JOIN student s ON pop_mjr_sids.sid = s.sid) pop_mjr_max_gpa
      ORDER BY pop_mjr_max_gpa.dname, pop_mjr_max_gpa.gpa DESC) highest_gpa_pop_mjr ON all_st_with_mjr.dname = highest_gpa_pop_mjr.dname AND all_st_with_mjr.gpa = highest_gpa_pop_mjr.gpa
ORDER BY all_st_with_mjr.gpa DESC