Given the following STUDENT-COURSE scheme: STUDENT (Rollno, Name, courseno) COURSE (courseno, coursename, capacity), where Rollno is the primary key of relation STUDENT and courseno is the primary key of relation COURSE. Attribute coursename of COURSE takes unique values only. Which of the following query(ies) will find the total number of students enrolled in each course, along with its coursename?
A. SELECT coursename, count (*) 'total' from STUDENT natural join COURSE group by coursename;
B. SELECT C.coursename, count (*) 'total' from STUDENT S, COURSE C where S.courseno = C.courseno group by coursename;
C. SELECT coursename, count (*) 'total' from COURSE C where courseno in (SELECT courseno from STUDENT);

Correct option is A

The problem requires us to identify which SQL queries correctly retrieve the total number of students enrolled in each course along with the course name from the given schema.
Step-by-step Analysis:
Given Schema:
1. STUDENT(Rollno, Name, courseno):
· Contains details of students enrolled in courses.
· Primary key: Rollno.
2. COURSE(courseno, coursename, capacity):
· Contains details of courses.
· Primary key: courseno.
· coursename has unique values.
Objective: To compute the total number of students enrolled in each course (count (*)) grouped by coursename.
Query Analysis:
1. Query A:

· The NATURAL JOIN automatically joins STUDENT and COURSE based on the common attribute courseno.
· Groups the records by coursename and counts the number of students (count (*)).
Correct: This query achieves the desired output.
2. Query B:

· This query explicitly joins STUDENT and COURSE on the condition S.courseno = C.courseno.
· Groups the records by coursename and counts the number of students (count (*)).
Correct: This query also achieves the desired output.
3. Query C:

· The WHERE clause restricts the COURSE table to only those courses for which there are enrollments in the STUDENT table.
· Groups the records by coursename and counts the number of students.
Incorrect: This query fails because it does not count the number of students correctly—it only counts the number of matching coursename entries in COURSE.
Information Booster:
1. NATURAL JOIN:
· Automatically joins two tables based on common column names and data types.
· Saves effort compared to writing explicit join conditions.
2. Explicit Join:
· Uses ON or WHERE to specify the exact condition for joining tables.
· Provides more control and flexibility than a NATURAL JOIN.
3. GROUP BY:
· Used to group rows that share the same values in specified columns.
· Essential for aggregate functions like count, sum, avg, etc.
Additional Knowledge:
· Query C fails to count the number of students because:
· It checks only if the courseno exists in the STUDENT table without aggregating the actual enrollments.
· As a result, it groups only based on coursename without calculating the number of students.