select
c.emp_id,
e.emp_level,
d.exam_tag
from
exam_record as c
join (
select
a.exam_id,
b.tag as exam_tag,
avg(timestampdiff (second, a.start_time, a.submit_time)) as avg_time,
avg(a.score) as avg_score
from
exam_record as a
join examination_info as b on a.exam_id = b.exam_id
group by
a.exam_id,
b.tag
) as d on c.exam_id = d.exam_id
join emp_info as e on c.emp_id = e.emp_id
where
timestampdiff (second, c.start_time, c.submit_time) < d.avg_time
and c.score > d.avg_score
and e.emp_level < 7
order by
c.emp_id,
c.exam_id