Question

Write a query to display the course ID, name and the total fees
collected from each course. Give alias name to total fees as
TOTALFEES. Sort the result based on course ID.​

Answer 1

Answer:

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 2

Answer:

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