这道SQL题应该怎么写？

Question

tname(姓名)、fr_grade(FR得分)、sqgrade(SQL得分)、tomcat_grade(TOMCAT得分)、kettle_grade(kettle得分)
、inserttime(考核时间)
其中KETTLE不是必考项是加分项，考核通过的要求是:FR、SQL、TOMCAT三个模块得分均超过60分同时四个模块
总分超过240题目要求:

建表
CREATE TABLE sql2002 (
tname varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
fr_grade int NULL DEFAULT NULL,
sql_grade int NULL DEFAULT NULL,
tomcat_grade int NULL DEFAULT NULL,
kettle_grade int NULL DEFAULT NULL,
inserttime date NULL DEFAULT NULL
)

INSERT INTO sql2002 VALUES ('陈一', 40, 68, 68, 39, '2021-05-20');
INSERT INTO sql2002 VALUES ('陈一', 43, 50, 81, 33, '2021-06-05');
INSERT INTO sql2002 VALUES ('李四', 41, 30, 36, 93, '2021-05-23');
INSERT INTO sql2002 VALUES ('李四', 60, 67, 60, 14, '2021-05-29');
INSERT INTO sql2002 VALUES ('李四', 52, 63, 76, 62, '2021-06-01');
INSERT INTO sql2002 VALUES ('林二', 72, 79, 68, 55, '2021-05-21');
INSERT INTO sql2002 VALUES ('林二', 63, 81, 60, 11, '2021-05-27');
INSERT INTO sql2002 VALUES ('林二', 75, 85, 90, 89, '2021-05-30');
INSERT INTO sql2002 VALUES ('王五', 50, 72, 89, 84, '2021-05-24');
INSERT INTO sql2002 VALUES ('王五', 94, 33, 53, 88, '2021-06-02');
INSERT INTO sql2002 VALUES ('张三', 56, 31, 41, 67, '2021-05-22');
INSERT INTO sql2002 VALUES ('张三', 80, 66, 72, 72, '2021-05-28');
INSERT INTO sql2002 VALUES ('张三', 85, 59, 29, 34, '2021-05-31');
INSERT INTO sql2002 VALUES ('赵七', 42, 77, 58, 87, '2021-05-26');
INSERT INTO sql2002 VALUES ('赵七', 83, 95, 65, 21, '2021-06-04');
INSERT INTO sql2002 VALUES ('赵七', 93, 25, 32, 70, '2021-06-07');
INSERT INTO sql2002 VALUES ('周六', 98, 61, 80, 28, '2021-05-25');
INSERT INTO sql2002 VALUES ('周六', 44, 84, 28, 99, '2021-06-03');
INSERT INTO sql2002 VALUES ('周六', 71, 62, 56, 79, '2021-06-06');

通过次数相同的人 ，他们中总分最大的那次的人 的考试日期

求答案

Answer 1

FR、SQL、TOMCAT三个模块得分均超过60分同时四个模块总分超过240题目要求:
SELECT tname 
          ,fr_grade
          ,sql_grade
            ,kettle_grade
      ,fr_grade+ +tomcat_grade +kettle_grade AS ZF 
            ,inserttime 
FROM sql2002
WHERE fr_grade > 60
AND sql_grade  > 60
AND tomcat_grade  > 60 
AND fr_grade+ +tomcat_grade +kettle_grade > 240 

6:
 SELECT TNAME
       ,COUNT(1)
             ,AVG(fr_grade)
             ,AVG(sql_grade)
             ,AVG(tomcat_grade)
             ,AVG(kettle_grade)
             ,MAX(fr_grade+ +tomcat_grade +kettle_grade+sql_grade) 
             ,MAX(inserttime)
 FROM sql2002  
 WHERE TNAME IN ( SELECT TNAME  FROM sql2002 T1  GROUP BY TNAME  HAVING COUNT(TNAME) >2)
 GROUP BY TNAME 
 
 
7:
SELECT tname 
          ,fr_grade
          ,sql_grade
            ,kettle_grade
        ,fr_grade+ +tomcat_grade +kettle_grade+sql_grade AS ZF 
            ,SUBSTR(inserttime,1,7 )
FROM sql2002
WHERE fr_grade+sql_grade +tomcat_grade +kettle_grade+sql_grade IN (SELECT MAX(fr_grade+sql_grade  +tomcat_grade +kettle_grade)OVER(PARTITION BY SUBSTR(inserttime,1,7),TNAME ) FROM sql2002 )
OR fr_grade+sql_grade +tomcat_grade +kettle_grade+sql_grade IN (SELECT  MIN(fr_grade+sql_grade  +tomcat_grade +kettle_grade)OVER(PARTITION BY SUBSTR(inserttime,1,7),TNAME )   FROM sql2002)
ORDER BY  SUBSTR(inserttime,1,7 ) DESC 
 

8:
SELECT tname 
          ,fr_grade
          ,sql_grade
            ,kettle_grade
        ,tomcat_grade
            ,sql_grade 
      ,sql_grade +fr_grade+ +tomcat_grade +kettle_grade AS ZF 
            ,inserttime 
            ,AVG(sql_grade +fr_grade+ +tomcat_grade +kettle_grade) 
FROM sql2002
HAVING AVG(sql_grade +fr_grade+ +tomcat_grade +kettle_grade) > sql_grade +fr_grade+ +tomcat_grade +kettle_grade