MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH Student AS (

SELECT 101 AS studenid, 'john' AS stuname UNION ALL

SELECT 102, 'aron' UNION ALL

SELECT 103, 'mary' UNION ALL

SELECT 104, 'lucy'

Subject AS (

SELECT 101 AS studenid, 1 AS subjid, 'maths' AS subjname UNION ALL

SELECT 102, 2, 'science' UNION ALL

SELECT 103, 3, 'computer' UNION ALL

SELECT 104, 4, 'english'

Marks AS (

SELECT 1 AS subjid, 50 AS mark UNION ALL

SELECT 2, 40 UNION ALL

SELECT 3, 55 UNION ALL

SELECT 4, 60 UNION ALL

SELECT 1, 40 UNION ALL

SELECT 2, 55 UNION ALL

SELECT 3, 60

)

SELECT

st.studenid,

st.stuname,

COALESCE(SUM(m.mark), 0) AS mark

FROM Student st

LEFT JOIN Subject su

ON st.studenid = su.studenid

LEFT JOIN Marks m

ON su.subjid = m.subjid

GROUP BY

st.studenid,

st.stuname;