How do I write a SQL query to display course_id, course_name and fee collected from each course?

Question

The various tables and their columns are:

1)Course

course_id
course_name
duration
fee

2)Registration

course_id
stud_id
doj

3)Student

stud_id
first_name
last name
city
dob

I have tried:

SELECT 
    C.COURSE_ID,
    C. COURSE_NAME, 
    COUNT(R.COURSE_ID) * FEES AS TOTAL_FEES 
FROM 
    COURSE C, 
    REGISTRATION R, 
WHERE 
    C.COURSE_ID = R.COURSE_ID 
GROUP BY 
    R.COURSE_ID

Answer 1

SELECT c.cource_id, c.course_name, (totalNumberOfStudentPerCourse.totalNumberOfStudent * c.fee) as totalFee
FROM Course c
INNER JOIN 
        (SELECT r.course_id, COUNT(r.stud_id) as totalNumberOfStudent
        FROM Registration r
        GROUP BY r.course_id) totalNumberOfStudentPerCourse ON c.course_id =  totalNumberOfStudentPerCourse.course_id 

the subquery creates a totalNumberOfStudentPerCourse table that looks like this:

course_id     totalNumberOfStudent
1             4
2             2

example:

course:

course_id  course_name  fee

1           math         300
2           english      200

registration:

course_id    stud_id
1             1
1               2
1               3
1               4
2               5
2               6

result:

course_id   course_name     totalFee
1           math            1200
2           english         400