Understanding the Database Schema

The question involves two database tables (relations):

• STUDENT: Contains information about students.
• COURSE: Contains information about courses.

Let's look at the attributes (columns) in each table:

The `courseno` attribute is present in both tables and links students to the courses they are enrolled in. The `coursename` attribute in the `COURSE` table is unique.

The Goal: Finding Total Students per Course

The objective is to write SQL queries that return two pieces of information for each course:

1. The name of the course (`coursename`).
2. The total number of students enrolled in that specific course.

This requires joining the `STUDENT` and `COURSE` tables based on the common attribute `courseno` and then grouping the results by `coursename` to count the students for each group (course).

Analyzing Each SQL Query

Query A Analysis: Using NATURAL JOIN

The first query is:

SELECT coursename, count(*) 'total' from STUDENT natural join COURSE group by coursename;

• FROM STUDENT natural join COURSE: This performs a NATURAL JOIN between the `STUDENT` table and the `COURSE` table. A NATURAL JOIN automatically joins two tables based on columns with the same name and data type in both tables. In this case, the join will happen on the `courseno` column because it's the only common column name. This effectively links each student record to their corresponding course record.
• SELECT coursename, count(*) 'total': This specifies the columns to be returned. `coursename` is selected, and `count(*)` is used to count the number of rows in each group. The result of `count(*)` is given an alias 'total'.
• group by coursename: This is the crucial part for getting counts per course. The rows resulting from the join are grouped based on their `coursename`. The `count(*)` function then operates on each group separately, counting the number of rows (students) within that group (course).

Conclusion for Query A: This query correctly joins the tables on `courseno`, groups the results by `coursename`, and counts the number of students in each group. It successfully finds the total number of students enrolled in each course along with its name.

Query B Analysis: Using Implicit Join with WHERE Clause

The second query is:

SELECT C.coursename, count(*) 'total' from STUDENT S, COURSE C where S.courseno = C.courseno group by coursename;

• FROM STUDENT S, COURSE C: This lists the tables involved, `STUDENT` aliased as `S` and `COURSE` aliased as `C`. Using a comma here indicates an implicit join, initially forming a Cartesian product of the two tables.
• where S.courseno = C.courseno: This is the join condition applied to the Cartesian product. It filters the results to include only rows where the `courseno` from the `STUDENT` table matches the `courseno` from the `COURSE` table. This achieves the same result as an INNER JOIN on `courseno`.
• SELECT C.coursename, count(*) 'total': Similar to Query A, this selects the course name (using the alias `C` for the `COURSE` table) and counts the rows in each group, aliasing the count as 'total'.
• group by coursename: Again, this groups the results by `coursename`, ensuring the `count(*)` is performed for each individual course.

Conclusion for Query B: This query also correctly joins the tables based on `courseno` using a WHERE clause condition, groups the results by `coursename`, and counts the students per course. It is a standard way to write an inner join query for this task.

Query C Analysis: Using Subquery and Filtering

The third query is:

SELECT coursename, count(*) 'total' from COURSE C where courseno in (SELECT courseno from STUDENT);

• FROM COURSE C: This specifies that the query primarily operates on the `COURSE` table.
• where courseno in (SELECT courseno from STUDENT): This is a filtering condition. The subquery (SELECT courseno from STUDENT) returns a list of all `courseno` values that exist in the `STUDENT` table (i.e., courses that have at least one student enrolled). The outer query then filters the `COURSE` table to include only those courses whose `courseno` is in this list. This correctly identifies courses with enrolled students.
• SELECT coursename, count(*) 'total': This selects the `coursename` and uses `count(*)`. However, there is no GROUP BY clause in this query. When `count(*)` is used without a `GROUP BY` clause, it counts all the rows that satisfy the `WHERE` condition in the entire result set. In this case, it will count the total number of rows from the `COURSE` table that have at least one student enrolled. It will likely return a single row containing one of the course names (depending on SQL implementation) and the total count of courses with students, not the count of students *per* course.

Conclusion for Query C: While the `WHERE` clause correctly filters for courses with students, the lack of a `GROUP BY coursename` clause means it does not count the students *for each specific course*. It counts the total number of qualifying rows from the `COURSE` table, which is not the required output.

Summary of Query Analysis

• Query A correctly uses NATURAL JOIN and GROUP BY to count students per course.
• Query B correctly uses an implicit join with a WHERE clause condition and GROUP BY to count students per course.
• Query C uses a subquery to filter courses but lacks a GROUP BY clause, thus failing to count students *per course*.

Therefore, queries A and B correctly solve the problem.

Final Answer

Based on the analysis, both Query A and Query B correctly find the total number of students enrolled in each course, along with the coursename. Query C does not produce the required per-course count.

Revision Table: Key SQL Concepts

Additional Information: Counting with GROUP BY

When using aggregate functions like COUNT(), SUM(), AVG(), MIN(), or MAX(), the GROUP BY clause is fundamental for performing calculations on subsets of rows rather than the entire result set.

If you use an aggregate function in a SELECT statement and you also select non-aggregated columns, you must include all the non-aggregated columns in the GROUP BY clause. For example, in Queries A and B, we select coursename (a non-aggregated column) and count(*) (an aggregate function), so we must group by coursename.

Consider the difference:

• SELECT count(*) from STUDENT; — Returns the total number of students in the table.
• SELECT courseno, count(*) from STUDENT group by courseno; — Returns the count of students for each unique courseno.

In our problem, we needed the count of students *per course name*, which is why joining with the `COURSE` table to get the `coursename` and then grouping by `coursename` was necessary for queries A and B.