try:
insert into C
(
select
AF1,AF2,AVG(AF3),
(select BF3 from B where BF1=AF1 AND BF2=AF2) from A group by AF1,AF2
)
insert into C
(
select
AF1,AF2,AVG(AF3),
(select BF3 from B where BF1=AF1 AND BF2=AF2) from A group by AF1,AF2
)
select AF1,AF2,avg(af3),max(bf3) from a,b where BF1=AF1 AND BF2=AF2 group by AF1,AF2;