SQL statement to list final course grade for students taking two specific courses

This is my statement for the final course grade for all those students taking MATH, how can I adjust this so it displays the final course grade for MATH, but only for students who are taking both MATH and ART ?

SELECT sum(mark.mark * assignment.weighting/100)
FROM mark, assignment
WHERE (assignment.course = 'MATH') and (assignment.course = mark.course) and (assignment.assignnum = mark.assignnum)
group by mark.studnum

Answer

You can do it with conditional aggregation:

SELECT m.studnum, 
       SUM(CASE WHEN m.course = 'MATH' THEN m.mark * a.weighting / 100 END) final_course_grade
FROM mark m INNER JOIN assignment a
ON a.course = m.course AND a.assignnum = m.assignnum
WHERE m.course IN ('MATH', 'ART')
GROUP BY m.studnum
HAVING COUNT(DISTINCT m.course) = 2 

First, you should use a proper join with an ON clause and not that archaic syntax with the , between the table names and the join conditions in the WHERE clause.

For your requirement you must remove the condition assignment.course = 'MATH' from the WHERE clause because you want to check that the students took 'ART' also.
This is done in the HAVING clause.

Also, note that if both mark and weighting are integers, then in this expression:

m.mark * a.weighting / 100

the division will truncate any decimal part because SQLite performs integer division between integers.
So, if you want a result with decimal part you should divide by 100.0:

m.mark * a.weighting / 100.0