Question

Consider the relational database with the following four schemas and their respective instances: \[ \text{Student(sNo, sName, dNo)} \text{       }\text{Dept(dNo, dName)} \\ \text{Course(cNo, cName, dNo)} \text{         } \text{Register(sNo, cNo)} \] 

SQL Query: 

SELECT * FROM Student AS S WHERE NOT EXIST
 (SELECT cNo FROM Course WHERE dNo = “D01”
	EXCEPT 
 SELECT cNo FROM Register WHERE sNo = S.sNo) 

The number of rows returned by the above SQL query is___________.

Hint:

In SQL, using the EXCEPT clause allows you to exclude certain rows from the results. Here, the query excludes courses that are already registered by the students.

Answer 1

Correct Answer: 2

Step 1: Identify the "Target Set"

The inner subquery selects all courses belonging to a specific department:$$\text{Target} = \{cNo \mid \text{Course.dNo} = \text{'D01'}\}$$This represents the set of courses that a student must have registered for to be excluded from the "NOT EXISTS" logic.

---

Step 2: Identify the "Student Set" 

For each student $S$, the second part of the subquery finds the courses they have actually registered for:$$\text{Registered}_S = \{cNo \mid \text{Register.sNo} = S.sNo\}$$

---

Step 3: Analyze the Set Difference (EXCEPT)

The EXCEPT operator performs the operation: $\text{Target} \setminus \text{Registered}_S$.

• If this set is empty, it means the student has registered for all courses in Department D01.
• If this set is not empty, it means there is at least one course in D01 that the student has not registered for.

 

---

Step 4: Apply the NOT EXISTS Logic

The outer query selects students where the result of the EXCEPT is empty. Therefore, the query is looking for:$$\text{Students who have registered for every course offered by Department 'D01'.}$$

---

Final Answer:

Based on the provided solution, there are exactly 2 students who meet the criteria of having registered for all courses in Department D01.$$\boxed{2}$$