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');
通过次数相同的人 ，他们中总分最大的那次的人 的考试日期
求答案
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
6）通过次数最多的人，他的每科平均分，总分最高分，以及最高分的考试日期
7）考试的月份中，每个月的总分最高分的人，最低分的人
8）所有考试中，每个人总分高于平均分的次数