Reverse Instructional Design — Insert and Select in SQL

Question1: You have the following two sql_tables A and B:

A

Column      |          Type          |          Modifiers
——————+————————+——————————
indx            | text                   | not null
ctd_filename    | text                   |
depth           | double precision       |
oxygen          | double precision       |

B

Column      |          Type          |          Modifiers
——————+————————+——————————
indx            | text                   | not null
ctd_filename    | text                   |
depth           | double precision       |
temperature     | double precision       |

What would be the sql code to fill A with

indx;ctd_filename;depth;oxygen
m_12_001_1; m_12_001;1; 212
m_12_001_2; m_12_001;2; 195
m_12_002_1; m_12_002;1; 198
m_12_002_2; m_12_002;2; 178

and B with

indx;ctd_filename;depth;temperature
m_12_001_1; m_12_001;1; 23
m_12_001_2; m_12_001;2; 22
m_12_002_1; m_12_002;1; 18
m_12_002_2; m_12_002;2; 22

?

Question 2:

What is the correct postgres sql statement to calculate from above tables the average depth, oxygen and temperature for the two different filenames and output them in one table?

A)

SELECT A.depth, avg(A.oxygen) as oxygen, avg(B.temperature) as temperature
from A
LEFT JOIN
B
ON A.ctd_filename = B.ctd_filename
GROUP BY A.depth

B)
SELECT A.ctd_filename, avg(A.oxygen) as oxygen, avg(B.temperature) as temperature
from A
LEFT JOIN
B
ON A.ctd_filename = B.ctd_filename

C)

SELECT A.ctd_filename, avg(A.depth) as depth, avg(A.oxygen) as oxygen, avg(B.temperature) as temperature
from A
LEFT JOIN
B
ON A.ctd_filename = B.ctd_filename
GROUP BY A.ctd_filename

D)
SELECT A.ctd_filename, avg(A.depth) as depth, avg(A.oxygen) as oxygen, avg(B.temperature) as temperature
from A
LEFT JOIN
B
ON A.ctd_filename = B.ctd_filename
GROUP BY A.indx