Question

Consider the following relational schema: 

Which of the following options is/are correct SQL query/queries to retrieve the names of the students enrolled in course number (i.e., courseno) 1470?

• A.
• B.
• C.
• D.

Hint:

In SQL, tt{EXISTS} is often more efficient than tt{COUNT(*)} when checking for the existence of matching rows.

Answer 1

The Correct Option is A, C, D

To find students enrolled in course number 1470, we check different SQL query approaches:

• Option (A): Uses EXISTS, which efficiently verifies if a student is enrolled. This is correct because the EXISTS clause checks for the existence of records that match the condition, in this case, enrollment in course 1470.
• Option (B): Uses SIZEOF, which is not a valid SQL function. This is incorrect because SIZEOF is not recognized in SQL; the correct function would be something like COUNT(*) or using EXISTS for verifying record existence.
• Option (C): Uses COUNT(*), which returns a positive value when a student is enrolled. Since it checks for a positive count of records, it is correct. This approach counts the number of rows where the student is enrolled in course 1470, and if the count is greater than zero, it confirms enrollment.
• Option (D): Uses NATURAL JOIN between Students and Enrolled, which implicitly joins on rollno and filters by courseno = 1470. This is also correct because a NATURAL JOIN automatically matches columns with the same name, and the query will filter students who are enrolled in the specified course.

Each of these options uses a different SQL approach to achieve the same result, but only Options (A), (C), and (D) are correct, while Option (B) is incorrect due to the use of an invalid function.