SELECT * FROM aa
WHERE sno IN (SELECT sno FROM aa
              GROUP BY sno
              HAVING COUNT(cno) > 1)
      AND degree != ALL (SELECT MAX(degree) FROM aa
                         GROUP BY cno);